private string exportHeader(string exportPath)
{
string uploadfilename = "PFIZER_NUC_PCMS_" + DateTime.UtcNow.ToString("yyyyMMddTHHmmss") + ".txt";
string fullpath = Path.Combine(exportPath, uploadfilename);
using (ImpersonateUser u = new ImpersonateUser())
{
DirectoryInfo di = new DirectoryInfo(exportPath);
if (di.Exists == false)
{
di.Create();
}
StreamWriter fileStream = new StreamWriter(fullpath, true);
try
{
fileStream.WriteLine(N360File.exportHeader());
}
catch (Exception e)
{
JobLog(myChannel, BatchStatus.Error, e.Message);
SMTPHelper.SendAlertInterface(myChannel.name, e.Message);
return(null);
}
finally
{
fileStream.Close();
fileStream.Dispose();
}
}
return(fullpath);
}
private bool SendMail(EmployeeMailTemplate model)
{
try
{
SMTPHelper smtpHelper = new SMTPHelper();
char[] symbols = new char[] { ';', ' ', ',', '\r', '\n' };
if (model != null)
{
string[] ToEmailId = model.To.Split(symbols);
//Loop to seperate email id's of CC peoples
string[] CCEmailId = null;
if (model.Cc != "" && model.Cc != null)
{
CCEmailId = model.Cc.Split(symbols);
string[] CCEmailIds = CCEmailId.Where(s => !String.IsNullOrEmpty(s)).ToArray();
return(smtpHelper.SendMail(ToEmailId, null, CCEmailIds, null, null, null, model.From, null, model.Subject, model.Message, null, null));
}
else
{
return(smtpHelper.SendMail(ToEmailId, null, null, null, null, null, model.From, null, model.Subject, model.Message, null, null));
}
}
else
{
return(false);
}
}
catch (Exception)
{
return(false);
}
}
private void PostMail(MailBaseData _MailBaseData, string recipientsAddressRange,
string MailSubject, string Describe, string File_Path)
{
MailSubject = MailSubject + " (" + WIPCommon.ForamtCurDateTime() + ")";//任务名称
Describe += "<br/><br/>";
Describe += "消息来自主机：" + WIPCommon.GetHostName() + "<br/>";
Describe += "主机发生时间：" + WIPCommon.ForamtCurDateTime() + "<br/>";
IDictionary <string, object> logDict = new Dictionary <string, object>();
logDict.Add("recipientsAddressRange", recipientsAddressRange);
logDict.Add("MailSubject", MailSubject);
logDict.Add("Describe", Describe);
logDict.Add("File_Path", File_Path);
ExceptionInfoEntity exception = WipLogHelper.GetExceptionInfo <IDictionary <string, object> >(namespaceName, "MailSending", logDict, "", "");
try
{
if (File_Path == null)
{
File_Path = "";
}
SMTPHelper.MailSending(_MailBaseData, _recipientsAddressRange, MailSubject, Describe.ToString(), "");
}
catch (Exception ex)
{
WipLogHelper.GetExceptionInfoForError(ex, ref exception);
WipLogHelper.WriteExceptionInfo(exception);
}
}
public ActionResult RecuperarSenha(string email)
{
if (email == "")
{
ViewBag.Erro = "Digite seu e-mail";
return(View());
}
Usuario usuario = obterUsuarioPorEmail(email);
if (usuario == null)
{
ViewBag.Erro = "O e-mail digitado não é válido";
return(View());
}
MensagemEmail mensagem = new MensagemEmail();
mensagem.Mensagem = String.Format("Olá!\n Você solicitou a recuperação de senha. Sua senha é: {0}", usuario.senha);
mensagem.Assunto = "Recuperação da Senha do Projeto Loja";
mensagem.Remetente = "*****@*****.**";
mensagem.Destinatario = email;
try
{
SMTPHelper.EnviarEmail(mensagem);
}
catch (Exception ex)
{
ViewBag.Erro = ex.Message;
}
return(RedirectToAction("Login"));
}
public void SendingMailToReportingPerson()
{
try
{
dsReportingTo = objLeaveDeatilsBOL.GetReportingTo(objLeaveDeatilsModel);
if (dsReportingTo.Tables[0].Rows.Count > 0)
{
//Send new password to the employee through Email.
MailMessage objMailMessage = new MailMessage();
objMailMessage.To.Add(new MailAddress(dsReportingTo.Tables[0].Rows[0]["EmployeeEmailID"].ToString()));
string strBody;
dsCancelDetails = objCompensationBOL.GetCancelCompOffDetails(objCompensationModel);
for (int k = 0; k < dsReportingTo.Tables[1].Rows.Count; k++)
{
if (dsReportingTo.Tables[1].Rows[k]["ConfigItemName"].ToString() == "From EmailID")
{
objMailMessage.From = new MailAddress(dsReportingTo.Tables[1].Rows[k]["ConfigItemValue"].ToString(), dsReportingTo.Tables[1].Rows[k]["ConfigItemValue"].ToString());
}
if (dsReportingTo.Tables[1].Rows[k]["ConfigItemName"].ToString() == "Mail Server Name")
{
break;
}
}
objMailMessage.Subject = "Updated Compensatory Leave Details";
strBody = "<font style= color:Navy;font-size:smaller;font-family:Arial>" + "Hi ##EmployeeName##," + " \n\n" + " Updated Compensatory Leave Application Details: " + " \n\n" + " Applied For: ##AppliedFor##" + " \n" + " Reason: ##Reason##" + "\n\n" + "Update the Approved Compensatory Leave Details, the required updates are made in the system.";
strBody = Regex.Replace(strBody, "##Environment##", Environment.NewLine);
strBody = Regex.Replace(strBody, "##ApproverName##", dsReportingTo.Tables[0].Rows[0]["ReporterName"].ToString());
strBody = Regex.Replace(strBody, "##EmployeeName##", dsReportingTo.Tables[0].Rows[0]["EmployeeName"].ToString());
strBody = Regex.Replace(strBody, "##AppliedFor##", Convert.ToDateTime(dsCancelDetails.Tables[0].Rows[0]["AppliedFor"].ToString()).ToShortDateString());
strBody = Regex.Replace(strBody, "##Reason##", dsCancelDetails.Tables[0].Rows[0]["Reason"].ToString());
objMailMessage.Body = strBody;
SMTPHelper objhelper = new SMTPHelper();
objhelper.SendMail(objMailMessage.From.ToString(), objMailMessage.To.ToString(), objMailMessage.Subject, objMailMessage.Body, null, null, 1);
}
}
catch (V2Exceptions ex)
{
throw;
}
catch (System.Exception ex)
{
FileLog objFileLog = FileLog.GetLogger();
objFileLog.WriteLine(LogType.Error, ex.Message, "CompensationApproval.aspx.cs", "SendingMailToReportingPerson", ex.StackTrace);
throw new V2Exceptions(ex.ToString(), ex);
}
}
protected void btnSend_Click(object sender, EventArgs e)
{
SMTPHelper smtp = new SMTPHelper();
if (!smtp.Send("*****@*****.**", "wwwww", "eeeeeeeeeeeee"))
{
lblStatus.Text = smtp.SMTPError;
lblStatus.Visible = true;
}
else
{
lblStatus.Text = string.Empty;
lblStatus.Visible = false;
}
}
private string exportData(List <PforceRXFile> datalist, string exportPath, string backuppath)
{
string uploadfilename = "KR_CONSENT_IN_" + DateTime.UtcNow.ToString("yyyyMMdd") + ".txt";
string fullpath = Path.Combine(exportPath, uploadfilename);
using (ImpersonateUser u = new ImpersonateUser())
{
DirectoryInfo di = new DirectoryInfo(exportPath);
if (di.Exists == false)
{
di.Create();
}
StreamWriter fileStream = new StreamWriter(new FileStream(fullpath, FileMode.OpenOrCreate, FileAccess.ReadWrite), Encoding.UTF8);
try
{
fileStream.WriteLine(PforceRXFile.exportHeader());
foreach (PforceRXFile n in datalist)
{
fileStream.WriteLine(n.Export());
}
}
catch (Exception e)
{
JobLog(myChannel, BatchStatus.Error, e.Message);
SMTPHelper.SendAlertInterface(myChannel.name, e.Message);
return(null);
}
finally
{
fileStream.Close();
fileStream.Dispose();
}
// 백업
try
{
File.Copy(fullpath, Path.Combine(backuppath, uploadfilename), true);
}
catch (Exception e)
{
JobLog(myChannel, BatchStatus.Error, e.Message);
SMTPHelper.SendAlertInterface(myChannel.name, e.Message);
return(null);
}
}
return(fullpath);
}
protected void btnAceptar_Click(object sender, EventArgs e)
{
DateTime birthdate;
if (DateTime.TryParse(txtFecha.Text, out birthdate))
{
PassRecover PassRecover = passRecoverSession(birthdate);
if (PassRecover.IsEmailAndBirthdayCorrect() && ValidateCaptcha())
{
if (HasPasswordRecoveryPermission(PassRecover))
{
string code = RandomString(5);
PassRecover.Code = code;
string message = string.Format("Usted ha solicitado una recuperación de contraseña en el sistema de ProcessSA, su código de recuperación es:" +
"</br>" +
"<h1>{0}</h1>" +
"</br>" +
"En caso que usted no haya solicitado este cambio, ignore este mensaje", code);
SMTPHelper SMTPHelper = new SMTPHelper("*****@*****.**", PassRecover.Email, message, "Recuperación de contraseña", null);
if (SMTPHelper.enviaMail())
{
Session["PassRecover"] = PassRecover;
Response.Redirect("CodigoContraseña.aspx");
}
else
{
lblErrorMessage.Text = "Error del servidor al enviar el mensaje, contacte al administrador";
}
}
else
{
lblErrorMessage.Text = "Su cuenta no posee el permiso de recuperar contraseña";
}
}
else
{
lblErrorMessage.Text = "El email, la fecha de nacimiento o el captcha son inválidos, número de intentos restantes: " + (3 - PassRecover.NumberOfTries);
CheckNumberOfTries(PassRecover.NumberOfTries);
}
}
else
{
lblErrorMessage.Text = "Error en lectura de fecha";
}
}
public void Execute()
{
JobLog(BatchStatus.Begin);
myChannel = dbContext.Channels.Find(myChannel.ID);
//CommonUtil.LogWrite("BatchStatus.Begin");
Channel channel = dbContext.Channels.Where(c => c.name == @"N360" && c.bound == BoundType.Outbound).FirstOrDefault();
//ChannelStatus(channel, ActionStatus.Running);
//CommonUtil.LogWrite("ActionStatus.Running");
// 미처리 채널 데이터를 파일로 export
string uploadfilename = exportHeader(channel.path);
//CommonUtil.LogWrite("exportHeader");
try
{
if (countTargetData() > 0)
{
//CommonUtil.LogWrite("initTargetData Before");
List <N360File> flist = initTargetData();
// 파일생성
//CommonUtil.LogWrite("initTargetData After");
exportData(flist, channel.path, uploadfilename);
//CommonUtil.LogWrite("exportData ");
}
// 업로드
if (!string.IsNullOrEmpty(channel.exportpath))
{
XferHelper.Call(XferHelper.XferActionEnum.Export, channel.path, channel.exportpath);
}
else
{
XferHelper.Call(XferHelper.XferActionEnum.Export, @"xfer_n360_export.txt");
}
}
catch (Exception e)
{
JobLog(myChannel, BatchStatus.Error, e.Message);
SMTPHelper.SendAlertInterface(myChannel.name, e.Message);
}
// 백업
CollectionLog(channel, uploadfilename, channel.exportpath, CollectionStatus.UPLOAD);
//added by venkat for use service accoutn while accessing file son 5th march 2020
using (ImpersonateUser u = new ImpersonateUser())
{
File.Move(uploadfilename, Path.Combine(channel.backuppath, Path.GetFileName(uploadfilename)));
}
JobLog(BatchStatus.Completed);
}
public JsonResult DecideRequest(int id, int action, string message)
{
Approval approval = db.Approvals.Find(id);
if (approval == null)
{
return(Json(new { result = false }));
}
Privacy p = db.Privacies.Find(approval.privacyId);
if (action == 1)
{
if (!CommonUtil.checkEmail(p.EMAIL) && !CommonUtil.checkMobile(p.MOBILE))
{
return(Json(new { result = false, msg = @"일부 데이터가 적합하지 않습니다. 승인할 수 없습니다.[이메일,핸드폰]" }));
}
if (string.IsNullOrEmpty(p.CONSENT.CONSENT_SOURCE))
{
return(Json(new { result = false, msg = @"일부 데이터가 적합하지 않습니다. 승인할 수 없습니다.[동의서채널]" }));
}
approval.status = ApprovalStatus.Approved;
}
else if (action == 2)
{
approval.status = ApprovalStatus.Rejected;
}
if (!string.IsNullOrEmpty(message))
{
approval.message = message;
}
db.Entry(approval).State = EntityState.Modified;
db.SaveChanges();
if (!string.IsNullOrEmpty(p.LINK_RESERVATION) && approval.status == ApprovalStatus.Approved)
{
SMTPHelper.SendAlertPfizerLink(p);
}
if (approval.status == ApprovalStatus.Approved)
{
SMTPHelper.SendAlertApproved(p);
}
else if (approval.status == ApprovalStatus.Rejected)
{
SMTPHelper.SendAlertRejected(p);
}
return(Json(new { result = true }));
}
private void exportData(List <N360File> datalist, string exportPath, string appendfile)
{
string uploadfilename = "PFIZER_NUC_PCMS_" + DateTime.UtcNow.ToString("yyyyMMddTHHmmss") + ".txt";
string fullpath = Path.Combine(exportPath, uploadfilename);
if (!string.IsNullOrEmpty(appendfile))
{
fullpath = appendfile;
}
using (ImpersonateUser u = new ImpersonateUser())
{
DirectoryInfo di = new DirectoryInfo(exportPath);
if (di.Exists == false)
{
di.Create();
}
StreamWriter fileStream = new StreamWriter(fullpath, true);
try
{
foreach (N360File n in datalist)
{
fileStream.WriteLine(n.Export());
}
}
catch (Exception e)
{
JobLog(myChannel, BatchStatus.Error, e.Message);
SMTPHelper.SendAlertInterface(myChannel.name, e.Message);
}
finally
{
fileStream.Close();
fileStream.Dispose();
}
}
}
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);
}
public JsonResult MailApi()
{
bool result = SMTPHelper.TestMail();
return(Json(new { result = result }, JsonRequestBehavior.AllowGet));
}
private void Display(bool endAnalyse)
{
SimpleLog.Log("Finalise...");
if (Analyser.Config.Duplicate)
{
dgvDoublons.DataSource = _dvDoublons;
if (_dvDoublons.Count < 5000)
{
dgvDoublons.AutoResizeColumns(
DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);
}
hideColumnFromList(dgvDoublons, new string[] { "Erreur", "Chemin", "isDuplicate", "AnalysisPath", "debug" });
if (Analyser.Config.Bdd)
{
dgvDoublons.Columns["id"].Visible = false;
}
hideQualifierColumn(dgvDoublons);
ResourceHelper.setHeaders(dgvDoublons);
Finalize(endAnalyse);
}
UpdateChemin();
_dvErreurs = new DataView(Analyser.Dset.Tables[0], "Erreur<>''", "Fichier asc", DataViewRowState.CurrentRows);
dgvErreurs.DataSource = _dvErreurs;
dgvErreurs.AutoResizeColumns(
DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);
hideColumnFromList(dgvErreurs, new string[] { "Chemin", "Taille", "isDuplicate", "Hash", "AnalysisPath", "isOriginal", "debug" });
if (Analyser.Config.Bdd)
{
dgvErreurs.Columns["id"].Visible = false;
}
hideQualifierColumn(dgvErreurs);
ResourceHelper.setHeaders(dgvErreurs);
_dv2003 = new DataView(Analyser.Dset.Tables[0],
"Fichier like '%.doc' or Fichier like '%.rtf' or Fichier like '%.dot' or Fichier like '%.xls' or Fichier like '%.ppt'"
, "Fichier asc", DataViewRowState.CurrentRows);
dgv2003.DataSource = _dv2003;
dgv2003.AutoResizeColumns(
DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);
hideColumnFromList(dgv2003, new string[] { "Erreur", "Chemin", "isDuplicate", "Hash", "AnalysisPath", "isOriginal" });
if (Analyser.Config.Bdd)
{
dgv2003.Columns["id"].Visible = false;
}
hideQualifierColumn(dgv2003);
ResourceHelper.setHeaders(dgv2003);
if (Analyser.Config.Qualifiers.Count > 0)
{
updateQualifier();
}
else
{
tabControl1.TabPages["tabQualifier"].Hide();
}
if (endAnalyse)
{
Analyser.setStats();
}
if (endAnalyse)
{
if (Analyser.Config.Mail)
{
if (!string.IsNullOrEmpty(Analyser.Config.ReportMail))
{
SMTPHelper.sendReport(dgvDoublons, dgvErreurs);
}
}
}
if (automaticMode)
{
Application.Exit();
}
}
//private ODSMDBContext odsmDB = new ODSMDBContext();
public void Execute()
{
//ODSMDBContext odsmDB = new ODSMDBContext();
JobLog(myChannel, BatchStatus.Begin);
myChannel = dbContext.Channels.Find(myChannel.ID);
// Channel 정보 획득
// 접속
ODSMEntities odsm = new ODSMEntities();
#pragma warning disable CS0219 // The variable 'ncnt' is assigned but its value is never used
int ncnt = 0;
#pragma warning restore CS0219 // The variable 'ncnt' is assigned but its value is never used
// 마지막 동작일 가져오기
PrivacyLog logone = null;
try
{
logone = dbContext.PrivacyLogs.Where(pl => pl.creater == @"ODSM").OrderByDescending(pl => pl.createdate).First();
}
#pragma warning disable CS0168 // The variable 'e' is declared but never used
catch (Exception e) {
#pragma warning restore CS0168 // The variable 'e' is declared but never used
logone = null;
}
try
{
if (logone == null)
{
logone = dbContext.PrivacyLogs.Where(pl => pl.creater == @"TASK" && (pl.changes.Contains(@"NucleusKey") || pl.changes.Contains(@"OneKey"))).OrderByDescending(pl => pl.createdate).First();
}
}
#pragma warning disable CS0168 // The variable 'e' is declared but never used
catch (Exception e)
#pragma warning restore CS0168 // The variable 'e' is declared but never used
{
logone = null;
}
try
{
IEnumerable <VW_CONSENT> list = null;
if (logone != null)
{
list = odsm.VW_CONSENT.Where(vc => vc.ODSK_MODIFY_DATE >= logone.createdate).ToList();
}
else
{
list = odsm.VW_CONSENT.ToList();
}
foreach (VW_CONSENT va in list)
{
Privacy privacy = dbContext.Privacies.Where(p => p.pcmsid.Equals(va.PCMS_ID)).FirstOrDefault();
if (privacy != null && (privacy.NucleusKey != va.NUC_ID || privacy.OneKey != va.ONEKEY))
{
List <string> tracelist = new List <string>();
if (!IsEqual(privacy.NucleusKey, va.NUC_ID))
{
tracelist.Add("NucleusKey : " + privacy.NucleusKey + " => " + va.NUC_ID);
privacy.NucleusKey = va.NUC_ID;
}
if (!IsEqual(privacy.OneKey, va.ONEKEY))
{
tracelist.Add("OneKey : " + privacy.OneKey + " => " + va.ONEKEY);
privacy.OneKey = va.ONEKEY;
}
privacy.status = Status.PrivacyStatus.GRANTED;
dbContext.Entry(privacy).State = System.Data.Entity.EntityState.Modified;
if (tracelist.Count() > 0)
{
string changes = tracelist.Aggregate((a, b) => a + ", " + b);
dbContext.PrivacyLogs.Add(new PrivacyLog {
Privacy = privacy, creater = @"ODSM", changes = changes
});
}
}
}
dbContext.SaveChanges();
}
catch (Exception e) {
dbContext.SaveChanges();
JobLog(myChannel, BatchStatus.Error, e.Message);
SMTPHelper.SendAlertInterface(myChannel.name, e.Message);
}
JobLog(myChannel, BatchStatus.Completed);
}
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
}
}
// Every action starts from here.
public void Run()
{
LogHelper logHelper = new LogHelper(_configuration, "MeterService");
var sendEmail = false;
try
{
// step 1. Get all the files from directories
DirectioryHelper directioryHelper = new DirectioryHelper(_logger, _configuration);
IEnumerable <string> workingDirectories = directioryHelper.GetAllWorkingDirectoryFullPath();
// step 2. Read data from files
foreach (string directory in workingDirectories)
{
logHelper.InsertLog(new LogObject(LogType.Information, ("Start to process: " + directory)));
// step 2.1 Check if the directory is empty
if (directioryHelper.HasFile(directory))
{
var files = Directory.EnumerateFiles(directory);
string excelVersion = string.Empty;
CSVHelper csvHelper = new CSVHelper();
ExcelHelper excelHelper = new ExcelHelper();
DataTable unikDataTable = new DataTable();
// 2.2 Read the Unik data
try
{
string unik = _configuration.GetValue <string>("Unik:Vendor");
unik = UtilHelper.ConvertToLatin1(unik);
unikDataTable = excelHelper.ReadDataAsDataTable(unik, "12.0");
}
catch (Exception ex)
{
logHelper.InsertLog(new LogObject(LogType.Error, "Reading Unik configuration file failed. " + ex.Message));
}
foreach (var file in files)
{
try
{
FileInfo fileInfo = new FileInfo(file);
string newFileName = string.Empty;
string newErrorFileName = string.Empty;
DataTable outputDataTable = new DataTable();
// 3. Get company name based on directory
var company = getCompanyByDirectoryName(directory);
logHelper.InsertLog(new LogObject(LogType.Information, ("Ready to process file: " + fileInfo.FullName)));
// 4. Reading from files.
if (fileInfo.Extension == ".csv")
{
// handling CSV files
outputDataTable = csvHelper.ReadDataAsDataTable(file);
var stringArray = string.Join(", ", outputDataTable.Rows[0].ItemArray);
var isNumeric = Regex.Match(stringArray, "[0-9]", RegexOptions.IgnoreCase).Success;
if (!isNumeric)
{
outputDataTable.Rows[0].Delete();
outputDataTable.AcceptChanges();
}
}
else if (fileInfo.Extension == ".xlsx" || fileInfo.Extension == ".xlsb")
{
// handling Excel files
excelVersion = "12.0";
outputDataTable = excelHelper.ReadDataAsDataTable(file, excelVersion);
}
else if (fileInfo.Extension == ".xls")
{
// handling Excel files
excelVersion = "8.0";
outputDataTable = excelHelper.ReadDataAsDataTable(file, excelVersion);
}
else
{
logHelper.InsertLog(new LogObject(LogType.Warning, (fileInfo.Extension + " file is not supported.")));
throw new FileLoadException("File format is not supported");
}
// 4.1 Create factormodel based on company, outputdata and data from unik
FactorModel factorModel = BusinessLogic.CreateFactorModelByCompany(company, unikDataTable, outputDataTable);
if (factorModel == null)
{
throw new Exception("Vendor configuration error. Cannot create factor model based based on vendor information.");
}
// 5. Filter source data.
IEnumerable <DataRow> filteredData = BusinessLogic.FilterDataByCompany(outputDataTable, factorModel, company);
IEnumerable <DataRow> errorData = BusinessLogic.ErrorDataByCompany(outputDataTable, factorModel, company);
// 6. Save data to file.
if (filteredData.Count() > 0)
{
newFileName = csvHelper.SaveDataToFile(filteredData, factorModel, company, directory, ConfigVariables.OutputFileNameSuffix);
}
if (errorData.Count() > 0)
{
newErrorFileName = csvHelper.SaveDataToFile(errorData, factorModel, company, directory, ConfigVariables.ErrorFileNameSuffix);
}
if (String.IsNullOrEmpty(newFileName))
{
throw new NullReferenceException("Error for generating new file name.");
}
else
{
directioryHelper.MoveFile(directory, newFileName, BDOEnum.FileMoveOption.Processed);
logHelper.InsertLog(new LogObject(LogType.Information, (newFileName + " has been moved to Processed dictory")));
}
if (String.IsNullOrEmpty(newErrorFileName))
{
throw new NullReferenceException("Error for generating new error file name.");
}
else
{
logHelper.InsertLog(new LogObject(LogType.Information, (newErrorFileName + " has been moved to Manual dictory")));
directioryHelper.MoveFile(directory, newErrorFileName, BDOEnum.FileMoveOption.Manual);
}
// 7. Move processed file to archive
directioryHelper.MoveFile(directory, file, BDOEnum.FileMoveOption.Archive);
logHelper.InsertLog(new LogObject(LogType.Information, (file + " has been moved to Archive dictory")));
sendEmail = true;
}
catch (Exception ex)
{
_logger.LogError("Processing file: " + file + " is failed.");
_logger.LogError(ex.Message);
logHelper.InsertLog(new LogObject(LogType.Error, ex.Message));
// step 4. Move processed file to error
directioryHelper.MoveFile(directory, file, BDOEnum.FileMoveOption.Error);
logHelper.InsertLog(new LogObject(LogType.Information, (file + " has been moved to Error dictory")));
}
}
}
else
{
_logger.LogInformation(directory + " has no file to work on.");
logHelper.InsertLog(new LogObject(LogType.Information, (directory + " has no file to work on.")));
}
}
// send notification
if (sendEmail)
{
SMTPHelper smtpHelper = new SMTPHelper(_configuration);
string emailBody = "Hej ";
emailBody += Environment.NewLine + Environment.NewLine;
emailBody += "Datakværnen har behandlet alle filerne i mappen.";
emailBody += Environment.NewLine + Environment.NewLine;
emailBody += "Dette er en autogenereret mail og kan ikke besvares.";
emailBody += Environment.NewLine + Environment.NewLine;
emailBody += "/Datakværn service";
smtpHelper.SendEmailAsync(emailBody);
}
}
catch (Exception ex)
{
_logger.LogError(ex.Message);
logHelper.InsertLog(new LogObject(LogType.Error, ex.Message));
}
}
public void Execute()
{
bool bTrjStarted = false;
JobLog(BatchStatus.Begin);
myChannel = dbContext.Channels.Find(myChannel.ID);
log.Info(MethodBase.GetCurrentMethod().DeclaringType.Name);
try
{
using (ImpersonateUser u = new ImpersonateUser())
{
// 가져오기
var filter = new Regex(@"^KR_CONSENT_OUT_\d{8}.txt$");
if (Directory.Exists(myChannel.path))
{
DirectoryInfo di = new DirectoryInfo(myChannel.path);
var files = di.GetFiles().Where(f => filter.IsMatch(f.Name)).ToList();
foreach (var item in files)
{
//if (!CheckFile(myChannel, item.Name)) comment by Venkat for apaccr-287
if (!CheckFile(myChannel, item.FullName))
{
// 찾았음 표시
CollectionLog(myChannel, item.Name, item.FullName, CollectionStatus.DOWNLOAD);
}
}
}
ODSMEntities ODSM = new ODSMEntities();
// Parsing
List <Collection> paringlist = dbContext.Collections.Where(c => c.status == CollectionStatus.DOWNLOAD && c.channelId == myChannel.ID).ToList();
foreach (Collection pfile in paringlist)
{
// 파일이 없는 경우
if (!File.Exists(Path.Combine(myChannel.path, pfile.name)))
{
Collection one = dbContext.Collections.Find(pfile.ID);
one.status = CollectionStatus.ERROR;
dbContext.SaveChanges();
continue;
}
// 실제 파일 입력
using (DbContextTransaction dbTran = upsertContext.Database.BeginTransaction())
{
bTrjStarted = true;
try
{
// 임포트
string line;
PforceRXFile f = null;
StreamReader file = new StreamReader(Path.Combine(myChannel.path, pfile.name));
Collection one = upsertContext.Collections.Find(pfile.ID);
one.status = CollectionStatus.COMPLETED;
line = file.ReadLine();
//added by venkat
/*Changes done as part of APACCR-287
* Assuming will get Opt Out records first and later Opt In records
* Inserting "Opt Out" record in database if not avaiable and skipiing remaining "Opt In" record in that file with respective to nucleus id and (email/phone)
* done bug fix while loging in collection log changed to pass "full name" of file alonng with ftp details.
* */
List <Privacy> inserteddata = new List <Privacy>();
// end of comment
while ((line = file.ReadLine()) != null)
{
string[] arr = line.Split('|');
f = new PforceRXFile(one, arr);
List <Privacy> templist = upsertContext.Privacies.Where(pt => pt.NucleusKey == f.IND_ID && ((pt.EMAIL != null && pt.EMAIL == f.CONSENT_EMAIL) || (pt.MOBILE != null && pt.MOBILE.Replace("-", "") == f.CONSENT_MOBILE.Replace("-", "")))).ToList();
if (inserteddata.Where(insert => insert.NucleusKey == f.IND_ID && ((insert.EMAIL != null && insert.EMAIL == f.CONSENT_EMAIL) || (insert.MOBILE != null && insert.MOBILE.Replace("-", "") == f.CONSENT_MOBILE.Replace("-", "")))).ToList().Count() == 0)
{
// 원키나 PCMSID가 아니라 N키여서 여러 항목이 동시에 삭제될 가능성이 있음.
if (templist.Count() > 0 && !string.IsNullOrEmpty(f.IND_ID) && f.CONSENT_STATUS == false)
{
//comment below line as part of apaccr-287 by venkat on 3/9/2020
/*List<Privacy> templist = upsertContext.Privacies.Where(pt => pt.NucleusKey == f.IND_ID).ToList(); */
foreach (Privacy p in templist)
{
/* comment as part of apaccr-287 by venkat on 3/9/2020
* p.IND_FIRSTNAME = "*****";
* p.IND_LASTNAME = "*****";
* p.IND_FULL_NAME = "*****";
* p.MOBILE = "*****";
* p.EMAIL = "*****";
* p.CONSENT.CONSENT_ABROAD = false;
* p.CONSENT.CONSENT_SIGN = false;
* p.CONSENT.CONSENT_TRUST = false;
* p.CONSENT.CONSENT_USE = false; */
p.Unsubscribe = true;
/* comment as part of apaccr-287 by venkat
* p.status = PrivacyStatus.ERASED;
* p.SENDCHANEL &= ~SendChannel.N360; */
upsertContext.Entry(p).State = System.Data.Entity.EntityState.Modified;
upsertContext.PrivacyLogs.Add(new PrivacyLog {
Privacy = p, creater = @"PFORCERX", changes = @"OptOut NucleusKey : => " + p.NucleusKey
});
upsertContext.SaveChanges();
}
}
else
{
try
{
Privacy p = new Privacy();
p.pcmsid = IdGenerater.Generater();
p.IND_ID = f.IND_ID;
p.NucleusKey = f.IND_ID;
if (f.CONSENT_EMAIL != null && f.CONSENT_EMAIL.Length > 0)
{
p.EMAIL = f.CONSENT_EMAIL;
}
if (f.CONSENT_MOBILE != null && f.CONSENT_MOBILE.Length > 0)
{
p.MOBILE = f.CONSENT_MOBILE;
}
p.CONSENT_SOURCE = f.CONSENT_SOURCE;
p.COUNTRY_CD = f.COUNTRY_CD;
p.Unsubscribe = !f.CONSENT_STATUS;
p.Consents.Add(new Consent
{
CONSENT_USE = f.CONSENT_STATUS,
CONSENT_TRUST = f.CONSENT_STATUS,
CONSENT_ABROAD = f.CONSENT_STATUS,
CONSENT_SIGN = f.CONSENT_STATUS,
CONSENT_DATE = f.CONSENT_DATE,
CONSENT_SOURCE = f.CONSENT_SOURCE
});
p.channelId = myChannel.ID;
p.status = PrivacyStatus.IMPORTED;
VW_CONSENT_ALIGNMENT vca = ODSM.VW_CONSENT_ALIGNMENT.Where(x => x.D_NUC_ID.Equals(f.IND_ID)).FirstOrDefault();
if (vca != null)
{
p.IND_FULL_NAME = vca.D_NAME;
p.ZIP = vca.H_POSTCODE;
p.WKP_NAME = vca.H_NAME;
p.WKP_TEL = vca.H_PHONE;
p.FULL_ADDR = vca.H_ADDR;
p.IND_SP = vca.D_SPEC;
p.OneKey = vca.D_ONEKEY_ID;
}
upsertContext.Privacies.Add(p);
upsertContext.SaveChanges();
//added by Venkat for apaccr-287
inserteddata.Add(p);
// end of comment
List <string> tracelist = new List <string>();
tracelist.Add("NucleusKey : => " + p.NucleusKey);
tracelist.Add("OneKey : => " + p.OneKey);
tracelist.Add("EMAIL : => " + p.EMAIL);
tracelist.Add("MOBILE : => " + p.MOBILE);
tracelist.Add("CONSENT_SOURCE : => " + p.CONSENT_SOURCE);
tracelist.Add("COUNTRY_CD : => " + p.COUNTRY_CD);
tracelist.Add("IND_FULL_NAME : => " + p.IND_FULL_NAME);
tracelist.Add("ZIP : => " + p.ZIP);
tracelist.Add("WKP_NAME : => " + p.WKP_NAME);
tracelist.Add("WKP_TEL : => " + p.WKP_TEL);
tracelist.Add("FULL_ADDR : => " + p.FULL_ADDR);
tracelist.Add("IND_SP : => " + p.IND_SP);
string changes = tracelist.Aggregate((a, b) => a + ", " + b);
upsertContext.PrivacyLogs.Add(new PrivacyLog {
Privacy = p, creater = @"PFORCERX", changes = changes
});
upsertContext.SaveChanges();
}
catch (Exception e)
{
JobLog(myChannel, BatchStatus.Error, e.Message);
SMTPHelper.SendAlertInterface(myChannel.name, e.Message);
}
}
}
}
dbTran.Commit();
bTrjStarted = false;
file.Close();
// Backup으로 이동
File.Move(Path.Combine(myChannel.path, pfile.name), Path.Combine(myChannel.backuppath, pfile.name));
}
catch (Exception e)
{
log.Error(e);
if (bTrjStarted)
{
dbTran.Rollback();
}
JobLog(myChannel, BatchStatus.Error, e.Message);
SMTPHelper.SendAlertInterface(myChannel.name, e.Message);
}
finally
{
dbTran.Dispose();
}
}
}
}
ChannelStatus(myChannel, ActionStatus.Waiting);
JobLog(BatchStatus.Completed);
}
catch (Exception e)
{
//added by venkat for exception habding on 5th march 2020
JobLog(myChannel, BatchStatus.Error, e.Message);
JobLog(BatchStatus.Completed);
SMTPHelper.SendAlertInterface(myChannel.name, e.Message);
}
}
public void Execute()
{
JobLog(BatchStatus.Begin);
myChannel = dbContext.Channels.Find(myChannel.ID);
try
{
// 가져오기
//added by venkat for use service account while accessing file son 5th march 2020
using (ImpersonateUser u = new ImpersonateUser())
{
using (SftpClient sftp = new SftpClient(myChannel.host, myChannel.account, myChannel.pwd))
{
try
{
sftp.Connect();
sftp.ChangeDirectory(myChannel.path);
}
#pragma warning disable CS0168 // The variable 'e' is declared but never used
catch (Exception e)
#pragma warning restore CS0168 // The variable 'e' is declared but never used
{
JobLog(myChannel, BatchStatus.Error, @"Cannot connect SFTP:" + myChannel.host + "[" + myChannel.account + "/" + myChannel.pwd + "]");
JobLog(BatchStatus.Completed);
SMTPHelper.SendAlertInterface(myChannel.name, @"Cannot connect SFTP:" + myChannel.host + "[" + myChannel.account + "/" + myChannel.pwd + "]");
return;
}
var filter = new Regex(@"^mms_\d{8}_pcms.csv$");
var files = sftp.ListDirectory(myChannel.path).Where(f => filter.IsMatch(f.Name)).ToList();
foreach (var file in files)
{
if (CheckFile(myChannel, file.FullName))
{
// 이미 존재 패스
}
else
{
// 찾았음 표시
CollectionLog(myChannel, file.Name, file.FullName, CollectionStatus.FIND);
}
}
// 다운로드
var downloadlist = dbContext.Collections.Where(c => c.status == CollectionStatus.FIND && c.channelId == myChannel.ID);
try
{
foreach (var clt in downloadlist)
{
Stream fileStream = File.OpenWrite(Path.Combine(myChannel.exportpath, clt.name));
sftp.DownloadFile(clt.ftpname, fileStream);
CollectionLog(clt, CollectionStatus.DOWNLOAD);
fileStream.Flush();
fileStream.Close();
}
}
catch (Exception e)
{
//added by venkat for exception habding on 5th march 2020
JobLog(myChannel, BatchStatus.Error, e.Message);
JobLog(BatchStatus.Completed);
SMTPHelper.SendAlertInterface(myChannel.name, e.Message);
return;
}
sftp.Disconnect();
}
// Parsing
List <Collection> collectionlist = dbContext.Collections.Where(c => c.channelId == myChannel.ID && c.status == CollectionStatus.DOWNLOAD).ToList();
foreach (Collection collection in collectionlist)
{
string line;
StreamReader file;
try
{
file = new StreamReader(Path.Combine(myChannel.exportpath, collection.name));
}
catch (Exception e)
{
continue;
}
line = file.ReadLine();
try
{
List <MMSFile> mms = new List <MMSFile>();
while ((line = file.ReadLine()) != null)
{
string[] arr = line.Split('|');
mms.Add(new MMSFile(collection, arr));
}
file.Close();
// privacy 추가
foreach (MMSFile m in mms)
{
Privacy p = new Privacy(
m.COMPANY // WKP_NAME
, m.TEL // WKP_TEL
, m.ZIP // ZIP
, string.Empty // PROVINCE
, string.Empty // CITY
, m.ADDRESS1 // DONG
, m.ADDRESS2 // STREET
, m.ADDRESS1 + " " + m.ADDRESS2 // FUKKADDR
, m.CATEGORY // IND_SP
, string.Empty // TITLE
, m.NAME // FULL_NAME
, m.EMAIL // EMAIL
, m.HP // MOBILE
, "MMS" // CONSENT_SOURCE
, m.SUB_CHANNEL // CONSENT_SUB_SOURCE
, string.Empty, string.Empty // LINK Re / LINK PHONE
, CommonUtil.toUtcDT(m.AGR_DATE)
, m.AGR_VER // CONSENT_VERSION
, chkOptIn2(m.AGREE1)
, chkOptIn2(m.AGREE2)
, chkOptIn2(m.AGREE3)
, true
, !chkOptIn2(m.RCV_MAIL)
, true
);
p.Channel = myChannel;
p.CONSENT_SOURCE = @"MMS";
bool bExist = false;
List <Privacy> tmp = dbContext.Privacies.Where(pp => pp.EMAIL == m.EMAIL && pp.CONSENT_SOURCE.Equals(@"MMS")).ToList();
foreach (Privacy pitem in tmp)
{
bExist = true;
Privacy priv = pitem;
List <string> tracelist = new List <string>();
if (!IsEqual(m.COMPANY, priv.WKP_NAME))
{
tracelist.Add("근무처(병원명) : " + priv.WKP_NAME + " => " + m.COMPANY);
priv.WKP_NAME = m.COMPANY;
}
if (!IsEqual(m.TEL, priv.WKP_TEL))
{
tracelist.Add("근무지연락처 : " + priv.WKP_TEL + " => " + m.TEL);
priv.WKP_TEL = m.TEL;
}
if (!IsEqual(m.ZIP, priv.ZIP))
{
tracelist.Add("우편번호 : " + priv.ZIP + " => " + m.ZIP);
priv.ZIP = m.ZIP;
}
if (!IsEqual(m.ADDRESS1, priv.DONG))
{
tracelist.Add("주소(군/구/동) : " + priv.DONG + " => " + m.ADDRESS1);
priv.DONG = m.ADDRESS1;
}
if (!IsEqual(m.ADDRESS2, priv.STREET))
{
tracelist.Add("상세주소 : " + priv.STREET + " => " + m.ADDRESS2);
priv.STREET = m.ADDRESS2;
}
string tmpaddr = m.ADDRESS1 + " " + m.ADDRESS2;
if (!IsEqual(tmpaddr, priv.FULL_ADDR))
{
tracelist.Add("주소 : " + priv.FULL_ADDR + " => " + tmpaddr);
priv.FULL_ADDR = tmpaddr;
}
if (!IsEqual(m.CATEGORY, priv.IND_SP))
{
tracelist.Add("진료과 : " + priv.IND_SP + " => " + m.CATEGORY);
priv.IND_SP = m.CATEGORY;
}
if (!IsEqual(m.NAME, priv.IND_FULL_NAME))
{
tracelist.Add("고객명 : " + priv.IND_FULL_NAME + " => " + m.NAME);
priv.IND_FULL_NAME = m.NAME;
}
if (!IsEqual(m.HP, priv.MOBILE))
{
tracelist.Add("핸드폰 : " + priv.MOBILE + " => " + m.HP);
priv.MOBILE = m.HP;
}
if (!IsEqual(m.SUB_CHANNEL, priv.CONSENT_SUB_SOURCE))
{
tracelist.Add("채널(부) : " + priv.CONSENT_SUB_SOURCE + " => " + m.SUB_CHANNEL);
priv.CONSENT_SUB_SOURCE = m.SUB_CHANNEL;
}
if (!chkOptIn2(m.RCV_MAIL) != priv.Unsubscribe)
{
tracelist.Add("수신거부 : " + priv.Unsubscribe + " => " + !chkOptIn2(m.RCV_MAIL));
priv.Unsubscribe = !chkOptIn2(m.RCV_MAIL);
}
// 동의서 변경시
if (priv.CONSENT == null)
{
priv.Consents.Add(new Consent
{
CONSENT_DATE = CommonUtil.toUtcDT(m.AGR_DATE),
CONSENT_VERSION = m.AGR_VER,
CONSENT_USE = chkOptIn2(m.AGREE1),
CONSENT_TRUST = chkOptIn2(m.AGREE2),
CONSENT_ABROAD = chkOptIn2(m.AGREE3),
CONSENT_SIGN = true,
CONSENT_SOURCE = "MMS"
});
}
else if (priv.CONSENT != null && CommonUtil.toUtcDT(m.AGR_DATE) != priv.CONSENTDATE)
{
priv.Consents.Add(new Consent
{
CONSENT_DATE = CommonUtil.toUtcDT(m.AGR_DATE),
CONSENT_VERSION = m.AGR_VER,
CONSENT_USE = chkOptIn2(m.AGREE1),
CONSENT_TRUST = chkOptIn2(m.AGREE2),
CONSENT_ABROAD = chkOptIn2(m.AGREE3),
CONSENT_SIGN = true,
CONSENT_SOURCE = "MMS"
});
}
// 변경사항 저장
if (tracelist.Count() > 0)
{
string changes = tracelist.Aggregate((a, b) => a + ", " + b);
dbContext.PrivacyLogs.Add(new PrivacyLog {
Privacy = priv, creater = @"MMS", changes = changes
});
}
dbContext.Entry(priv).State = System.Data.Entity.EntityState.Modified;
dbContext.SaveChanges();
} // for 존재하면 업데이트
if (bExist == false)
{
// mms 에서 온 데이터가 없는 경우 새로 추가
dbContext.Privacies.Add(p);
dbContext.SaveChanges();
List <string> tracelist = new List <string>();
tracelist.Add("근무처(병원명) : => " + p.WKP_NAME);
tracelist.Add("근무지연락처 : => " + p.WKP_TEL);
tracelist.Add("우편번호 : => " + p.ZIP);
tracelist.Add("주소 : => " + p.FULL_ADDR);
tracelist.Add("진료과 : => " + p.IND_SP);
tracelist.Add("고객명 : => " + p.IND_FULL_NAME);
tracelist.Add("핸드폰 : => " + p.MOBILE);
tracelist.Add("채널(부) : => " + p.CONSENT_SUB_SOURCE);
tracelist.Add("채널 : => " + p.CONSENT_SOURCE);
tracelist.Add("수신거부 : => " + p.Unsubscribe);
string changes = tracelist.Aggregate((a, b) => a + ", " + b);
dbContext.PrivacyLogs.Add(new PrivacyLog {
Privacy = p, creater = @"MMS", changes = changes
});
dbContext.SaveChanges();
}
}
}
catch (Exception e)
{
JobLog(myChannel, BatchStatus.Error, e.Message);
SMTPHelper.SendAlertInterface(myChannel.name, e.Message);
}
finally
{
if (file != null)
{
file.Dispose();
}
}
// 완료처리
collection.status = CollectionStatus.COMPLETED;
dbContext.Entry(collection).State = System.Data.Entity.EntityState.Modified;
dbContext.SaveChanges();
// 백업처리
try
{
File.Move(Path.Combine(myChannel.exportpath, collection.name), Path.Combine(myChannel.backuppath, collection.name));
}
#pragma warning disable CS0168 // The variable 'e' is declared but never used
catch (Exception e)
{
#pragma warning restore CS0168 // The variable 'e' is declared but never used
File.Move(Path.Combine(myChannel.exportpath, collection.name), Path.Combine(myChannel.backuppath, Guid.NewGuid().ToString()));
}
}
}
}
catch (Exception e)
{
//added by venkat for exception habding on 5th march 2020
JobLog(myChannel, BatchStatus.Error, e.Message);
JobLog(BatchStatus.Completed);
SMTPHelper.SendAlertInterface(myChannel.name, e.Message);
}
JobLog(BatchStatus.Completed);
}