static void Main(string[] args)
{
//Step1. 備份檔案
//Step1.1. 本機檔案讀取
var filename = "PATMPatientItem";
DirectoryInfo readlocalfile = new DirectoryInfo($@"C:\Users\user\Downloads\{filename}.csv");
DataTable local_dt = TxtConvertToDataTable(readlocalfile.ToString(), "localfile", "|");
string firstColumnName = local_dt.Columns[0].ColumnName;
DataRow[] rows = local_dt.Select();
//Step1.2. 檔案讀取→\\10.1.225.17\d$\csv \\10.1.225.17\d$\CSV - 複製
var host = @"10.1.225.17";
var RDPfile = "CSV";
var username = @"LAPTOP-ODUSIH5U\Administrator";
var password = "******";
string old_path = "";
string new_path = $@"\\{host}\d$\{RDPfile}\" + filename + ".csv";
using (new RDPCredentials(host, username, password))
{
//Step1.3. 找到相對應File
DirectoryInfo readfile = new DirectoryInfo($@"\\{host}\d$\{RDPfile}\{filename}.csv");
//Step1.4. 將File中的資料存入var
string LastWriteTime = File.GetLastWriteTime(readfile.ToString()).ToString("yyyyMMdd");
old_path = $@"\\{host}\d$\{RDPfile}\" + filename + "_" + LastWriteTime + ".csv";
//old_path = $@"\\{host}\d$\{RDPfile}\" + filename + "_" + "2.csv";
//Step1.5. 修改名稱(原File_修改日期yyyyMMdd)--備份
//readfile.MoveTo($@"\\{host}\d$\{RDPfile}\" + filename + "_" + LastWriteTime + ".csv");
readfile.MoveTo(old_path);
//Step1.6. 將下載的File 複製到 mstv
File.Copy(readlocalfile.ToString(), new_path);
}
//Step2. SSMS import CSV
//Step2.1. 連線SSMS
IConfiguration config = new ConfigurationBuilder().AddJsonFile("appsetting.json", optional: true, reloadOnChange: true).Build();
//取得連線字串
string connString = config.GetConnectionString("DefaultConnection");
SqlServerDBHelper sqlHelper = new SqlServerDBHelper(string.Format(connString, "HISDB", "msdba", "1qaz@wsx"));
//Step2.2. 將檔案名稱丟入SQL
string sqlOldCSV = $@"select * from {filename}";
string sqlCSV = $@"--use [HISDB];
--use[HISBILLINGDB];
--, CODEPAGE = 65001
--已更新至225.17
DECLARE @TABLENAME VARCHAR(MAX) = '{filename}'; --檔案名稱去除CSV
IF LEFT(@TABLENAME,3) IN('CHG', 'CLA')
BEGIN
use[HISBILLINGDB];
END
ELSE
BEGIN
use[HISDB];
END
EXEC('TRUNCATE TABLE ' + @TABLENAME)
DECLARE @INS_CNT INT,@UPD_CNT INT
DECLARE @START_TIME VARCHAR(24)
SET @START_TIME = CONVERT(VARCHAR(24), GETDATE(), 121)
DECLARE @ERR_NO INT
DECLARE @SP_NAME VARCHAR(100) = ('mSP_INS_' + @TABLENAME + '_all');
--DECLARE @SP_NAME VARCHAR(100) = ('mSP_INS_' + @TABLENAME + '_fromExternal');
EXEC @ERR_NO = @SP_NAME @INS_CNT OUTPUT, @UPD_CNT OUTPUT
PRINT @ERR_NO;
IF @ERR_NO = 0 BEGIN
EXEC('SELECT * FROM ' + @TABLENAME);
END;";
DataTable old_CSV_dt = sqlHelper.FillTableAsync(sqlOldCSV).Result;
DataTable new_CSV_dt = sqlHelper.FillTableAsync(sqlCSV).Result;
int rowCount = (new_CSV_dt == null) ? 0 : new_CSV_dt.Rows.Count;
Console.WriteLine(rowCount);
int rowCount_old = (old_CSV_dt == null) ? 0 : old_CSV_dt.Rows.Count;
Console.WriteLine(rowCount_old);
DataRow[] id_row = new_CSV_dt.Select();
//Step3. 核對 SQL 跟 檔案中筆數及ID是否正確
bool status = true;
//Step3.1. Map ListA & ListB 是否相同
for (int i = 56; i < rows.Length; i++)
{
bool intStatus = Int32.TryParse(rows[i][firstColumnName].ToString(), out int num);
if (intStatus)
{
for (int c = 56; c < id_row.Length; c++)
{
if (!Equals(num, id_row[c][firstColumnName]))
{
status = false;
}
else
{
status = true;
break;
}
}
}
}
//Step3.3. 回傳比對結果
Console.WriteLine(status);
//Step3.3.1 如果失敗必須先將備份的File名稱rename
if (!status)
{
using (new RDPCredentials(host, username, password))
{
FileInfo readfile_new = new FileInfo(new_path);
readfile_new.Delete();
DirectoryInfo readfile_old = new DirectoryInfo(old_path);
readfile_old.MoveTo(new_path);
}
// Step3.3.2 重新匯入225.17
var reault = sqlHelper.FillTableAsync(sqlCSV).Result;
}
else
{
//Step4. 比對新跟舊的差異發送Email
DataTable compare_result = CompareRows(old_CSV_dt, new_CSV_dt);
DatatableToHTML datatableToHTML = new DatatableToHTML();
//Step4.3. 將List_sync利用Email寄發
var helper = new SMTPHelper("*****@*****.**", "koormyktfbbacpmj", "smtp.gmail.com", 587, true, true); //寄出信email
string subject = $"Initial Data異動 {DateTime.Now.ToString("yyyyMMdd")}"; //信件主旨
string body = $"Hi All, \r\n\r\n{DateTime.Now.ToString("yyyyMMdd")} {filename}.csv更改如下表，\r\n\r\n{(datatableToHTML.ToHTML(compare_result) == null ? string.Empty : datatableToHTML.ToHTML(compare_result))}\r\n\r\n Best Regards, \r\n\r\n Vicky Yin"; //信件內容
string attachments = null; //附件
/*var fileName = @"D:\微軟MCS\SchedulerDB_Excel\" + excelname;//附件位置
* if (File.Exists(fileName.ToString()))
* {
* attachments = fileName.ToString();
* }*/
string toMailList = "[email protected];[email protected]"; //收件者
string ccMailList = ""; //CC收件者
helper.SendMail(toMailList, ccMailList, null, subject, body, null);
//Step5. 同步到各個DB
//Step5.1 讀取相對應SyncData
//Step5.2 執行同步到各個DB
}
}
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);
}