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);
}
//-------------------------------------------------------------------------------------------------------------------------
//
//-------------------------------------------------------------------------------------------------------------------------
/// <summary>
///
/// </summary>
/// <param name="p_certapp"></param>
/// <param name="p_certifier"></param>
/// <param name="p_invoicerId"></param>
/// <param name="p_fromDay"></param>
/// <param name="p_tillDay"></param>
/// <returns></returns>
public int SignatureWithDateRange(Guid p_certapp, string[] p_certifier, string p_invoicerId, DateTime p_fromDay, DateTime p_tillDay)
{
int _result = 0;
try
{
if (ISigner.CheckValidApplication(p_certapp) == true)
{
UTextHelper.SNG.GetSigningRange(ref p_fromDay, ref p_tillDay);
var _sqlstr
= "SELECT invoicerId, COUNT(invoicerId) as norec "
+ " FROM TB_eTAX_INVOICE "
+ " WHERE isSuccess != @isSuccess " // for resignning, do check 'isSuccess' here
+ " AND ( "
+ " (RIGHT(typeCode, 2) IN ('01', '02', '04') AND invoicerId=@invoicerId) "
+ " OR "
+ " (RIGHT(typeCode, 2) IN ('03', '05') AND brokerId=@invoicerId) "
+ " ) "
+ " AND issueDate>=@fromDate AND issueDate<=@tillDate "
+ " GROUP BY invoicerId";
var _dbps = new PgDatParameters();
{
_dbps.Add("@isSuccess", NpgsqlDbType.Varchar, "T");
_dbps.Add("@invoicerId", NpgsqlDbType.Varchar, p_invoicerId);
_dbps.Add("@fromDate", NpgsqlDbType.TimestampTz, p_fromDay);
_dbps.Add("@tillDate", NpgsqlDbType.TimestampTz, p_tillDay);
}
var _ds = LSQLHelper.SelectDataSet(UAppHelper.ConnectionString, _sqlstr, _dbps);
if (LSQLHelper.IsNullOrEmpty(_ds) == false)
{
X509CertMgr _invoicerCert = UCertHelper.GetCustomerCertMgr(p_invoicerId, p_certifier[0], p_certifier[1], p_certifier[2]);
var _rows = _ds.Tables[0].Rows;
for (int i = 0; i < _rows.Count; i++)
{
string _invoicerId = Convert.ToString(_rows[i]["invoicerId"]);
int _noInvoice = Convert.ToInt32(_rows[i]["norec"]);
_result += ESigner.DoSignInvoice(_invoicerCert, _invoicerId, _noInvoice, p_fromDay, p_tillDay);
}
}
}
}
catch (SignerException ex)
{
ELogger.SNG.WriteLog(ex);
}
catch (Exception ex)
{
ELogger.SNG.WriteLog(ex);
}
return(_result);
}
//-------------------------------------------------------------------------------------------------------------------------
//
//-------------------------------------------------------------------------------------------------------------------------
/// <summary>
///
/// </summary>
/// <param name="p_certapp"></param>
/// <param name="p_invoicerId"></param>
/// <param name="p_fromDay"></param>
/// <param name="p_tillDay"></param>
/// <returns></returns>
public int ReportWithDateRange(Guid p_certapp, string p_invoicerId, DateTime p_fromDay, DateTime p_tillDay)
{
int _result = 0;
try
{
if (IReporter.CheckValidApplication(p_certapp) == true)
{
var _sqlstr
= "SELECT b.invoicerId, COUNT(b.invoicerId) as norec "
+ " FROM TB_eTAX_ISSUING a INNER JOIN TB_eTAX_INVOICE b "
+ " ON a.issueId=b.issueId "
+ " WHERE a.isNTSReport != @isNTSReport "
+ " 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 b.issueDate>=@fromDay AND b.issueDate<=@tillDay "
+ " GROUP BY b.invoicerId";
var _dbps = new PgDatParameters();
{
_dbps.Add("@isNTSReport", NpgsqlDbType.Varchar, "T");
_dbps.Add("@invoicerId", NpgsqlDbType.Varchar, p_invoicerId);
_dbps.Add("@fromDay", NpgsqlDbType.TimestampTz, p_fromDay);
_dbps.Add("@tillDay", NpgsqlDbType.TimestampTz, p_tillDay);
}
var _ds = LSQLHelper.SelectDataSet(UAppHelper.ConnectionString, _sqlstr, _dbps);
if (LSQLHelper.IsNullOrEmpty(_ds) == false)
{
var _rows = _ds.Tables[0].Rows;
for (int i = 0; i < _rows.Count; i++)
{
string _invoicerId = Convert.ToString(_rows[i]["invoicerId"]);
int _noInvoice = Convert.ToInt32(_rows[i]["norec"]);
_result += EReporter.DoReportInvoicer(_invoicerId, _noInvoice, p_fromDay, p_tillDay);
}
}
}
}
catch (ReporterException ex)
{
ELogger.SNG.WriteLog(ex);
}
catch (Exception ex)
{
ELogger.SNG.WriteLog(ex);
}
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);
}
//-------------------------------------------------------------------------------------------------------------------------
//
//-------------------------------------------------------------------------------------------------------------------------
private bool CheckBroker(string p_issue_id, string p_customerId, string p_brokerId, DateTime p_signTime, DataTable p_resultTbl)
{
var _result = true;
var _sqlstr
= "SELECT * FROM TB_eTAX_BROKER "
+ " WHERE customerId=@customerId AND brokerId=@brokerId";
var _dbps = new PgDatParameters();
{
_dbps.Add("@customerId", NpgsqlDbType.Varchar, p_customerId);
_dbps.Add("@brokerId", NpgsqlDbType.Varchar, p_brokerId);
}
var _brokerSet = LSQLHelper.SelectDataSet(UAppHelper.ConnectionString, _sqlstr, _dbps);
if (LSQLHelper.IsNullOrEmpty(_brokerSet) == true)
{
const string _resultStatus = "INV900";
string _message = String.Format("broker({0}) is undefined by invoicer({1})", p_brokerId, p_customerId);
string _filterExpression = String.Format("issueId='{0}' AND resultStatus='{1}'", p_issue_id, _resultStatus);
DataRow[] _oldrow = p_resultTbl.Select(_filterExpression);
if (_oldrow.Length == 0)
{
DataRow _resultRow = p_resultTbl.NewRow();
_resultRow["issueId"] = p_issue_id;
_resultRow["resultStatus"] = _resultStatus;
_resultRow["isDone"] = "F";
_resultRow["message"] = _message;
_resultRow["created"] = p_signTime;
p_resultTbl.Rows.Add(_resultRow);
}
else
{
DataRow _resultRow = _oldrow[0];
_resultRow["isDone"] = "F";
_resultRow["message"] = _message;
_resultRow["created"] = p_signTime;
}
_result = false;
}
return(_result);
}
/// <summary>
/// 선택한 일자의 IssueId를 구한다.
/// </summary>
/// <param name="p_createDate"></param>
/// <returns></returns>
public string GetIssueId(DateTime p_createDate)
{
ICollector.WriteDebug(p_createDate.ToString());
Int32 _maxIssueId = 1;
if (m_saveIssueIds == null)
{
m_saveIssueIds = new Dictionary <string, Int32>();
}
string _issueDay = p_createDate.ToString("yyyyMMdd");
if (m_saveIssueIds.ContainsKey(_issueDay) == false)
{
string _fromId = String.Format("{0}{1}{2:D8}", _issueDay, UAppHelper.RegisterId, 0);
string _tillId = String.Format("{0}{1}{2:D8}", _issueDay, UAppHelper.RegisterId, 99999999);
var _sqlstr
= "SELECT COALESCE(MAX(TO_NUMBER(RIGHT(issueId, 8), '99999999')), 0) as maxSeqNo "
+ " FROM TB_eTAX_INVOICE "
+ " WHERE issueId>=@fromId AND issueId<=@tillId";
var _dbps = new PgDatParameters();
{
_dbps.Add("@fromId", NpgsqlDbType.Varchar, _fromId);
_dbps.Add("@tillId", NpgsqlDbType.Varchar, _tillId);
}
var _ds = LSQLHelper.SelectDataSet(UAppHelper.ConnectionString, _sqlstr, _dbps);
if (_ds.Tables[0].Rows.Count > 0)
{
_maxIssueId = Convert.ToInt32(_ds.Tables[0].Rows[0]["maxSeqNo"]) + 1;
}
m_saveIssueIds.Add(_issueDay, _maxIssueId);
}
else
{
_maxIssueId = m_saveIssueIds[_issueDay];
_maxIssueId++;
m_saveIssueIds[_issueDay] = _maxIssueId;
}
return(String.Format("{0}{1}{2:D8}", _issueDay, UAppHelper.RegisterId, _maxIssueId));
}
//-------------------------------------------------------------------------------------------------------------------------
//
//-------------------------------------------------------------------------------------------------------------------------
/// <summary>
///
/// </summary>
/// <param name="p_invoicerId"></param>
/// <returns></returns>
public DataSet GetCustomerSet(string p_invoicerId)
{
IReporter.WriteDebug(p_invoicerId);
var _sqlstr
= "SELECT reportingType, reportFromDay, reportTillDay "
+ " FROM TB_eTAX_CUSTOMER "
+ " WHERE customerId=@customerId";
var _dbps = new PgDatParameters();
{
_dbps.Add("@customerId", NpgsqlDbType.Varchar, p_invoicerId);
}
var _customer_set = LSQLHelper.SelectDataSet(UAppHelper.ConnectionString, _sqlstr, _dbps);
if (LSQLHelper.IsNullOrEmpty(_customer_set) == true)
{
throw new ReporterException(String.Format("not exist customer: invoicerId->'{0}'", p_invoicerId));
}
return(_customer_set);
}
private void DoReporting(object p_args)
{
ReportArgs _args = (ReportArgs)p_args;
_args.noIssuing = _args.noReporting;
_args.noReporting = 0;
try
{
int _toprow = 100;
int _chunkCount = _args.noIssuing / _toprow + 1;
if (_chunkCount > UAppHelper.NoThreadOfReporter)
{
_chunkCount = UAppHelper.NoThreadOfReporter;
}
string _issueid = "";
var _sqlstr
= "SELECT a.issueId, a.document, a.rvalue "
+ " 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) "
+ " ) "
+ " AND a.issueId > @issueId "
+ _args.where
+ " ORDER BY a.issueId"
+ " LIMIT " + _toprow;
{
_args.dbps.Add("@isNTSSendingX", NpgsqlDbType.Varchar, "X");
_args.dbps.Add("@invoicerId", NpgsqlDbType.Varchar, _args.invoicerId);
}
//if (LogCommands == true)
// ELogger.SNG.WriteLog(String.Format("begin: invoicerId->'{0}', noIssuing->{1}", _args.invoicerId, _args.noIssuing));
// 만약 InsertDeltaSet을 처리하는 중에 오류가 발생하면 무한 loop를 발생 하게 되므로,
// 'X'로 marking한 레코드의 총 갯수를 감소하여 '0'보다 큰 경우에만 반복한다.
while (_args.noIssuing > 0)
{
int _rowsCount = 0;
IssuingTbl.Clear();
ResponseTbl.Clear();
var _doneEvents = new ThreadPoolWait[_chunkCount];
for (int i = 0; i < _chunkCount; i++)
{
_args.dbps.Add("@issueId", NpgsqlDbType.Varchar, _issueid); // 100건 까지를 한 묶음으로 전송하기 위해 기준이 되는 승인번호
var _workingSet = LSQLHelper.SelectDataSet(UAppHelper.ConnectionString, _sqlstr, _args.dbps);
if (LSQLHelper.IsNullOrEmpty(_workingSet) == true)
{
break;
}
var _rows = _workingSet.Tables[0].Rows;
_issueid = Convert.ToString(_rows[_rows.Count - 1]["issueId"]); // 다음 100건의 기준 (>) 승인번호
_doneEvents[i] = new ThreadPoolWait();
Updater _worker = new Updater(IssuingTbl, ResponseTbl);
_doneEvents[i].QueueUserWorkItem(_worker.ReporterCallback, _rows);
if (Environment.UserInteractive == true)
{
_doneEvents[i].WaitOne();
}
_rowsCount += _rows.Count;
}
ThreadPoolWait.WaitForAll(_doneEvents);
// 처리된 레코드가 한개 이하 인 경우는 종료한다. (문제가 있는 경우로 보여 짐)
if (_rowsCount < 1)
{
break;
}
//if (LogCommands == true)
// ELogger.SNG.WriteLog(String.Format("loop: invoicerId->'{0}', noIssuing->{1}, noReporting->{2}", _args.invoicerId, _args.noIssuing, _rowsCount));
_args.noIssuing -= _rowsCount;
_args.noReporting += IssuingTbl.Rows.Count;
LDltaHelper.InsertDeltaSet(UAppHelper.ConnectionString, ResponseSet);
}
}
catch (ReporterException ex)
{
ELogger.SNG.WriteLog(ex);
}
catch (Exception ex)
{
ELogger.SNG.WriteLog(ex);
}
finally
{
if (LogCommands == true)
{
ELogger.SNG.WriteLog(String.Format("end: invoicerId->'{0}', noIssuing->{1}, noReporting->{2}", _args.invoicerId, _args.noIssuing, _args.noReporting));
}
int _noClearing = ClearXFlag(_args.invoicerId);
if (_noClearing > 0)
{
if (LogCommands == true)
{
ELogger.SNG.WriteLog(String.Format("clearX: invoicerId->'{0}', noClear->{1}", _args.invoicerId, _noClearing));
}
}
}
}
/// <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>
/// invoice 테이블의 해당 issueid 레코드를 읽어서 전자서명하여 issuing 테이블에 추가 합니다.
/// </summary>
/// <param name="p_invoiceeRow">invoicerId, issueId, invoicerEMail, invoiceeEMail, brokerEMail, providerEMail</param>
public void SignatureCallBack(Object p_invoiceeRow)
{
var _signTime = DateTime.Now;
DataRow _invoiceeRow = (DataRow)p_invoiceeRow;
DataRow _invoiceRow = null;
string _isIssued = "F";
string _isPassed = "F";
string _issueid = Convert.ToString(_invoiceeRow["issueId"]);
try
{
lock (m_invoiceTbl)
{
_invoiceRow = m_invoiceTbl.NewRow();
_invoiceRow["issueId"] = _issueid;
_invoiceRow["isIssued"] = "X"; // update deltaset에서 original과 current를 비교하는 관계로 제3의 값을 할당 하여야 함.
_invoiceRow["isSuccess"] = "X";
}
while (true)
{
string _signedXml = "";
{
var _sqlstr = "SELECT * FROM TB_eTAX_RESULT WHERE issueId=@issueId";
var _dbps = new PgDatParameters();
{
_dbps.Add("@issueId", NpgsqlDbType.Varchar, _issueid);
}
var _resultSet = LSQLHelper.SelectDataSet(UAppHelper.ConnectionString, _sqlstr, _dbps);
var _resultTbl = _resultSet.Tables[0];
foreach (DataRow _dr in _resultTbl.Rows)
{
if (Convert.ToString(_dr["isDone"]) != "T")
{
_dr["isDone"] = "T";
}
}
var _isSuccess = true;
string _typeCode = Convert.ToString(_invoiceeRow["typeCode"]).Substring(2, 2);
if (_typeCode == "03" || _typeCode == "05")
{
string _invoicerId = Convert.ToString(_invoiceeRow["invoicerId"]);
string _brokerId = Convert.ToString(_invoiceeRow["brokerId"]);
if (CheckBroker(_issueid, _invoicerId, _brokerId, _signTime, _resultTbl) == false)
{
ELogger.SNG.WriteLog(String.Format("while checking issuing({0}), broker({1}) is undefined by invoicer({2}).", _issueid, _brokerId, _invoicerId));
_isSuccess = false;
}
}
lock (SyncEngine)
_signedXml = GetSignedXml(_issueid, _signTime, _resultTbl);
if (String.IsNullOrEmpty(_signedXml) == true)
{
ELogger.SNG.WriteLog(String.Format("because signed-xml is empty, could not update issuing table -> '{0}'.", _issueid));
_isSuccess = false;
}
LDltaHelper.InsertDeltaSet(UAppHelper.ConnectionString, _resultSet);
if (_isSuccess == false)
{
break;
}
}
lock (m_issuingTbl)
{
var _issuingRow = m_issuingTbl.NewRow();
_issuingRow["issueId"] = _issueid;
//_issuingRow["typeCode"] = _invoiceeRow["typeCode"];
//_issuingRow["invoicerId"] = _invoiceeRow["invoicerId"];
//_issuingRow["invoiceeId"] = _invoiceeRow["invoiceeId"];
//_issuingRow["brokerId"] = _invoiceeRow["brokerId"];
_issuingRow["providerId"] = _invoiceeRow["providerId"];
_issuingRow["document"] = _signedXml;
_issuingRow["rvalue"] = Convert.ToBase64String(m_invoicerCert.RandomNumber);
_issuingRow["signingDate"] = _signTime;
//_issuingRow["invoicerEMail"] = _invoiceeRow["invoicerEMail"];
//_issuingRow["invoiceeEMail"] = _invoiceeRow["invoiceeEMail"];
//_issuingRow["brokerEMail"] = _invoiceeRow["brokerEMail"];
_issuingRow["providerEMail"] = _invoiceeRow["providerEMail"];
foreach (DataColumn _dc in m_issuingTbl.Columns)
{
if (_dc.AllowDBNull == false && _issuingRow[_dc] == DBNull.Value)
{
if (_dc.DataType == typeof(String))
{
if (_dc.MaxLength == 1)
{
_issuingRow[_dc] = "F";
}
}
else if (_dc.DataType == typeof(Decimal))
{
_issuingRow[_dc] = 0;
}
}
}
m_issuingTbl.Rows.Add(_issuingRow);
}
_isPassed = "T";
break;
}
_isIssued = "T";
}
catch (Exception ex)
{
ELogger.SNG.WriteLog(ex);
}
finally
{
lock (m_invoiceTbl)
{
m_invoiceTbl.Rows.Add(_invoiceRow);
_invoiceRow.AcceptChanges();
_invoiceRow["isIssued"] = _isIssued;
_invoiceRow["isSuccess"] = _isPassed;
}
}
}
/// <summary>
/// issueid에 해당하는 invoice테이블의 레코드를 읽어서 전자서명한 xml String을 리턴한다.
/// 전자서명 도중 오류가 발생한 내역은 result 테이블에 기록 한다.
/// </summary>
/// <param name="p_issue_id">전자서명 할 invoice테이블의 issueId</param>
/// <returns></returns>
private string GetSignedXml(string p_issue_id, DateTime p_signTime, DataTable p_resultTbl)
{
var _result = "";
var _sqlstr = "SELECT * FROM TB_eTAX_INVOICE WHERE issueId=@issueId";
var _dbps = new PgDatParameters();
{
_dbps.Add("@issueId", NpgsqlDbType.Varchar, p_issue_id);
}
var _invoiceSet = LSQLHelper.SelectDataSet(UAppHelper.ConnectionString, _sqlstr, _dbps);
if (LSQLHelper.IsNullOrEmpty(_invoiceSet) == false)
{
_sqlstr = "SELECT * FROM TB_eTAX_LINEITEM WHERE issueId=@issueId";
var _lineitemSet = LSQLHelper.SelectDataSet(UAppHelper.ConnectionString, _sqlstr, _dbps);
_invoiceSet.Merge(_lineitemSet);
if (Validator.SNG.CheckInvoiceDataTable(_invoiceSet) <= 0)
{
//-------------------------------------------------------------------------------------------------------------------//
// 세금계산서 작성
//-------------------------------------------------------------------------------------------------------------------//
Writer _etaxbill = new Writer(_invoiceSet);
//-------------------------------------------------------------------------------------------------------------------//
// 전자서명
//-------------------------------------------------------------------------------------------------------------------//
MemoryStream _signed = XSignature.SNG.GetSignedXmlStream(_etaxbill.TaxStream, m_invoicerCert.X509Cert2);
string _signedXml = (new StreamReader(_signed)).ReadToEnd();
string _validator = Validator.SNG.DoValidation(_signedXml);
if (String.IsNullOrEmpty(_validator) == false)
{
const string _resultStatus = "INV800";
string _filterExpression = String.Format("issueId='{0}' AND resultStatus='{1}'", p_issue_id, _resultStatus);
DataRow[] _oldrow = p_resultTbl.Select(_filterExpression);
if (_oldrow.Length == 0)
{
DataRow _resultRow = p_resultTbl.NewRow();
_resultRow["issueId"] = p_issue_id;
_resultRow["resultStatus"] = _resultStatus;
_resultRow["isDone"] = "F";
_resultRow["message"] = _validator;
_resultRow["created"] = p_signTime;
p_resultTbl.Rows.Add(_resultRow);
}
else
{
DataRow _resultRow = _oldrow[0];
_resultRow["isDone"] = "F";
_resultRow["message"] = _validator;
_resultRow["created"] = p_signTime;
}
}
else
{
// SUCCESS
_result = _signedXml;
}
}
else
{
DataTable _invoiceTbl = _invoiceSet.Tables["TB_eTAX_INVOICE"];
foreach (DataRow _dr in _invoiceTbl.Rows)
{
NameValueCollection _nvCols = Validator.SNG.GetCollectionFromResult(_dr.RowError);
foreach (string _resultStatus in _nvCols.AllKeys)
{
string _filterExpression = String.Format("issueId='{0}' AND resultStatus='{1}'", p_issue_id, _resultStatus);
DataRow[] _oldrow = p_resultTbl.Select(_filterExpression);
if (_oldrow.Length == 0)
{
DataRow _resultRow = p_resultTbl.NewRow();
_resultRow["issueId"] = p_issue_id;
_resultRow["resultStatus"] = _resultStatus;
_resultRow["isDone"] = "F";
_resultRow["message"] = _nvCols[_resultStatus];
_resultRow["created"] = p_signTime;
p_resultTbl.Rows.Add(_resultRow);
}
else
{
DataRow _resultRow = _oldrow[0];
_resultRow["isDone"] = "F";
_resultRow["message"] = _nvCols[_resultStatus];
_resultRow["created"] = p_signTime;
}
}
}
}
}
return(_result);
}
private void DoSignature(object p_args)
{
var _args = (SignatureArgs)p_args;
_args.noInvoicee = _args.noIssuing;
_args.noIssuing = 0;
try
{
int _toprow = 800;
var _sqlstr
= "SELECT a.issueId, a.typeCode, a.invoicerId, a.invoicerEMail, a.invoiceeId, a.invoiceeEMail1 as invoiceeEMail, "
+ " a.brokerId, a.brokerEMail, b.providerId, c.aspEMail as providerEMail "
+ " FROM TB_eTAX_INVOICE a "
+ " LEFT JOIN TB_eTAX_CUSTOMER b ON a.invoiceeId=b.customerId "
+ " LEFT JOIN (SELECT * FROM TB_eTAX_PROVIDER WHERE NULLIF(providerId, '') IS NOT NULL) c ON b.providerId=c.providerId "
+ " WHERE a.isIssued=@isIssuedX " // to avoid infinite loop, do check isIssued here.
+ " 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) "
+ " ) "
+ _args.where
+ " ORDER BY a.issueId"
+ " LIMIT " + _toprow;
{
_args.dbps.Add("@isIssuedX", NpgsqlDbType.Varchar, "X");
_args.dbps.Add("@invoicerId", NpgsqlDbType.Varchar, _args.invoicerId);
}
//if (LogCommands == true)
// ELogger.SNG.WriteLog(String.Format("begin: invoicerId->'{0}', noInvoicee->{1}", _args.invoicerId, _args.noInvoicee));
// 만약 InsertDeltaSet을 처리하는 중에 오류가 발생하면 무한 loop를 발생 하게 되므로,
// 'X'로 marking한 레코드의 총 갯수를 감소하여 '0'보다 큰 경우에만 반복한다.
while (_args.noInvoicee > 0)
{
InvoiceTbl.Clear();
IssuingTbl.Clear();
var _workingSet = LSQLHelper.SelectDataSet(UAppHelper.ConnectionString, _sqlstr, _args.dbps);
if (LSQLHelper.IsNullOrEmpty(_workingSet) == true)
{
break;
}
var _rows = _workingSet.Tables[0].Rows;
var _doneEvents = new ThreadPoolWait[_rows.Count];
for (int i = 0; i < _rows.Count; i++)
{
_doneEvents[i] = new ThreadPoolWait();
Updater _worker = new Updater(_args.invoicerCert, IssuingTbl, InvoiceTbl);
_doneEvents[i].QueueUserWorkItem(_worker.SignatureCallBack, _rows[i]);
if (Environment.UserInteractive == true)
{
_doneEvents[i].WaitOne();
}
}
ThreadPoolWait.WaitForAll(_doneEvents);
// 처리된 레코드가 한개 이하 인 경우는 종료한다. (문제가 있는 경우로 보여 짐)
if (_rows.Count < 1)
{
break;
}
//if (LogCommands == true)
// ELogger.SNG.WriteLog(String.Format("loop: invoicerId->'{0}', noInvoicee->{1}, noIssuing->{2}", _args.invoicerId, _args.noInvoicee, _rows.Count));
_args.noInvoicee -= _rows.Count;
_args.noIssuing += IssuingTbl.Rows.Count;
LDltaHelper.InsertDeltaSet(UAppHelper.ConnectionString, IssuingSet);
}
}
catch (SignerException ex)
{
ELogger.SNG.WriteLog(ex);
}
catch (Exception ex)
{
ELogger.SNG.WriteLog(ex);
}
finally
{
if (LogCommands == true)
{
ELogger.SNG.WriteLog(String.Format("end: invoicerId->'{0}', noInvoicee->{1}, noIssuing->{2}", _args.invoicerId, _args.noInvoicee, _args.noIssuing));
}
int _noClearing = ClearXFlag(_args.invoicerId);
if (_noClearing > 0)
{
if (LogCommands == true)
{
ELogger.SNG.WriteLog(String.Format("clearX: invoicerId->'{0}', noClear->{1}", _args.invoicerId, _noClearing));
}
}
}
}
private void ReporterWakeup(object stateInfo)
{
var _autoEvent = (AutoResetEvent)stateInfo;
try
{
IReporter.WriteDebug("wakeup...");
var _nday = DateTime.Now;
DateTime _pday = _nday.AddDays(-1);
DateTime _fromDay = new DateTime(_pday.Year, _pday.Month, _pday.Day);
DateTime _tillDay = new DateTime(_nday.Year, _nday.Month, _nday.Day);
// check table for auto-reporting
var _sqlstr
= "SELECT b.invoicerId, COUNT(b.invoicerId) as norec, @fromDay as fromDay, @tillDay as tillDay "
+ " FROM TB_eTAX_ISSUING a INNER JOIN TB_eTAX_INVOICE b "
+ " ON a.issueId=b.issueId "
+ " WHERE a.isNTSReport != @isNTSReport "
+ " AND b.issueDate>=@fromDay AND b.issueDate<@tillDay "
+ " GROUP BY b.invoicerId";
var _dbps = new PgDatParameters();
{
_dbps.Add("@isNTSReport", NpgsqlDbType.Varchar, "T");
_dbps.Add("@fromDay", NpgsqlDbType.TimestampTZ, _fromDay);
_dbps.Add("@tillDay", NpgsqlDbType.TimestampTZ, _tillDay);
}
var _ds = LSQLHelper.SelectDataSet(UAppHelper.ConnectionString, _sqlstr, _dbps);
if (LSQLHelper.IsNullOrEmpty(_ds) == false)
{
var _rows = _ds.Tables[0].Rows;
ELogger.SNG.WriteLog(String.Format("selected invoicer(s): {0} ", _rows.Count));
var _doneEvents = new ThreadPoolWait[_rows.Count];
for (int i = 0; i < _rows.Count; i++)
{
_doneEvents[i] = new ThreadPoolWait();
_doneEvents[i].QueueUserWorkItem(ReporterBackWork, _rows[i]);
if (Environment.UserInteractive == true)
{
_doneEvents[i].WaitOne();
}
}
ThreadPoolWait.WaitForAll(_doneEvents);
}
}
catch (ReporterException ex)
{
ELogger.SNG.WriteLog(ex);
}
catch (Exception ex)
{
ELogger.SNG.WriteLog(ex);
}
finally
{
IReporter.WriteDebug("sleep...");
ReportingTimer.Change(UAppHelper.ReporterDueTime, Timeout.Infinite);
_autoEvent.Set();
}
}
private void DoMailing(object p_args)
{
MailingArgs _args = (MailingArgs)p_args;
_args.noInvoicee = _args.noSending;
_args.noSending = 0;
try
{
int _toprow = 800;
var _sqlstr
= "SELECT a.issueId, a.document, a.securityId, b.issueDate, b.typeCode, b.invoiceeKind, "
+ " b.chargeTotal, b.taxTotal, b.grandTotal, b.description, a.isMailSending, "
+ " b.invoicerId, b.invoicerEMail, b.invoicerName, b.invoicerPerson, b.invoicerPhone, "
+ " a.isInvoiceeMail, b.invoiceeId, b.invoiceeEMail1 as invoiceeEMail, b.invoiceeName, "
+ " b.invoiceePerson, b.invoiceePhone1 as invoiceePhone, a.isProviderMail, a.providerId, "
+ " a.providerEMail, a.sendMailCount, a.mailSendingDate "
+ " FROM TB_eTAX_ISSUING a INNER JOIN TB_eTAX_INVOICE b "
+ " ON a.issueId=b.issueId "
+ " WHERE a.isMailSending=@isMailSendingX " // to avoid infinite loop, do check isMailSending here.
+ " 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) "
+ " ) "
+ _args.where
+ " ORDER BY a.providerEMail"
+ " LIMIT " + _toprow;
{
_args.dbps.Add("@isMailSendingX", NpgsqlDbType.Varchar, "X");
_args.dbps.Add("@invoicerId", NpgsqlDbType.Varchar, _args.invoicerId);
}
//if (LogCommands == true)
// ELogger.SNG.WriteLog(String.Format("begin: invoicerId->'{0}', noInvoicee->{1}", _args.invoicerId, _args.noInvoicee));
var _random = new Random();
// 만약 InsertDeltaSet을 처리하는 중에 오류가 발생하면 무한 loop를 발생 하게 되므로,
// 'X'로 marking한 레코드의 총 갯수를 감소하여 '0'보다 큰 경우에만 반복한다.
while (_args.noInvoicee > 0)
{
IssuingTbl.Clear();
ResultTbl.Clear();
var _workingSet = LSQLHelper.SelectDataSet(UAppHelper.ConnectionString, _sqlstr, _args.dbps);
if (LSQLHelper.IsNullOrEmpty(_workingSet) == true)
{
break;
}
var _rows = _workingSet.Tables[0].Rows;
var _doneEvents = new ThreadPoolWait[_rows.Count];
for (int i = 0; i < _rows.Count; i++)
{
if (String.IsNullOrEmpty(Convert.ToString(_rows[i]["securityId"])) == true)
{
_rows[i]["securityId"] = Convert.ToString(_random.Next(100000, 999999));
}
if (_args.reSending == true)
{
_rows[i]["invoiceeEMail"] = _args.invoiceeEMail;
}
_doneEvents[i] = new ThreadPoolWait();
AsyncWorker _worker = new AsyncWorker(IssuingTbl, ResultTbl);
_doneEvents[i].QueueUserWorkItem(_worker.MailerCallback, _rows[i]);
if (Environment.UserInteractive == true)
{
_doneEvents[i].WaitOne();
}
}
ThreadPoolWait.WaitForAll(_doneEvents);
// 처리된 레코드가 한개 이하 인 경우는 종료한다. (문제가 있는 경우로 보여 짐)
if (_rows.Count < 1)
{
break;
}
//if (LogCommands == true)
// ELogger.SNG.WriteLog(String.Format("loop: invoicerId->'{0}', noInvoicee->{1}, noSending->{2}", _args.invoicerId, _args.noInvoicee, _rows.Count));
_args.noInvoicee -= _rows.Count;
_args.noSending += IssuingTbl.Rows.Count;
LDltaHelper.InsertDeltaSet(UAppHelper.ConnectionString, IssuingSet);
}
}
catch (MailerException ex)
{
ELogger.SNG.WriteLog(ex);
}
catch (Exception ex)
{
ELogger.SNG.WriteLog(ex);
}
finally
{
if (LogCommands == true)
{
ELogger.SNG.WriteLog(String.Format("end: invoicerId->'{0}', noInvoicee->{1}, noSending->{2}", _args.invoicerId, _args.noInvoicee, _args.noSending));
}
int _noClearing = ClearXFlag(_args.invoicerId);
if (_noClearing > 0)
{
if (LogCommands == true)
{
ELogger.SNG.WriteLog(String.Format("clearX: invoicerId->'{0}', noClear->{1}", _args.invoicerId, _noClearing));
}
}
}
}
private void SignerWakeup(object stateInfo)
{
var _autoEvent = (AutoResetEvent)stateInfo;
try
{
ISigner.WriteDebug("wakeup...");
DateTime _fromDay = UTextHelper.SNG.GetFirstDayOfLastMonth();
DateTime _tillDay = UTextHelper.SNG.GetLastDayOfThisMonth();
UTextHelper.SNG.GetSigningRange(ref _fromDay, ref _tillDay);
// check table for auto-signing
var _sqlstr
= "SELECT invoicerId, COUNT(invoicerId) as norec, @fromDay as fromDay, @tillDay as tillDay "
+ " FROM TB_eTAX_INVOICE "
+ " WHERE isSuccess != @isSuccess " // for resignning, do check 'isSuccess' here
+ " AND issueDate>=@fromDay AND issueDate<=@tillDay "
+ " GROUP BY invoicerId";
var _dbps = new PgDatParameters();
{
_dbps.Add("@isSuccess", NpgsqlDbType.Varchar, "T");
_dbps.Add("@fromDay", NpgsqlDbType.TimestampTz, _fromDay);
_dbps.Add("@tillDay", NpgsqlDbType.TimestampTz, _tillDay);
}
var _ds = LSQLHelper.SelectDataSet(UAppHelper.ConnectionString, _sqlstr, _dbps);
if (LSQLHelper.IsNullOrEmpty(_ds) == false)
{
var _rows = _ds.Tables[0].Rows;
ELogger.SNG.WriteLog(String.Format("selected invoicer(s): {0} ", _rows.Count));
var _doneEvents = new ThreadPoolWait[_rows.Count];
for (int i = 0; i < _rows.Count; i++)
{
_doneEvents[i] = new ThreadPoolWait();
_doneEvents[i].QueueUserWorkItem(SignerCallback, _rows[i]);
if (Environment.UserInteractive == true)
{
_doneEvents[i].WaitOne();
}
}
ThreadPoolWait.WaitForAll(_doneEvents);
}
}
catch (SignerException ex)
{
ELogger.SNG.WriteLog(ex);
}
catch (Exception ex)
{
ELogger.SNG.WriteLog(ex);
}
finally
{
ISigner.WriteDebug("sleep...");
SignatureTimer.Change(UAppHelper.SignerDueTime, Timeout.Infinite);
_autoEvent.Set();
}
}