/// <summary>
///
/// </summary>
/// <param name="p_invoicerId"></param>
/// <returns></returns>
public int ClearXFlag(string p_invoicerId)
{
IReporter.WriteDebug(p_invoicerId);
var _sqlstr
= "UPDATE TB_eTAX_ISSUING "
+ " SET isNTSSending=@isNTSSending, isNTSReport=@isNTSReport "
+ " FROM TB_eTAX_ISSUING a INNER JOIN TB_eTAX_INVOICE b "
+ " ON a.issueId=b.issueId "
+ " WHERE a.isNTSSending=@isNTSSendingX "
+ " AND ( "
+ " (RIGHT(b.typeCode, 2) IN ('01', '02', '04') AND b.invoicerId=@invoicerId) "
+ " OR "
+ " (RIGHT(b.typeCode, 2) IN ('03', '05') AND b.brokerId=@invoicerId) "
+ " ) ";
var _dbps = new PgDatParameters();
{
_dbps.Add("@isNTSSending", NpgsqlDbType.Varchar, "F");
_dbps.Add("@isNTSReport", NpgsqlDbType.Varchar, "F");
_dbps.Add("@isNTSSendingX", NpgsqlDbType.Varchar, "X");
_dbps.Add("@invoicerId", NpgsqlDbType.Varchar, p_invoicerId);
}
return(LSQLHelper.ExecuteText(UAppHelper.ConnectionString, _sqlstr, _dbps));
}
/// <summary>
///
/// </summary>
/// <param name="p_invoicerId"></param>
/// <returns></returns>
public int ClearXFlag(string p_invoicerId)
{
ISigner.WriteDebug(p_invoicerId);
var _sqlstr
= "UPDATE TB_eTAX_INVOICE "
+ " SET isIssued=@isIssued, isSuccess=@isSuccess "
+ " WHERE isIssued=@isIssuedX "
+ " AND ( "
+ " (RIGHT(typeCode, 2) IN ('01', '02', '04') AND invoicerId=@invoicerId) "
+ " OR "
+ " (RIGHT(typeCode, 2) IN ('03', '05') AND brokerId=@invoicerId) "
+ " )";
var _dbps = new PgDatParameters();
{
_dbps.Add("@isIssued", NpgsqlDbType.Varchar, "F");
_dbps.Add("@isSuccess", NpgsqlDbType.Varchar, "F");
_dbps.Add("@isIssuedX", NpgsqlDbType.Varchar, "X");
_dbps.Add("@invoicerId", NpgsqlDbType.Varchar, p_invoicerId);
}
return(LSQLHelper.ExecuteText(UAppHelper.ConnectionString, _sqlstr, _dbps));
}
/// <summary>
///
/// </summary>
/// <param name="p_invoicerId"></param>
/// <param name="p_issue_id"></param>
/// <param name="p_newMailAddress"></param>
/// <returns></returns>
public int DoMailReSend(string p_invoicerId, string p_issue_id, string p_newMailAddress)
{
IMailer.WriteDebug(p_invoicerId);
int _result = -1;
var _sqlstr
= "UPDATE TB_eTAX_ISSUING "
+ " SET isInvoiceeMail=@isInvoiceeMail "
+ " FROM TB_eTAX_ISSUING a INNER JOIN TB_eTAX_INVOICE b "
+ " ON a.issueId=b.issueId "
+ " WHERE a.isMailSending!=@isMailSendingX "
+ " AND ( "
+ " (RIGHT(b.typeCode, 2) IN ('01', '02', '04') AND b.invoicerId=@invoicerId) "
+ " OR "
+ " (RIGHT(b.typeCode, 2) IN ('03', '05') AND b.brokerId=@invoicerId) "
+ " ) "
+ " AND a.issueId=@issueId";
var _dbps = new PgDatParameters();
{
_dbps.Add("@isMailSendingX", NpgsqlDbType.Varchar, "X");
_dbps.Add("@invoicerId", NpgsqlDbType.Varchar, p_invoicerId);
_dbps.Add("@isInvoiceeMail", NpgsqlDbType.Varchar, "X");
_dbps.Add("@issueId", NpgsqlDbType.Varchar, p_issue_id);
}
if (LSQLHelper.ExecuteText(UAppHelper.ConnectionString, _sqlstr, _dbps) > 0)
{
var _where = " AND a.issueId=@issueId ";
_dbps.Clear();
{
_dbps.Add("@issueId", NpgsqlDbType.Varchar, p_issue_id);
}
lock (SyncEngine)
_result = CheckReEnter(p_invoicerId, _where, _dbps);
if (_result > 0)
{
MailingArgs _args = new MailingArgs()
{
reSending = true,
invoiceeEMail = p_newMailAddress,
invoicerId = p_invoicerId,
noInvoicee = 1,
noSending = _result,
where = _where,
dbps = _dbps
};
DoMailing(_args);
}
}
return(_result);
}
private int CheckReEnter(string p_invoicerId, string p_where, PgDatParameters p_dbps)
{
int _result = -1;
// 동일한 조건으로 실행 중인 record가 1개 이상 있는지 확인 한다.
var _sqlstr
= "SELECT COUNT(a.isMailSending) as norec "
+ " FROM TB_eTAX_ISSUING a INNER JOIN TB_eTAX_INVOICE b "
+ " ON a.issueId=b.issueId "
+ " WHERE a.isMailSending=@isMailSendingX "
+ " AND ( "
+ " (RIGHT(b.typeCode, 2) IN ('01', '02', '04') AND b.invoicerId=@invoicerId) "
+ " OR "
+ " (RIGHT(b.typeCode, 2) IN ('03', '05') AND b.brokerId=@invoicerId) "
+ " ) "
+ p_where;
p_dbps.Add("@invoicerId", NpgsqlDbType.Varchar, p_invoicerId);
p_dbps.Add("@isMailSendingX", NpgsqlDbType.Varchar, "X");
var _ds = LSQLHelper.SelectDataSet(UAppHelper.ConnectionString, _sqlstr, p_dbps);
int _norec = Convert.ToInt32(_ds.Tables[0].Rows[0]["norec"]);
if (_norec < 1)
{
// 매입처 메일 발송이 안되었거나, ASP사업자에게 메일 발송이 안된경우를 선택한다.
string _updstr
= "UPDATE TB_eTAX_ISSUING "
+ " SET isMailSending=@isMailSendingX "
+ " FROM TB_eTAX_ISSUING a INNER JOIN TB_eTAX_INVOICE b "
+ " ON a.issueId=b.issueId "
+ " WHERE (a.isInvoiceeMail != @isInvoiceeMail OR a.isProviderMail != @isProviderMail) "
+ " AND ( "
+ " (RIGHT(b.typeCode, 2) IN ('01', '02', '04') AND b.invoicerId=@invoicerId) "
+ " OR "
+ " (RIGHT(b.typeCode, 2) IN ('03', '05') AND b.brokerId=@invoicerId) "
+ " ) "
+ p_where;
p_dbps.Add("@invoicerId", NpgsqlDbType.Varchar, p_invoicerId);
p_dbps.Add("@isMailSendingX", NpgsqlDbType.Varchar, "X");
p_dbps.Add("@isInvoiceeMail", NpgsqlDbType.Varchar, "T");
p_dbps.Add("@isProviderMail", NpgsqlDbType.Varchar, "T");
_result = LSQLHelper.ExecuteText(UAppHelper.ConnectionString, _updstr, p_dbps);
}
else
{
if (LogCommands == true)
{
ELogger.SNG.WriteLog(String.Format("re-enter: invoicerId->'{0}', working-row(s)->{1}", p_invoicerId, _norec));
}
}
return(_result);
}
private int CheckReEnter(string p_invoicerId, string p_where, PgDatParameters p_dbps)
{
int _result = -1;
var _sqlstr
= "SELECT COUNT(a.isIssued) as norec "
+ " FROM TB_eTAX_INVOICE a "
+ " WHERE a.isIssued=@isIssuedX "
+ " AND ( "
+ " (RIGHT(a.typeCode, 2) IN ('01', '02', '04') AND a.invoicerId=@invoicerId) "
+ " OR "
+ " (RIGHT(a.typeCode, 2) IN ('03', '05') AND a.brokerId=@invoicerId) "
+ " ) "
+ p_where;
p_dbps.Add("@isIssuedX", NpgsqlDbType.Varchar, "X");
p_dbps.Add("@invoicerId", NpgsqlDbType.Varchar, p_invoicerId);
var _ds = LSQLHelper.SelectDataSet(UAppHelper.ConnectionString, _sqlstr, p_dbps);
int _norec = Convert.ToInt32(_ds.Tables[0].Rows[0]["norec"]);
if (_norec < 1)
{
string _updstr
= "UPDATE TB_eTAX_INVOICE "
+ " SET isIssued=@isIssuedX "
+ " FROM TB_eTAX_INVOICE a "
+ " WHERE a.isSuccess != @isSuccess "
+ " AND ( "
+ " (RIGHT(a.typeCode, 2) IN ('01', '02', '04') AND a.invoicerId=@invoicerId) "
+ " OR "
+ " (RIGHT(a.typeCode, 2) IN ('03', '05') AND a.brokerId=@invoicerId) "
+ " ) "
+ p_where;
p_dbps.Add("@isIssuedX", NpgsqlDbType.Varchar, "X");
p_dbps.Add("@isSuccess", NpgsqlDbType.Varchar, "T");
p_dbps.Add("@invoicerId", NpgsqlDbType.Varchar, p_invoicerId);
_result = LSQLHelper.ExecuteText(UAppHelper.ConnectionString, _updstr, p_dbps);
}
else
{
if (LogCommands == true)
{
ELogger.SNG.WriteLog(String.Format("re-enter: invoicerId->'{0}', working-row(s)->{1}", p_invoicerId, _norec));
}
}
return(_result);
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public int ClearXFlag()
{
IReporter.WriteDebug("*");
var _sqlstr
= "UPDATE TB_eTAX_ISSUING "
+ " SET isNTSSending=@isNTSSending, isNTSReport=@isNTSReport "
+ " WHERE isNTSSending=@isNTSSendingX";
var _dbps = new PgDatParameters();
{
_dbps.Add("@isNTSSending", NpgsqlDbType.Varchar, "F");
_dbps.Add("@isNTSReport", NpgsqlDbType.Varchar, "F");
_dbps.Add("@isNTSSendingX", NpgsqlDbType.Varchar, "X");
}
return(LSQLHelper.ExecuteText(UAppHelper.ConnectionString, _sqlstr, _dbps));
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public int ClearXFlag()
{
ISigner.WriteDebug("*");
var _sqlstr
= "UPDATE TB_eTAX_INVOICE "
+ " SET isIssued=@isIssued, isSuccess=@isSuccess "
+ " WHERE isIssued=@isIssuedX";
var _dbps = new PgDatParameters();
{
_dbps.Add("@isIssued", NpgsqlDbType.Varchar, "F");
_dbps.Add("@isSuccess", NpgsqlDbType.Varchar, "F");
_dbps.Add("@isIssuedX", NpgsqlDbType.Varchar, "X");
}
return(LSQLHelper.ExecuteText(UAppHelper.ConnectionString, _sqlstr, _dbps));
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public int DoUpdateCert()
{
ICollector.WriteDebug("");
int _result = 0;
MimeContent _certContent = RequestCert();
if (_certContent.Parts.Count < 2 || _certContent.StatusCode != 0)
{
throw new CollectException(_certContent.ErrorMessage);
}
ZipInputStream _izipStream = new ZipInputStream(_certContent.Parts[1].GetContentAsStream());
ZipEntry _izipEntry;
while ((_izipEntry = _izipStream.GetNextEntry()) != null)
{
if (_izipEntry.Name.IndexOf(".ini") >= 0)
{
continue;
}
MemoryStream _ostream = new MemoryStream();
{
int _size = 2048;
byte[] _obuffer = new byte[_size];
while (true)
{
_size = _izipStream.Read(_obuffer, 0, _obuffer.Length);
if (_size <= 0)
{
break;
}
_ostream.Write(_obuffer, 0, _size);
}
_ostream.Seek(0, SeekOrigin.Begin);
}
string _fileName = Path.GetFileNameWithoutExtension(_izipEntry.Name);
string _registerid = _fileName.Substring(0, 8);
string _newEMail = _fileName.Substring(9);
byte[] _publicBytes = _ostream.ToArray();
string _publicStr = Encryptor.SNG.PlainBytesToChiperBase64(_publicBytes);
X509Certificate2 _publicCert2 = new X509Certificate2(_publicBytes);
DateTime _expiration = Convert.ToDateTime(_publicCert2.GetExpirationDateString());
string _userName = _publicCert2.GetNameInfo(X509NameType.SimpleName, false);
var _sqlstr
= "SELECT publicKey, aspEMail "
+ " FROM TB_eTAX_PROVIDER "
+ " WHERE registerId=@registerId AND aspEMail=@aspEMail";
var _dbps = new PgDatParameters();
{
_dbps.Add("@registerId", NpgsqlDbType.Varchar, _registerid);
_dbps.Add("@aspEMail", NpgsqlDbType.Varchar, _newEMail);
}
var _ds = LSQLHelper.SelectDataSet(UAppHelper.ConnectionString, _sqlstr, _dbps);
if (LSQLHelper.IsNullOrEmpty(_ds) == true)
{
_sqlstr
= "INSERT TB_eTAX_PROVIDER "
+ "( "
+ " registerId, aspEMail, name, person, publicKey, userName, expiration, lastUpdate, providerId "
+ ") "
+ "VALUES "
+ "( "
+ " @registerId, @aspEMail, @name, @person, @publicKey, @userName, @expiration, @lastUpdate, @providerId "
+ ")";
_dbps.Add("@registerId", NpgsqlDbType.Varchar, _registerid);
_dbps.Add("@aspEMail", NpgsqlDbType.Varchar, _newEMail);
_dbps.Add("@name", NpgsqlDbType.Varchar, _userName);
_dbps.Add("@person", NpgsqlDbType.Varchar, "");
_dbps.Add("@publicKey", NpgsqlDbType.Varchar, _publicStr);
_dbps.Add("@userName", NpgsqlDbType.Varchar, _userName);
_dbps.Add("@expiration", NpgsqlDbType.TimestampTZ, _expiration);
_dbps.Add("@lastUpdate", NpgsqlDbType.TimestampTZ, DateTime.Now);
_dbps.Add("@providerId", NpgsqlDbType.Varchar, "");
if (LSQLHelper.ExecuteText(UAppHelper.ConnectionString, _sqlstr, _dbps) < 1)
{
if (LogCommands == true)
{
ELogger.SNG.WriteLog(String.Format("INSERT FAILURE: {0}, {1}, {2}, {3}", _userName, _registerid, _newEMail, _expiration));
}
}
else
{
if (LogCommands == true)
{
ELogger.SNG.WriteLog(String.Format("INSERT SUCCESS: {0}, {1}, {2}, {3}", _userName, _registerid, _newEMail, _expiration));
}
_result++;
}
}
else
{
DataRow _dr = _ds.Tables[0].Rows[0];
string _publicKey = Convert.ToString(_dr["publicKey"]);
byte[] _puboldBytes = Encryptor.SNG.ChiperBase64ToPlainBytes(_publicKey);
X509Certificate2 _puboldCert2 = new X509Certificate2(_puboldBytes);
if (_puboldCert2.Equals(_publicCert2) == false)
{
_sqlstr
= "UPDATE TB_eTAX_PROVIDER "
+ " SET publicKey=@publicKey, userName=@userName, expiration=@expiration, lastUpdate=@lastUpdate "
+ " WHERE registerId=@registerId AND aspEMail=@aspEMail";
_dbps.Add("@publicKey", NpgsqlDbType.Varchar, _publicStr);
_dbps.Add("@userName", NpgsqlDbType.Varchar, _userName);
_dbps.Add("@expiration", NpgsqlDbType.TimestampTZ, _expiration);
_dbps.Add("@lastUpdate", NpgsqlDbType.TimestampTZ, DateTime.Now);
if (LSQLHelper.ExecuteText(UAppHelper.ConnectionString, _sqlstr, _dbps) < 1)
{
if (LogCommands == true)
{
ELogger.SNG.WriteLog(String.Format("UPDATE FAILURE: {0}, {1}, {2}, {3}", _userName, _registerid, _newEMail, _expiration));
}
}
else
{
if (LogCommands == true)
{
ELogger.SNG.WriteLog(String.Format("UPDATE SUCCESS: {0}, {1}, {2}, {3}", _userName, _registerid, _newEMail, _expiration));
}
_result++;
}
}
else
{
//if (LogCommands == true)
// ELogger.SNG.WriteLog(String.Format("SAME-KEY: {0}, {1}, {2}, {3}", _userName, _registerid, _newEMail, _expiration));
}
}
_ostream.Close();
}
_izipStream.Close();
return(_result);
}
/// <summary>
/// Stores message to specified mailbox.
/// </summary>
/// <param name="mailbox">Mailbox name.</param>
/// <param name="folder">Folder where to store message. Eg. 'Inbox'.</param>
/// <param name="msgStream">Stream where message has stored.</param>
/// <param name="to">Recipient email address.</param>
/// <param name="from">Sendred email address.</param>
/// <param name="isRelay">Specifies if message must be relayed.</param>
/// <param name="date">Recieve date.</param>
/// <param name="flags">Message flags.</param>
public void StoreMessage(string p_mailfrom, string p_mailto, string p_folder, MemoryStream p_message)
{
//byte[] _topLines = null;
//_topLines = GetTopLines(p_message, 50);
try
{
var _today = DateTime.Now;
string _fileid = Guid.NewGuid().ToString();
string _mailbox = p_mailto;
{
if (p_mailto.IndexOf("@") != -1)
{
_mailbox = p_mailto.Substring(0, p_mailto.IndexOf("@"));
}
var _sqlstr
= "INSERT INTO TB_eTAX_MAILBOX "
+ "( "
+ " fileid, mailbox, folder, data, size, sender, createtime "
+ ") "
+ "VALUES "
+ "( "
+ " @fileid, @mailbox, @folder, @data, @size, @sender, @createtime "
+ ")";
var _dbps = new PgDatParameters();
{
_dbps.Add("@fileid", NpgsqlDbType.Varchar, _fileid);
_dbps.Add("@mailbox", NpgsqlDbType.Varchar, _mailbox);
_dbps.Add("@folder", NpgsqlDbType.Varchar, p_folder);
_dbps.Add("@data", NpgsqlDbType.Bytea, p_message.ToArray());
_dbps.Add("@size", NpgsqlDbType.Numeric, p_message.Length);
_dbps.Add("@sender", NpgsqlDbType.Varchar, p_mailfrom);
_dbps.Add("@createtime", NpgsqlDbType.TimestampTZ, _today);
}
if (LSQLHelper.ExecuteText(ConnectionString, _sqlstr, _dbps) < 1)
{
if (LogCommands == true)
{
ELogger.SNG.WriteLog(String.Format("mail insert failure: fileid: {0}, mailbox: {1}, folder: {2}, sender: {3}", _fileid, _mailbox, p_folder, p_mailfrom));
}
}
else
{
if (LogCommands == true && LiveServer == true)
{
ELogger.SNG.WriteLog(String.Format("mail insert success: fileid: {0}, mailbox: {1}, folder: {2}, sender: {3}", _fileid, _mailbox, p_folder, p_mailfrom));
}
}
}
if (MailSniffing == true)
{
p_message.Seek(0, SeekOrigin.Begin);
string _directory = Path.Combine(USvcHelper.eMailFolder, IProvider.Proxy.ProductId);
{
_directory = Path.Combine(_directory, _mailbox);
_directory = Path.Combine(_directory, p_folder);
_directory = Path.Combine(_directory, _today.ToString("yyyyMMdd"));
}
string _filename = _fileid + ".eml";
QFWriter.QueueWrite(_directory, _filename, p_message.ToArray());
}
if (_mailbox == AspMailAddress)
{
p_message.Seek(0, SeekOrigin.Begin);
MimeParser _parser = new MimeParser(p_message.ToArray());
if (_parser.MimeEntries.Count > 1)
{
MimeEntry _attachment = (MimeEntry)_parser.MimeEntries[1];
{
byte[] _obuffer = CmsManager.SNG.GetDecryptedContent(UCertHelper.AspKmCert.X509Cert2, _attachment.Data);
XmlDocument _taxdoc = new XmlDocument();
_taxdoc.LoadXml(Encoding.UTF8.GetString(_obuffer));
Reader _reader = new Reader(_taxdoc);
DataSet _purchaseSet = _reader.TaxPurchase();
LDltaHelper.InsertDeltaSet(ConnectionString, _purchaseSet);
}
// update [issueid] for compare with purchase-table.
string _issueid = Path.GetFileNameWithoutExtension(_attachment.FileName);
{
var _sqlstr
= "UPDATE TB_eTAX_MAILBOX "
+ " SET filler0=@issueid "
+ " WHERE fileid=@fileid";
var _dbps = new PgDatParameters();
{
_dbps.Add("@fileid", NpgsqlDbType.Varchar, _fileid);
_dbps.Add("@issueid", NpgsqlDbType.Varchar, _issueid);
}
if (LSQLHelper.ExecuteText(ConnectionString, _sqlstr, _dbps) < 1)
{
if (LogCommands == true)
{
ELogger.SNG.WriteLog(String.Format("mail update failure: fileid: {0}, issueid: {1}, sender: {2}", _fileid, _issueid, p_mailfrom));
}
}
else
{
if (LogCommands == true && LiveServer == true)
{
ELogger.SNG.WriteLog(String.Format("mail update success: fileid: {0}, issueid: {1}, sender: {2}", _fileid, _issueid, p_mailfrom));
}
}
}
}
}
}
catch (Exception ex)
{
ELogger.SNG.WriteLog(ex);
}
}