// ---- Real Work
public void CreateSqlRecords()
{
SetupSqlServer();
if (CreatedRecords == true)
{
return;
}
try
{
using (var motSqlServer =
new MotSqlServer(
$"Data Source={dataSource};Initial Catalog=McKessonTestDb;User ID=sa;Password=$MOT2018"))
{
for (var i = 0; i < 16; i++)
{
CreatedRecords = true;
var docId = RandomData.Integer();
var patId = RandomData.Integer();
var facId = RandomData.TrimString(2).ToUpper();
var storeId = "PHARMASERVE1";
var scripId = RandomData.Integer();
var drugId = RandomData.Integer();
// Prescriber
var sql = $"INSERT INTO dbo.vPrescriber VALUES(" +
$"'{docId}', " + // Prescriber_ID
$"'{RandomData.TrimString(25).ToUpper()}', " + // Last_Name
$"'{RandomData.TrimString(15).ToUpper()}', " + // First_Name
$"'{RandomData.TrimString(1).ToUpper()}', " + // Middle_Initial
$"'{RandomData.TrimString(25).ToUpper()}', " + // Address_Line_1
$"'{RandomData.TrimString(25).ToUpper()}', " + // Address_Line_2
$"'{RandomData.TrimString(20).ToUpper()}', " + // City
$"'MA', " + // State_Code
$"'0{RandomData.Integer(1000, 10000)}', " + // Zip_Code
$"'{RandomData.Integer(1000, 10000)}', " + // Zip_Plus_4
$"'{RandomData.Integer(100, 1000)}', " + // Area_Code
$"'{RandomData.Integer(1000000, 10000000)}', " + // Telephone_Number
$"'{RandomData.Integer(1, 100)}', " + // Exchange
$"'{RandomData.TrimString(2).ToUpper()}{RandomData.Integer(1000000, 10000000)}', " + // DEA_Number
$"'{RandomData.Integer(100000, 1000000)}', " + // DEA_Suffix
$"'{RandomData.TrimString(4).ToUpper()}', " + // Prescriber_Type//
$"'{RandomData.Bit()}', " + // Active_Flag
$"DEFAULT);"; // MSSQLTS
motSqlServer.ExecuteNonQuery(sql);
// Prescriber Note
sql = $"INSERT INTO dbo.vPrescriberNote VALUES(" +
$"'{docId}', " +
$"'{RandomData.Integer()}', " +
$"'{RandomData.TrimString(10).ToUpper()}', " +
$"'{RandomData.TrimString(30).ToUpper()}', " +
$"'{DateTime.Now}', " +
$"'{RandomData.String()}');";
motSqlServer.ExecuteNonQuery(sql);
sql = $"INSERT INTO dbo.vMOTLocation VALUES(" +
$"'{facId}', " +
$"'{storeId}', " +
$"'{RandomData.TrimString(64).ToUpper()}', " +
$"'{RandomData.TrimString(40).ToUpper()}', " +
$"'{RandomData.TrimString(40).ToUpper()}', " +
$"'{RandomData.TrimString(25).ToUpper()}', " +
$"'NH', " +
$"'0{RandomData.Integer(1000, 10000)}', " +
$"'{RandomData.USPhoneNumber()}', " +
$"DEFAULT);";
motSqlServer.ExecuteNonQuery(sql);
var ndc = RandomData.TrimString(11).ToUpper();
// Drug
sql = $"INSERT INTO dbo.vItem " +
$"VALUES('{drugId}', " + //[ITEM_ID]
$"{RandomData.Integer(1, short.MaxValue)}, " + //[ITEM_VERSION]
$"'{ndc}', " + //[NDC_CODE]
$"'{RandomData.TrimString(2).ToUpper()}', " + //[PACKAGE_CODE]
$"'{RandomData.Double(100)}', " + //[PACKAGE_SIZE]
$"{RandomData.Integer(1, 10)}, " + //[CURRENT_ITEM_VERSION]
$"'{RandomData.TrimString(3).ToUpper()}', " + //[ITEM_TYPE]
$"'{RandomData.TrimString(40)}', " + //[ITEM_NAME]
$"'{RandomData.Integer()}', " + //[KDC_NUMBER]
$"'{RandomData.Integer(1, byte.MaxValue)}', " + //[GPI_GROUP_CODE]
$"'{RandomData.Integer(1, byte.MaxValue)}', " + //[GPI_CLASS_CODE]
$"'{RandomData.Integer(1, byte.MaxValue)}', " + //[GPI_SUBCLASS_CODE]
$"'{RandomData.Integer(1, byte.MaxValue)}', " + //[GPI_NAME_CODE]
$"'{RandomData.Integer(1, byte.MaxValue)}', " + //[GPI_NAME_EXTENSION_CODE]
$"'{RandomData.Integer(1, byte.MaxValue)}', " + //[GPI_DOSAGE_FORM_CODE]
$"'{RandomData.Integer(1, byte.MaxValue)}', " + //[GPI_STRENGTH_CODE]
$"'{RandomData.Integer()}', " + //[HRI_NUMBER]
$"'{RandomData.TrimString(7).ToUpper()}', " + //[DOSAGE_SIGNA_CODE]
$"'{RandomData.TrimString(80).ToUpper()}', " + //[INSTRUCTION_SIGNA_STRING]
$"'{RandomData.TrimString(4).ToUpper()}', " + //[FORM_TYPE]
$"'{RandomData.TrimString(3).ToUpper()}', " + //[ROUTE_OF_ADMINISTRATION]
$"'{RandomData.Integer()}', " + //[ALTERNATE_MANUFACTURER_ID]
$"'{RandomData.TrimString(13).ToUpper()}', " + //[UPC]
$"'{RandomData.Double(10).ToString(CultureInfo.InvariantCulture).Substring(0, 15)}', " + //[STRENGTH]
$"'{RandomData.TrimString(4).ToUpper()}', " + //[COLOR_CODE]
$"'{RandomData.TrimString(4).ToUpper()}', " + //[FLAVOR_CODE]
$"'{RandomData.TrimString(4).ToUpper()}', " + //[SHAPE_CODE]
$"'{RandomData.TrimString(10).ToUpper()}', " + //[PRODUCT_MARKING]
$"'{RandomData.Integer(1, 8)}', " + //[NARCOTIC_CODE]
$"'{RandomData.Double(100)}', " + //[UNIT_SIZE]
$"'{RandomData.TrimString(2).ToUpper()}', " + //[UNIT_OF_MEASURE]
$"'{RandomData.TrimString(5).ToUpper()}', " + //[NDC_Manufacturer_Number]
$"'{RandomData.TrimString(10).ToUpper()}', " + //[Manufacturer_Abbreviation]
$"DEFAULT);"; //[MSSQLTS]
motSqlServer.ExecuteNonQuery(sql);
// Drug Caution
sql = $"INSERT INTO dbo.vItemCaution VALUES(" +
$"'{RandomData.Integer()}', " +
$"'{RandomData.Integer(1, 10000)}', " +
$"'{RandomData.TrimString(255)}');";
motSqlServer.ExecuteNonQuery(sql);
var mf = new[] { "M", "F" };
// Patient
sql = $"INSERT INTO dbo.vPatient VALUES(" +
$"'{patId}', " + // Patient_ID
$"'{RandomData.TrimString(25).ToUpper()}', " + // Last_Name
$"'{RandomData.TrimString(15).ToUpper()}', " + // First_Name
$"'{RandomData.TrimString(1).ToUpper()}', " + // Middle_Initial
$"'{RandomData.TrimString(25).ToUpper()}', " + // Address_Line_1
$"'{RandomData.TrimString(25).ToUpper()}', " + // Address_Line_2
$"'{RandomData.TrimString(20).ToUpper()}', " + // City
$"'NH', " + // State_Code
$"'0{RandomData.Integer(1000, 10000)}', " + // Zip_Code
$"'{RandomData.Integer(1000, 10000)}', " + // Zip_Plus_4
$"'{facId}', " + // Patient_Location_Code
$"'{docId}', " + // Primary_Prescriber_ID
$"'{RandomData.Integer(100000000, 1000000000)}', " + // SSN
$"'{DateTime.Now.ToString(CultureInfo.InvariantCulture)}', " + // BirthDate
$"'{DateTime.Now.ToString(CultureInfo.InvariantCulture)}', " + // Deceased_Date
$"'{mf[RandomData.Bit()]}', " + // Sex
$"DEFAULT, " + // MSSQLTS
$"'{RandomData.Integer(100, 1000)}', " + // Area_Code
$"'{RandomData.Integer(1000000, 10000000)}', " + // Telephone_Number
$"'{RandomData.Integer(1, 100)}');"; // Extension
motSqlServer.ExecuteNonQuery(sql);
// Patient Note
sql = $"INSERT INTO dbo.vPatientNote VALUES(" +
$"'{patId}', " +
$"'{RandomData.Integer()}', " +
$"'{RandomData.TrimString(10).ToUpper()}', " +
$"'{RandomData.TrimString(30).ToUpper()}', " +
$"'{DateTime.Now}', " +
$"'{RandomData.String()}');";
motSqlServer.ExecuteNonQuery(sql);
// Patient Allergy
sql = $"INSERT INTO dbo.vPatientAllergy VALUES(" +
$"'{patId}', " +
$"'{RandomData.Integer()}', " +
$"'{RandomData.TrimString(3).ToUpper()}', " +
$"'{RandomData.TrimString(80)}', " +
$"'{RandomData.TrimString(70)}', " +
$"'{RandomData.Integer()}', " +
$"'{DateTime.Now}');";
motSqlServer.ExecuteNonQuery(sql);
// Patient Diagnosis
sql = $"INSERT INTO dbo.vPatientDiagnosis VALUES(" +
$"'{patId}', " +
$"'{RandomData.TrimString(70)}', " +
$"'{RandomData.TrimString(80)}', " +
$"'{DateTime.Now}', " +
$"'{DateTime.Now}');";
motSqlServer.ExecuteNonQuery(sql);
var refills = RandomData.Integer(1, 100);
var refillsRemaining = RandomData.Integer(1, refills);
var daysSupply = RandomData.Integer(1, 366);
var daysRemaining = RandomData.Integer(1, daysSupply);
/*
* sql = $"INSERT INTO dbo.vRx VALUES(" +
* $"'{patId}', " + // Patient_ID
* $"'{scripId}', " + // Rx_ID
* $"{RandomData.Integer()}, " + // External_Rx_ID
* $"'{docId}', " + // Prescriber_ID
* $"'{RandomData.TrimString(7).ToUpper()}', " + // Dosage_Signa_Code
* $"'{RandomData.TrimString(255)}', " + // Decoded_Dosage_Signa
* $"'{RandomData.TrimString(80)}', " + // Signa_String
* $"'{RandomData.TrimString(255)}', " + // Instruction_Signa_Text
* $"'{DateTime.Now.ToString(CultureInfo.InvariantCulture)}', " + // Date_Written
* $"'{DateTime.Now.ToString(CultureInfo.InvariantCulture)}', " + // Dispense_Date
* $"'{DateTime.Now.ToString(CultureInfo.InvariantCulture)}', " + // Last_Dispense_Stop_Date
* $"'{refills}', " + // Total_Refiles_Authorized
* $"'{refillsRemaining}', " + // Total_Refills_Used
* $"'{RandomData.Integer()}', " + // Dispensed_Item_ID
* $"'{RandomData.Integer(1, short.MaxValue)}', " + // Dispensed_Item_Version
* $"'{ndc}', " + // NDC_Code
* $"'{RandomData.Double(100)}', " + // Quantity_Dispensed
* $"'{RandomData.Integer()}', " + // Writen_For_Item_ID
* $"'{RandomData.Integer(1, short.MaxValue)}', " + // Written_For_Item_Version
* $"'{RandomData.Bit()}', " + // Script_Status
* $"'{DateTime.Now.ToString(CultureInfo.InvariantCulture)}', " + // Prescription_Expiration_Date
* $"'{docId}', " + // Responsible_Prescriber_ID
* $"'{DateTime.Now.ToString(CultureInfo.InvariantCulture)}', " + // Discontinue_Date
* $"'{RandomData.Double(10)}', " + // Quantity_Written
* $"'{RandomData.Double(10)}', " + // Total_Qty_Used
* $"'{RandomData.Double(10)}', " + // Total_Qty_Authorized
* $"'{daysSupply}', " + // Days_Supply_Written
* $"'{daysRemaining}', " + // Days_Supply_Remaining
* $"'{RandomData.TrimString(3).ToUpper()}', " + // Script_Origin_Indicater
* $"DEFAULT);"; // MSSQLTS
*/
sql = $"INSERT INTO dbo.vRx VALUES(" +
$"'{patId}', " + // Patient_ID
$"'{scripId}', " + // Rx_ID
$"{RandomData.Integer()}, " + // External_Rx_ID
$"'{docId}', " + // Prescriber_ID
$"'{RandomData.TrimString(7).ToUpper()}', " + // Dosage_Signa_Code
$"'{RandomData.TrimString(255)}', " + // Decoded_Dosage_Signa
$"'{RandomData.TrimString(80)}', " + // Signa_String
$"'{RandomData.TrimString(255)}', " + // Instruction_Signa_Text
$"'{DateTime.Now.ToString(CultureInfo.InvariantCulture)}', " + // Date_Written
$"'{DateTime.Now.ToString(CultureInfo.InvariantCulture)}', " + // Dispense_Date
$"NULL, " + // Last_Dispense_Stop_Date
$"'{refills}', " + // Total_Refiles_Authorized
$"'{refillsRemaining}', " + // Total_Refills_Used
$"'{RandomData.Integer()}', " + // Dispensed_Item_ID
$"'{RandomData.Integer(1, short.MaxValue)}', " + // Dispensed_Item_Version
$"'{ndc}', " + // NDC_Code
$"'{RandomData.Double(100)}', " + // Quantity_Dispensed
$"'{RandomData.Integer()}', " + // Writen_For_Item_ID
$"'{RandomData.Integer(1, short.MaxValue)}', " + // Written_For_Item_Version
$"'{RandomData.Bit()}', " + // Script_Status
$"'{RandomData.Date(2019)}', " + // Prescription_Expiration_Date
$"'{docId}', " + // Responsible_Prescriber_ID
$"NULL, " + // Discontinue_Date
$"'{RandomData.Double(10)}', " + // Quantity_Written
$"'{RandomData.Double(10)}', " + // Total_Qty_Used
$"'{RandomData.Double(10)}', " + // Total_Qty_Authorized
$"'{daysSupply}', " + // Days_Supply_Written
$"'{daysRemaining}', " + // Days_Supply_Remaining
$"'{RandomData.TrimString(3).ToUpper()}', " + // Script_Origin_Indicater
$"DEFAULT);"; // MSSQLTS
motSqlServer.ExecuteNonQuery(sql);
// Rx Note
sql = $"INSERT INTO dbo.vRxNote VALUES(" +
$"'{scripId}', " +
$"'{RandomData.Integer()}', " +
$"'{RandomData.TrimString(10).ToUpper()}', " +
$"'{RandomData.TrimString(30).ToUpper()}', " +
$"'{DateTime.Now}', " +
$"'{RandomData.String(512)}');";
motSqlServer.ExecuteNonQuery(sql);
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw;
}
}
public void RandomDataCycle()
{
try
{
string StoreId = "1081";
OpenTestLogDb();
using (var gateway = new TcpClient(GatewayAddress, GatewayPort))
{
using (var stream = gateway.GetStream())
{
for (var s = 0; s < 3; s++)
{
if (!useLegacy)
{
var Store = new MotStoreRecord("Add")
{
AutoTruncate = AutoTruncate,
logRecords = true,
UseAscii = UseAscii,
StoreID = Guid.NewGuid().ToString(),
StoreName = $"{DateTime.Now.ToLongTimeString()}{RandomData.String()}",
Address1 = RandomData.TrimString(),
Address2 = RandomData.TrimString(),
City = RandomData.TrimString(),
State = "NH",
Zipcode = $"{RandomData.Integer(0, 100000).ToString("D5")}-{RandomData.Integer(0, 100000).ToString("D4")}",
DEANum = RandomData.ShortDEA(),
Phone = RandomData.USPhoneNumber(),
Fax = RandomData.USPhoneNumber()
};
Store.Write(stream);
WriteTestLogRecord(new TestRecord()
{
Id = Guid.NewGuid().ToString(),
RecordId = new Guid(Store.StoreID).ToString(),
RecordType = RecordType.Store,
Name = Store.StoreName,
TimeStamp = DateTime.UtcNow
});
StoreId = Store.StoreID;
}
for (var i = 0; i < MaxLoops; i++)
{
var Facility = new MotFacilityRecord("Add")
{
AutoTruncate = AutoTruncate,
logRecords = true,
UseAscii = UseAscii,
LocationID = Guid.NewGuid().ToString(),
StoreID = StoreId,
LocationName = RandomData.String(),
Address1 = RandomData.TrimString(),
Address2 = RandomData.TrimString(),
City = RandomData.TrimString(),
State = "NH",
Zipcode = $"0{RandomData.Integer(1000, 10000)}",
Phone = RandomData.USPhoneNumber(),
CycleDays = RandomData.Integer(1, 32),
CycleType = RandomData.Bit(),
Comments = RandomData.String(2048)
};
Facility.Write(stream);
WriteTestLogRecord(new TestRecord()
{
Id = Guid.NewGuid().ToString(),
RecordId = new Guid(Facility.LocationID).ToString(),
RecordType = RecordType.Facility,
Name = Facility.LocationName,
TimeStamp = DateTime.UtcNow
});
var Prescriber = new MotPrescriberRecord("Add")
{
AutoTruncate = AutoTruncate,
logRecords = true,
UseAscii = UseAscii,
RxSys_DocID = Guid.NewGuid().ToString(),
LastName = RandomData.TrimString(),
FirstName = RandomData.TrimString(),
MiddleInitial = RandomData.TrimString(1),
Address1 = RandomData.TrimString(),
Address2 = RandomData.TrimString(),
City = RandomData.TrimString(),
State = "NH",
Zipcode = $"0{RandomData.Integer(1000, 10000)}",
DEA_ID = RandomData.ShortDEA(),
TPID = RandomData.Integer(100000).ToString(),
Phone = RandomData.USPhoneNumber(),
Comments = RandomData.String(2048),
Fax = RandomData.USPhoneNumber()
};
Prescriber.Write(stream);
WriteTestLogRecord(new TestRecord()
{
Id = Guid.NewGuid().ToString(),
RecordId = new Guid(Prescriber.PrescriberID).ToString(),
RecordType = RecordType.Prescriber,
Name = $"{Prescriber.LastName}, {Prescriber.FirstName}, {Prescriber.MiddleInitial}",
TimeStamp = DateTime.UtcNow
});
for (var f = 0; f < MaxLoops; f++)
{
var rxId = Guid.NewGuid().ToString();
var drugId = Guid.NewGuid().ToString();
var Patient = new MotPatientRecord("Add")
{
AutoTruncate = AutoTruncate,
logRecords = true,
UseAscii = UseAscii,
PatientID = Guid.NewGuid().ToString(),
LocationID = Facility.LocationID,
PrimaryPrescriberID = Prescriber.PrescriberID,
LastName = RandomData.TrimString(),
FirstName = RandomData.TrimString(),
MiddleInitial = RandomData.TrimString(1),
Address1 = RandomData.TrimString(),
Address2 = RandomData.TrimString(),
City = RandomData.TrimString(),
State = "NH",
Zipcode = $"0{RandomData.Integer(1000, 10000)}",
Gender = RandomData.TrimString(1),
CycleDate = RandomData.Date(DateTime.Now.Year),
CycleDays = RandomData.Integer(0, 32),
CycleType = RandomData.Bit(),
AdmitDate = RandomData.Date(),
ChartOnly = RandomData.Bit().ToString(),
Phone1 = RandomData.USPhoneNumber(),
Phone2 = RandomData.USPhoneNumber(),
WorkPhone = RandomData.USPhoneNumber(),
DOB = RandomData.Date(),
SSN = RandomData.SSN(),
Allergies = RandomData.String(1024),
Diet = RandomData.String(1024),
DxNotes = RandomData.String(1024),
InsName = RandomData.String(),
InsPNo = RandomData.Integer().ToString(),
AltInsName = RandomData.String(),
AltInsPNo = RandomData.Integer().ToString()
};
Patient.Write(stream);
WriteTestLogRecord(new TestRecord()
{
Id = Guid.NewGuid().ToString(),
RecordId = new Guid(Patient.PatientID).ToString(),
RecordType = RecordType.Patient,
Name = $"{Patient.LastName}, {Patient.FirstName}, {Patient.MiddleInitial}",
TimeStamp = DateTime.UtcNow
});
for (var rx = 0; rx < 8; rx++)
{
var Drug = new MotDrugRecord("Add")
{
AutoTruncate = AutoTruncate,
logRecords = true,
UseAscii = UseAscii,
DrugID = Guid.NewGuid().ToString(),
DrugName = RandomData.TrimString(),
NDCNum = RandomData.TrimString().ToUpper(),
ProductCode = RandomData.TrimString().ToUpper(),
LabelCode = RandomData.TrimString().ToUpper(),
TradeName = RandomData.TrimString(),
DrugSchedule = RandomData.Integer(2, 8),
Strength = RandomData.Double(100),
Route = RandomData.TrimString(),
RxOTC = RandomData.Bit() == 1 ? "R" : "O",
VisualDescription = $"{RandomData.TrimString(3)}/{RandomData.TrimString(3)}/{RandomData.TrimString(3)}",
DoseForm = RandomData.TrimString(),
DefaultIsolate = RandomData.Bit(),
ShortName = RandomData.TrimString(),
ConsultMsg = RandomData.String()
};
WriteTestLogRecord(new TestRecord()
{
Id = Guid.NewGuid().ToString(),
RecordId = new Guid(Drug.DrugID).ToString(),
RecordType = RecordType.Drug,
Name = $"{Drug.TradeName}",
TimeStamp = DateTime.UtcNow
});
Drug.Write(stream);
var Rx = new MotPrescriptionRecord("Add")
{
AutoTruncate = AutoTruncate,
logRecords = true,
UseAscii = UseAscii,
PatientID = Patient.PatientID,
PrescriberID = Prescriber.PrescriberID,
DrugID = Drug.DrugID,
RxSys_RxNum = RandomData.Integer(1, 1000000000).ToString(),
RxStartDate = RandomData.Date(DateTime.Now.Year),
RxStopDate = RandomData.Date(DateTime.Now.Year),
DoseScheduleName = RandomData.TrimString().ToUpper(),
QtyPerDose = RandomData.Double(10),
QtyDispensed = RandomData.Integer(1, 120),
RxType = RandomData.Integer(1, 21),
DoseTimesQtys = RandomData.DoseTimes(RandomData.Integer(1, 25)),
Isolate = RandomData.Bit().ToString(),
Refills = RandomData.Integer(1, 100),
Sig = RandomData.String()
};
WriteTestLogRecord(new TestRecord()
{
Id = Guid.NewGuid().ToString(),
RecordId = Guid.NewGuid().ToString(),
RecordType = RecordType.Prescription,
Name = $"{Rx.RxSys_RxNum}",
TimeStamp = DateTime.UtcNow
});
Rx.Write(stream);
}
}
}
}
}
}
CloseTestLogDb();
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
finally
{
StartCleaning = true;
}
}