Ravi ReddyandClaude Opus 5.5
3b371cdb28
VoiceNotes is an Arduino sketch for the Waveshare ESP32-C6-LCD-0.85 that records meetings and voice memos to microSD and turns them into notes: - Records 16 kHz mono WAV (up to 4 h) from the ES7210 dual mics, with a boosted preamp and soft-limited digital gain - Transcribes via an OpenAI-compatible speech-to-text API in ~10 min parts cut at quiet points, with retries and resume after failures - Summarizes the transcript with Claude into meeting notes (decisions, action items, topics with timestamps), streamed to and from the card - Copies the notes to Nextcloud over WebDAV - Shows recording/processing state on the 0.85" LCD and the LED ring - Works around the board's SD card failing multi-sector reads Credentials live in VoiceNotes/secrets.h, which is git-ignored; see secrets.example.h. Also adds the Microsoft CDM customer service SQL implementation (DDL, sample data, reporting queries and docs). Its README is renamed to CDM_README.md so the repository README stays at the top level. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
221 lines
8.9 KiB
Transact-SQL
221 lines
8.9 KiB
Transact-SQL
-- Simplified Bulk Data Generation - 100x More Data
|
|
-- This approach focuses on generating meaningful test data efficiently
|
|
|
|
USE CDM_CustomerService;
|
|
GO
|
|
|
|
PRINT '========================================';
|
|
PRINT 'Starting Simplified Bulk Data Generation';
|
|
PRINT '========================================';
|
|
PRINT '';
|
|
|
|
DECLARE @StartDate DATE = DATEADD(MONTH, -3, CAST(GETUTCDATE() AS DATE));
|
|
DECLARE @EndDate DATE = CAST(GETUTCDATE() AS DATE);
|
|
|
|
-- ============================================================================
|
|
-- GENERATE ACCOUNTS (380 additional - total 400)
|
|
-- ============================================================================
|
|
PRINT 'Generating 380 additional Accounts...';
|
|
DECLARE @i INT = 5;
|
|
DECLARE @SiteId UNIQUEIDENTIFIER = (SELECT TOP 1 siteId FROM cdm.Site ORDER BY NEWID());
|
|
DECLARE @OwnerId UNIQUEIDENTIFIER = (SELECT TOP 1 userId FROM cdm.[User] ORDER BY NEWID());
|
|
|
|
WHILE @i <= 400
|
|
BEGIN
|
|
INSERT INTO cdm.Account (accountName, accountNumber, email, phone, ownerUserId, siteId, accountType, industry, numberOfEmployees, revenue, isActive)
|
|
VALUES ('Account ' + CAST(@i AS NVARCHAR(10)), 'ACC-' + RIGHT('0000' + CAST(@i AS VARCHAR(10)), 5),
|
|
'contact' + CAST(@i AS VARCHAR(10)) + '@customer.com', '555-' + CAST(2000 + @i AS VARCHAR(4)),
|
|
(SELECT TOP 1 userId FROM cdm.[User] ORDER BY NEWID()),
|
|
(SELECT TOP 1 siteId FROM cdm.Site ORDER BY NEWID()),
|
|
CASE WHEN @i % 10 = 0 THEN 'Prospect' ELSE 'Customer' END,
|
|
CHOOSE((@i % 10) + 1, 'Tech', 'Manuf', 'Finance', 'Health', 'Retail', 'Consult', 'Telecom', 'Education', 'Govt', 'Energy'),
|
|
100 + (@i * 3),
|
|
50000 + (@i * 100),
|
|
1);
|
|
SET @i = @i + 1;
|
|
IF @i % 50 = 0 PRINT ' Created ' + CAST(@i AS VARCHAR(10)) + ' accounts...';
|
|
END
|
|
|
|
PRINT 'Accounts: 400 total';
|
|
PRINT '';
|
|
|
|
-- ============================================================================
|
|
-- GENERATE CONTACTS (480 additional - total 500)
|
|
-- ============================================================================
|
|
PRINT 'Generating 480 additional Contacts...';
|
|
SET @i = 6;
|
|
|
|
WHILE @i <= 500
|
|
BEGIN
|
|
INSERT INTO cdm.Contact (firstName, lastName, email, phone, accountId, ownerUserId, jobTitle, department, isActive)
|
|
VALUES ('User' + CAST(@i AS VARCHAR(10)), 'Contact' + CAST(@i AS VARCHAR(10)),
|
|
'user' + CAST(@i AS VARCHAR(10)) + '@customer.com', '555-' + CAST(3000 + @i AS VARCHAR(4)),
|
|
(SELECT TOP 1 accountId FROM cdm.Account ORDER BY NEWID()),
|
|
(SELECT TOP 1 userId FROM cdm.[User] ORDER BY NEWID()),
|
|
CHOOSE((@i % 6) + 1, 'Manager', 'Director', 'Lead', 'Engineer', 'Analyst', 'Specialist'),
|
|
CHOOSE((@i % 5) + 1, 'Operations', 'IT', 'Finance', 'Sales', 'Support'),
|
|
1);
|
|
SET @i = @i + 1;
|
|
IF @i % 100 = 0 PRINT ' Created ' + CAST(@i AS VARCHAR(10)) + ' contacts...';
|
|
END
|
|
|
|
PRINT 'Contacts: 500 total';
|
|
PRINT '';
|
|
|
|
-- ============================================================================
|
|
-- GENERATE CONTRACTS (290 additional - total 300)
|
|
-- ============================================================================
|
|
PRINT 'Generating 290 additional Contracts...';
|
|
SET @i = 11;
|
|
|
|
WHILE @i <= 300
|
|
BEGIN
|
|
INSERT INTO cdm.Contract (contractNumber, contractName, accountId, ownerUserId, startDate, endDate, status, contractValue, contractType, isActive)
|
|
SELECT 'CNT-' + FORMAT(@i, '000000'),
|
|
'Contract' + CAST(@i AS VARCHAR(10)),
|
|
(SELECT TOP 1 accountId FROM cdm.Account WHERE accountType = 'Customer' ORDER BY NEWID()),
|
|
(SELECT TOP 1 userId FROM cdm.[User] ORDER BY NEWID()),
|
|
DATEADD(DAY, (@i * 3) % 90, @StartDate),
|
|
DATEADD(DAY, ((@i * 3) % 90) + 365, @StartDate),
|
|
CASE WHEN (@i % 20) < 15 THEN 'Active' ELSE CASE WHEN (@i % 20) < 18 THEN 'Expired' ELSE 'Draft' END END,
|
|
10000 + (@i * 500),
|
|
CHOOSE((@i % 3), 'Support', 'Service', 'Maintenance');
|
|
SET @i = @i + 1;
|
|
IF @i % 50 = 0 PRINT ' Created ' + CAST(@i AS VARCHAR(10)) + ' contracts...';
|
|
END
|
|
|
|
PRINT 'Contracts: 300 total';
|
|
PRINT '';
|
|
|
|
-- ============================================================================
|
|
-- GENERATE CASES (1996 additional - total 2000)
|
|
-- ============================================================================
|
|
PRINT 'Generating 1996 additional Cases...';
|
|
SET @i = 5;
|
|
DECLARE @CaseCreatedDate DATETIME2;
|
|
DECLARE @TotalMinutes INT = DATEDIFF(MINUTE, @StartDate, @EndDate);
|
|
|
|
WHILE @i <= 2000
|
|
BEGIN
|
|
SET @CaseCreatedDate = DATEADD(MINUTE, ((@i * 7919) % @TotalMinutes), @StartDate);
|
|
|
|
INSERT INTO cdm.[Case] (caseNumber, title, priority, severity, status, accountId, contactId, ownerUserId, createdDate, emailAddress, isActive)
|
|
SELECT 'CSE-' + FORMAT(@i, '000000'),
|
|
'Case ' + CAST(@i AS VARCHAR(10)),
|
|
CHOOSE((@i % 5), 'Low', 'Low', 'Medium', 'High', 'Critical'),
|
|
CHOOSE((@i % 3), 'Low', 'Medium', 'High'),
|
|
CHOOSE((@i % 6), 'Active', 'Active', 'Active', 'Waiting', 'Resolved', 'Closed'),
|
|
(SELECT TOP 1 accountId FROM cdm.Account ORDER BY NEWID()),
|
|
(SELECT TOP 1 contactId FROM cdm.Contact ORDER BY NEWID()),
|
|
(SELECT TOP 1 userId FROM cdm.[User] ORDER BY NEWID()),
|
|
@CaseCreatedDate,
|
|
'support@customer.com',
|
|
1;
|
|
|
|
SET @i = @i + 1;
|
|
IF @i % 200 = 0 PRINT ' Created ' + CAST(@i AS VARCHAR(10)) + ' cases...';
|
|
END
|
|
|
|
PRINT 'Cases: 2000 total';
|
|
PRINT '';
|
|
|
|
-- ============================================================================
|
|
-- GENERATE SERVICE ACTIVITIES (2997 additional - total 3000)
|
|
-- ============================================================================
|
|
PRINT 'Generating 2997 additional Service Activities...';
|
|
SET @i = 4;
|
|
DECLARE @ActivityStart DATETIME2;
|
|
DECLARE @ActivityEnd DATETIME2;
|
|
|
|
WHILE @i <= 3000
|
|
BEGIN
|
|
SET @ActivityStart = DATEADD(MINUTE, ((@i * 13) % @TotalMinutes), @StartDate);
|
|
SET @ActivityEnd = DATEADD(HOUR, 2, @ActivityStart);
|
|
|
|
INSERT INTO cdm.ServiceActivity (subject, serviceId, caseId, ownerUserId, startTime, endTime, duration, status, isActive)
|
|
SELECT 'Activity' + CAST(@i AS VARCHAR(10)),
|
|
(SELECT TOP 1 serviceId FROM cdm.Service ORDER BY NEWID()),
|
|
(SELECT TOP 1 caseId FROM cdm.[Case] ORDER BY NEWID()),
|
|
(SELECT TOP 1 userId FROM cdm.[User] ORDER BY NEWID()),
|
|
@ActivityStart,
|
|
@ActivityEnd,
|
|
120,
|
|
CHOOSE((@i % 4), 'Scheduled', 'In Progress', 'Completed', 'Cancelled'),
|
|
1;
|
|
|
|
SET @i = @i + 1;
|
|
IF @i % 300 = 0 PRINT ' Created ' + CAST(@i AS VARCHAR(10)) + ' service activities...';
|
|
END
|
|
|
|
PRINT 'Service Activities: 3000 total';
|
|
PRINT '';
|
|
|
|
-- ============================================================================
|
|
-- GENERATE TASKS (996 additional - total 1000)
|
|
-- ============================================================================
|
|
PRINT 'Generating 996 additional Tasks...';
|
|
SET @i = 5;
|
|
|
|
WHILE @i <= 1000
|
|
BEGIN
|
|
INSERT INTO cdm.Task (subject, priority, status, dueDate, ownerUserId, caseId)
|
|
SELECT 'Task' + CAST(@i AS VARCHAR(10)),
|
|
CHOOSE((@i % 3), 'Low', 'Medium', 'High'),
|
|
CHOOSE((@i % 4), 'Open', 'In Progress', 'Completed', 'Cancelled'),
|
|
DATEADD(DAY, (@i % 30), @EndDate),
|
|
(SELECT TOP 1 userId FROM cdm.[User] ORDER BY NEWID()),
|
|
(SELECT TOP 1 caseId FROM cdm.[Case] ORDER BY NEWID());
|
|
|
|
SET @i = @i + 1;
|
|
IF @i % 100 = 0 PRINT ' Created ' + CAST(@i AS VARCHAR(10)) + ' tasks...';
|
|
END
|
|
|
|
PRINT 'Tasks: 1000 total';
|
|
PRINT '';
|
|
|
|
-- ============================================================================
|
|
-- GENERATE SLA KPI INSTANCES
|
|
-- ============================================================================
|
|
PRINT 'Generating SLA KPI Instances for Cases...';
|
|
|
|
INSERT INTO cdm.SLAKPIInstance (caseId, kpiName, metric, metricValue, metricUnit, targetValue, status)
|
|
SELECT c.caseId, 'First Response Time', 'Response',
|
|
CAST((CAST(c.caseId AS BINARY(16)) ^ CAST(GETUTCDATE() AS BINARY(8))) AS INT) % 120 + 15,
|
|
'Minutes', 60,
|
|
CASE WHEN (CAST(c.caseId AS BINARY(16)) ^ CAST(GETUTCDATE() AS BINARY(8))) AS INT) % 10 > 7 THEN 'Non-Compliant' ELSE 'Compliant' END
|
|
FROM cdm.[Case] c
|
|
WHERE c.isActive = 1;
|
|
|
|
PRINT '';
|
|
PRINT '========================================';
|
|
PRINT 'Data Generation Summary:';
|
|
PRINT '========================================';
|
|
PRINT '';
|
|
|
|
SELECT 'Entity' AS Entity, 'Count' AS Count
|
|
UNION ALL
|
|
SELECT 'Accounts', CAST(COUNT(*) AS VARCHAR(10)) FROM cdm.Account
|
|
UNION ALL
|
|
SELECT 'Contacts', CAST(COUNT(*) AS VARCHAR(10)) FROM cdm.Contact
|
|
UNION ALL
|
|
SELECT 'Services', CAST(COUNT(*) AS VARCHAR(10)) FROM cdm.Service
|
|
UNION ALL
|
|
SELECT 'Contracts', CAST(COUNT(*) AS VARCHAR(10)) FROM cdm.Contract
|
|
UNION ALL
|
|
SELECT 'Cases', CAST(COUNT(*) AS VARCHAR(10)) FROM cdm.[Case]
|
|
UNION ALL
|
|
SELECT 'Service Activities', CAST(COUNT(*) AS VARCHAR(10)) FROM cdm.ServiceActivity
|
|
UNION ALL
|
|
SELECT 'Tasks', CAST(COUNT(*) AS VARCHAR(10)) FROM cdm.Task
|
|
UNION ALL
|
|
SELECT 'SLA KPI Instances', CAST(COUNT(*) AS VARCHAR(10)) FROM cdm.SLAKPIInstance;
|
|
|
|
PRINT '';
|
|
PRINT 'Date Range: ' + CAST(@StartDate AS VARCHAR(10)) + ' to ' + CAST(@EndDate AS VARCHAR(10));
|
|
PRINT '';
|
|
PRINT '========================================';
|
|
PRINT 'All Data Generated Successfully!';
|
|
PRINT '========================================';
|
|
|
|
GO
|