static void Main(string[] args)
{
string sql = @"
SELECT 'BLOMBlood' SourceTable,a.ChargeItemId,a.ChargeCode,a.ItemChineseName, a.CreateTime,a.ModifyTime ,b.BloodId ItemId,b.BloodCode ItemCode,b.BloodChineseName ItemName
FROM dbo.CHGMChargeItem a FULL OUTER JOIN dbo.BLOMBlood b ON a.ChargeItemId = b.BloodId
WHERE a.OrderTypeCode = 'BLO' AND (a.ChargeItemId IS NULL OR b.BloodId IS NULL OR a.ChargeCode <> b.BloodCode)
UNION all
SELECT 'EXAMExamination' SourceTable,a.ChargeItemId,a.ChargeCode,a.ItemChineseName, a.CreateTime,a.ModifyTime ,b.ExaminationId ItemId,b.ExaminationCode ItemCode,b.ExamChineseName ItemName
FROM dbo.CHGMChargeItem a FULL OUTER JOIN dbo.EXAMExamination b ON a.ChargeItemId = b.ExaminationId
WHERE a.OrderTypeCode = 'EXA' AND (a.ChargeItemId IS NULL OR b.ExaminationId IS NULL OR a.ChargeCode <> b.ExaminationCode)
UNION all
SELECT 'LABMLaboratory' SourceTable,a.ChargeItemId,a.ChargeCode,a.ItemChineseName, a.CreateTime,a.ModifyTime ,b.LaboratoryId ItemId,b.LaboratoryCode ItemCode,b.LaboratoryChineseName ItemName
FROM dbo.CHGMChargeItem a FULL OUTER JOIN dbo.LABMLaboratory b ON a.ChargeItemId = b.LaboratoryId
WHERE a.OrderTypeCode = 'LAB' AND (a.ChargeItemId IS NULL OR b.LaboratoryId IS NULL OR a.ChargeCode <> b.LaboratoryCode)
UNION all
SELECT 'OPRMOperation' SourceTable,a.ChargeItemId,a.ChargeCode,a.ItemChineseName, a.CreateTime,a.ModifyTime ,b.OperationId ItemId,b.OperationCode ItemCode,b.OprChineseName ItemName
FROM dbo.CHGMChargeItem a FULL OUTER JOIN dbo.OPRMOperation b ON a.ChargeItemId = b.OperationId
WHERE a.OrderTypeCode = 'OPR' AND (a.ChargeItemId IS NULL OR b.OperationId IS NULL OR a.ChargeCode <> b.OperationCode)
UNION all
SELECT 'PHRMMedication' SourceTable,a.ChargeItemId,a.ChargeCode,a.ItemChineseName, a.CreateTime,a.ModifyTime,b.MedicationId ItemId,b.MedicationCode ItemCode,b.GenericName ItemName
FROM dbo.CHGMChargeItem a FULL OUTER JOIN dbo.PHRMMedication b ON a.ChargeItemId = b.MedicationId
WHERE a.OrderTypeCode = 'PHR' AND (a.ChargeItemId IS NULL OR b.MedicationId IS NULL OR a.ChargeCode <> b.MedicationCode)
UNION all
SELECT 'PEXMHealthCheckItem' SourceTable,a.ChargeItemId,a.ChargeCode,a.ItemChineseName, a.CreateTime,a.ModifyTime ,b.HealthCheckItemId ItemId,b.HealthCheckItemCode ItemCode,b.ItemChineseName ItemName
FROM dbo.CHGMChargeItem a FULL OUTER JOIN dbo.PEXMHealthCheckItem b ON a.ChargeItemId = b.HealthCheckItemId
WHERE a.OrderTypeCode = 'PEX' AND (a.ChargeItemId IS NULL OR b.HealthCheckItemId IS NULL OR a.ChargeCode <> b.HealthCheckItemCode)
UNION all
SELECT 'TREMTreatment' SourceTable,a.ChargeItemId,a.ChargeCode,a.ItemChineseName, a.CreateTime ,a.ModifyTime,b.TreatmentId ItemId,b.TreatmentCode ItemCode,b.TreatmentChineseName ItemName
FROM dbo.CHGMChargeItem a FULL OUTER JOIN dbo.TREMTreatment b ON a.ChargeItemId = b.TreatmentId
WHERE a.OrderTypeCode = 'TRE' AND (a.ChargeItemId IS NULL OR b.TreatmentId IS NULL OR a.ChargeCode <> b.TreatmentCode)
";
IConfiguration config = new ConfigurationBuilder().AddJsonFile("appsetting.json", optional: true, reloadOnChange: true).Build();
//取得連線字串
string connString = config.GetConnectionString("DefaultConnection");
//string connString = "Data Source=10.1.222.181;Initial Catalog={0};Integrated Security=False;User ID={1};Password={2};Pooling=True;MultipleActiveResultSets=True;Connect Timeout=120;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite";
SqlServerDBHelper sqlHelper = new SqlServerDBHelper(string.Format(connString, "HISDB", "msdba", "1qaz@wsx"));
List <DBdata> migrationTableInfoList = sqlHelper.QueryAsync <DBdata>(sql).Result?.ToList();
DataTable dt = sqlHelper.FillTableAsync(sql).Result;
/*var excelname = new FileInfo(DateTime.Now.ToString("yyyyMMddhhmm") + ".xlsx");
* //ExcelPackage.LicenseContext = LicenseContext.Commercial;
* ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
* using (var excel = new ExcelPackage(excelname))
* {
* excel.Workbook.Worksheets.Add("結果");
* ExcelWorksheet firstsheet = excel.Workbook.Worksheets[0];
* int rowIndex = 1;
* int colIndex = 1;
* //4.3.1塞資料到某一格
* firstsheet.Cells[rowIndex, colIndex++].Value = "SourceTable";
* firstsheet.Cells[rowIndex, colIndex++].Value = "ChargeItemId";
* firstsheet.Cells[rowIndex, colIndex++].Value = "ChargeCode";
* firstsheet.Cells[rowIndex, colIndex++].Value = "ItemChineseName";
* firstsheet.Cells[rowIndex, colIndex++].Value = "CreateTime";
* firstsheet.Cells[rowIndex, colIndex++].Value = "ModifyTime";
* firstsheet.Cells[rowIndex, colIndex++].Value = "ItemId";
* firstsheet.Cells[rowIndex, colIndex++].Value = "ItemCode";
* firstsheet.Cells[rowIndex, colIndex++].Value = "ItemName";
* //4.3.2 Cell Style
* firstsheet.Cells[rowIndex, 1, rowIndex, colIndex - 1]
* .SetQuickStyle(Color.Black, Color.LightPink, ExcelHorizontalAlignment.Center);
*
* foreach (var v in migrationTableInfoList)
* {
* rowIndex++;
* colIndex = 1;
* firstsheet.Cells[rowIndex, colIndex++].Value = v.SourceTable;
* firstsheet.Cells[rowIndex, colIndex++].Value = v.ChargeItemId;
* firstsheet.Cells[rowIndex, colIndex++].Value = v.ChargeCode;
* firstsheet.Cells[rowIndex, colIndex++].Value = v.ItemChineseName;
* firstsheet.Cells[rowIndex, colIndex].Value = v.CreateTime;
* firstsheet.Cells[rowIndex, colIndex].Style.Numberformat.Format = DateTimeFormatInfo.CurrentInfo.ShortDatePattern;
* firstsheet.Cells[rowIndex, colIndex++].Style.Numberformat.Format = "yyyy/MM/dd HH:mm:ss";
* firstsheet.Cells[rowIndex, colIndex].Value = v.ModifyTime;
* firstsheet.Cells[rowIndex, colIndex].Style.Numberformat.Format = DateTimeFormatInfo.CurrentInfo.ShortDatePattern;
* firstsheet.Cells[rowIndex, colIndex++].Style.Numberformat.Format = "yyyy/MM/dd HH:mm:ss";
* firstsheet.Cells[rowIndex, colIndex++].Value = v.ItemId;
* firstsheet.Cells[rowIndex, colIndex++].Value = v.ItemCode;
* firstsheet.Cells[rowIndex, colIndex++].Value = v.ItemName;
*
* }
* //4.3.3 儲存格和字數相等
* int startColumn = firstsheet.Dimension.Start.Column;
* int endColumn = firstsheet.Dimension.End.Column;
* for (int count = startColumn; count <= endColumn; count++)
* {
* firstsheet.Column(count).AutoFit();
* }
* Byte[] bin = excel.GetAsByteArray();
* File.WriteAllBytes(@"C:\Users\v-vyin\SchedulerDB_ExcelFile\" + excelname, bin);
*
* }*/
DatatableToHTML datatableToHTML = new DatatableToHTML();
var helper = new SMTPHelper($"{config[$"SendEmail:FromAddressMail"]}",
$"{config[$"SendEmail:FromAddressMailPassword"]}",
$"{config[$"SendEmail:SMTPHost"]}",
int.Parse($"{config[$"SendEmail:SMTPPort"]}"),
bool.Parse($"{config[$"SendEmail:SMTPEnableSsl"]}"),
bool.Parse($"{config[$"SendEmail:UseDefaultCredentials"]}")); //寄出信email
string subject = $"有關收標與子檔無法對應項目"; //信件主旨
string body = $"Hi All, \r\n\r\n無法對應的項目如下表，\r\n\r\n{datatableToHTML.ToHTML(dt)}\r\n\r\n Best Regards, \r\n\r\n Vicky Yin"; //信件內容
string attachments = null; //附件
/* var fileName = $@"C:\Users\v-vyin\SchedulerDB_ExcelFile\{excelname}";//附件位置
* if (File.Exists(fileName.ToString()))
* {
* attachments = fileName.ToString();
* }*/
//string toMailList = "*****@*****.**";//收件者
//string ccMailList = "";//CC收件者
string toMailList = $"{config[$"TargetTable:toMail"]}"; //收件者
string ccMailList = $"{config[$"TargetTable:ccMail"]}"; //CC收件者
helper.SendMail(toMailList, ccMailList, null, subject, body, null);
}