//-------------------------------------------------------------------------------------------------------------------------
//
//-------------------------------------------------------------------------------------------------------------------------
/// <summary>
/// 국세청으로 부터 전달 된 메시지를 기반으로 ISSUING, RESPONSE, RESULT 테이블을 Update 합니다.
/// </summary>
/// <param name="p_xmldoc"></param>
/// <param name="p_request_date"></param>
/// <param name="o_error"></param>
/// <returns></returns>
public bool DoSaveRequestAck(XmlDocument p_xmldoc, DateTime p_request_date, out string o_error)
{
var _result = false;
o_error = "";
try
{
IssuingTbl.Clear();
ResponseTbl.Clear();
ResultTbl.Clear();
XmlNamespaceManager _nsmgr = new XmlNamespaceManager(p_xmldoc.NameTable);
_nsmgr.AddNamespace("etax", XSignature.SNG.SignNameCollections[""]);
XPathExpression _xexpr = XPathExpression.Compile("//etax:TaxInvoiceResponse/etax:ResultDocument");
_xexpr.SetContext(_nsmgr);
DataRow _responseRow = ResponseTbl.NewRow();
{
_responseRow["totalCount"] = 0;
_responseRow["successCount"] = 0;
_responseRow["failCount"] = 0;
}
XPathNavigator _nav = p_xmldoc.CreateNavigator().SelectSingleNode(_xexpr);
if (_nav.MoveToChild(XPathNodeType.Element) == true)
{
do
{
if (_nav.Name == "ValidationDocument")
{
DataRow _resultRow = ResultTbl.NewRow();
{
XPathNodeIterator _iter = _nav.SelectChildren(XPathNodeType.Element);
while (_iter.MoveNext() == true)
{
string _name = _iter.Current.Name;
{
if (_name == "IssueID")
{
_name = "issueId";
}
else if (_name == "ResultStatusCode")
{
_name = "resultStatus";
}
}
if (_resultRow.Table.Columns.IndexOf(_name) >= 0)
{
string _value = _iter.Current.Value;
if (_resultRow.Table.Columns[_name].DataType == typeof(DateTime))
{
_resultRow[_name] = DateTime.ParseExact(_value, "yyyyMMddHHmmss", System.Globalization.CultureInfo.CurrentCulture);
}
else
{
_resultRow[_name] = _value;
}
}
}
_resultRow["isDone"] = "F";
_resultRow["created"] = p_request_date;
ResultTbl.Rows.Add(_resultRow);
}
var _issuingRow = IssuingTbl.NewRow();
{
// row.RowState Add상태를 제거하기 위해서 Temp자료를 넣는다.
_issuingRow["issueId"] = _resultRow["issueId"];
_issuingRow["isNTSConfirm"] = "X";
_issuingRow["isNTSSuccess"] = "X";
_issuingRow["ntsConfirmDate"] = DateTime.MinValue;
IssuingTbl.Rows.Add(_issuingRow);
_issuingRow.AcceptChanges();
_issuingRow["isNTSConfirm"] = "T";
_issuingRow["isNTSSuccess"] = "F";
string _status = Convert.ToString(_resultRow["resultStatus"]);
if (_status == "SUC001" || _status == "SYN003")
{
_issuingRow["isNTSSuccess"] = "T";
_resultRow["isDone"] = "T";
}
_issuingRow["ntsConfirmDate"] = p_request_date;
}
}
else
{
string _name = _nav.Name;
{
if (_name == "RefSubmitID")
{
_name = "submitId";
}
else if (_name == "ReceiptID")
{
_name = "receiptId";
}
else if (_name == "TypeCode")
{
_name = "typeCode";
}
else if (_name == "ResponseDateTime")
{
_name = "responseTime";
}
else if (_name == "ProcessStatusCode")
{
_name = "processStatus";
}
else if (_name == "FailReasonStatusCode")
{
_name = "failReason";
}
else if (_name == "TotalCountQuantity")
{
_name = "totalCount";
}
else if (_name == "SuccessCountQuantity")
{
_name = "successCount";
}
else if (_name == "FailCountQuantity")
{
_name = "failCount";
}
}
if (_responseRow.Table.Columns.IndexOf(_name) >= 0)
{
string _value = _nav.Value;
if (_responseRow.Table.Columns[_name].DataType == typeof(DateTime))
{
_responseRow[_name] = DateTime.ParseExact(_value, "yyyyMMddHHmmss", System.Globalization.CultureInfo.CurrentCulture);
}
else
{
_responseRow[_name] = _value;
}
}
}
}while (_nav.MoveToNext(XPathNodeType.Element));
ResponseTbl.Rows.Add(_responseRow);
LDltaHelper.InsertDeltaSet(UAppHelper.ConnectionString, ResponseSet);
}
o_error = String.Format("Update result deltaSet: {0}, {1} record(s)", ResponseTbl.Rows[0]["submitId"], ResultTbl.Rows.Count);
_result = true;
}
catch (Exception ex)
{
if (ex.InnerException != null)
{
o_error = ex.InnerException.Message;
}
else
{
o_error = ex.Message;
}
}
return(_result);
}
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));
}
}
}
}
//-------------------------------------------------------------------------------------------------------------------------
// Public functions - 파일을 저장한다. 1000개까지 처리한다.
//-------------------------------------------------------------------------------------------------------------------------
/// <summary>
/// 엑셀에서 추출한 테이블을 저장한다. 1,000개까지 처리한다.
/// </summary>
/// <param name="p_uploadTable">Upload Excel Table</param>
/// <param name="p_createdBy">created id</param>
/// <returns></returns>
public bool DoExcelUpload(DataTable p_uploadTable, string p_createdBy)
{
ICollector.WriteDebug(p_createdBy);
var _result = false;
try
{
if (p_uploadTable.Rows.Count > 1000)
{
throw new ProxyException(String.Format("Number of records can not exceed 1,000: '{0}'", p_uploadTable.Rows.Count));
}
// 데이터 Clear..
g_InvoinceSet.Clear();
g_invoiceTbl.Clear();
g_lineitemTbl.Clear();
g_customerTbl.Clear();
g_partnerTbl.Clear();
foreach (DataRow _row in p_uploadTable.Rows)
{
string _IssueId = GetIssueId(Convert.ToDateTime(_row[1]));
// TB_eTAX_INVOICE 테이블에 입력
DataRow _invoiceRow = g_invoiceTbl.NewRow();
DataRow _customerRow = g_customerTbl.NewRow();
DataRow _partnerRow = g_partnerTbl.NewRow();
_invoiceRow["exchangeId"] = _IssueId;
_invoiceRow["exchangeDate"] = Convert.ToDateTime(_row[1]);
_invoiceRow["isIssued"] = "F";
_invoiceRow["isSuccess"] = "F";
_invoiceRow["refIssueId"] = "";
_invoiceRow["creator"] = p_createdBy;
_invoiceRow["issueId"] = _IssueId;
_invoiceRow["issueDate"] = Convert.ToDateTime(_row[1]);
_invoiceRow["typeCode"] = "01" + Convert.ToString(_row[0]);
_invoiceRow["purposeCode"] = Convert.ToString(_row[58]);
_invoiceRow["amendmentCode"] = "";
_invoiceRow["description"] = Convert.ToString(_row[21]);
_invoiceRow["importId"] = "";
_invoiceRow["importQuantity"] = Convert.ToDecimal("0");
_invoiceRow["invoicerId"] = Convert.ToString(_row[2]);
_invoiceRow["invoicerOrgId"] = Convert.ToString(_row[3]);
_invoiceRow["invoicerName"] = Convert.ToString(_row[4]);
_invoiceRow["invoicerPerson"] = Convert.ToString(_row[5]);
_invoiceRow["invoicerAddress"] = Convert.ToString(_row[6]);
_invoiceRow["invoicerType"] = Convert.ToString(_row[7]);
_invoiceRow["invoicerClass"] = Convert.ToString(_row[8]);
_invoiceRow["invoicerDepartment"] = "";
_invoiceRow["invoicerContactor"] = "";
_invoiceRow["invoicerPhone"] = "";
_invoiceRow["invoicerEMail"] = Convert.ToString(_row[9]);
_invoiceRow["invoiceeId"] = Convert.ToString(_row[10]);
if (_row[10].ToString() == "9999999999999")
{
_invoiceRow["invoiceeKind"] = "03";
}
else if (_row[10].ToString().Length == 10)
{
_invoiceRow["invoiceeKind"] = "01";
}
else
{
_invoiceRow["invoiceeKind"] = "02";
}
_invoiceRow["invocieeOrgId"] = Convert.ToString(_row[11]);
_invoiceRow["invoiceeName"] = Convert.ToString(_row[12]);
_invoiceRow["invoiceePerson"] = Convert.ToString(_row[13]);
_invoiceRow["invoiceeAddress"] = Convert.ToString(_row[14]);
_invoiceRow["invoiceeType"] = Convert.ToString(_row[15]);
_invoiceRow["invoiceeClass"] = Convert.ToString(_row[16]);
_invoiceRow["invoiceeDepartment1"] = "";
_invoiceRow["invoiceeContactor1"] = "";
_invoiceRow["invoiceePhone1"] = "";
_invoiceRow["invoiceeEMail1"] = Convert.ToString(_row[17]);
_invoiceRow["invoiceeDepartment2"] = "";
_invoiceRow["invoiceeContactor2"] = "";
_invoiceRow["invoiceePhone2"] = "";
_invoiceRow["invoiceeEMail2"] = Convert.ToString(_row[18]);
_invoiceRow["brokerId"] = "";
_invoiceRow["brokerOrgId"] = "";
_invoiceRow["brokerName"] = "";
_invoiceRow["brokerPerson"] = "";
_invoiceRow["brokerAddress"] = "";
_invoiceRow["brokerType"] = "";
_invoiceRow["brokerClass"] = "";
_invoiceRow["brokerDepartment"] = "";
_invoiceRow["brokerContactor"] = "";
_invoiceRow["brokerPhone"] = "";
_invoiceRow["brokerEMail"] = "";
_invoiceRow["isUploaded"] = "T";
_invoiceRow["paidCash"] = Convert.ToDecimal(_row[54]);
_invoiceRow["paidCheck"] = Convert.ToDecimal(_row[55]);
_invoiceRow["paidNote"] = Convert.ToDecimal(_row[56]);
_invoiceRow["paidCredit"] = Convert.ToDecimal(_row[57]);
_invoiceRow["chargeTotal"] = Convert.ToDecimal(_row[19]);
_invoiceRow["taxTotal"] = Convert.ToDecimal(_row[20]);
_invoiceRow["grandTotal"] = Convert.ToDecimal(_row[19]) + Convert.ToDecimal(_row[20]);
// TB_eTAX_CUSTOMER 정보를 넣는다.
string _filterCustomer = String.Format("customerId='{0}'", Convert.ToString(_row[10]));
DataRow[] _oldcustomerow = g_customerTbl.Select(_filterCustomer);
if (_oldcustomerow.Length == 0)
{
_customerRow["customerId"] = Convert.ToString(_row[10]);
if (_row[10].ToString() == "9999999999999")
{
_customerRow["kind"] = "03";
}
else if (_row[10].ToString().Length == 10)
{
_customerRow["kind"] = "01";
}
else
{
_customerRow["kind"] = "02";
}
_customerRow["name"] = Convert.ToString(_row[12]);
_customerRow["person"] = Convert.ToString(_row[13]);
_customerRow["address"] = Convert.ToString(_row[14]);
_customerRow["type"] = Convert.ToString(_row[15]);
_customerRow["class"] = Convert.ToString(_row[16]);
_customerRow["department1"] = "";
_customerRow["contactor1"] = "";
_customerRow["phone1"] = "";
_customerRow["eMail1"] = Convert.ToString(_row[17]);
_customerRow["department2"] = "";
_customerRow["contactor2"] = "";
_customerRow["phone2"] = "";
_customerRow["eMail2"] = Convert.ToString(_row[18]);
_customerRow["bizregAttach"] = "F";
_customerRow["bankbookAttach"] = "F";
_customerRow["providerId"] = "";
_customerRow["headOffice"] = "";
_customerRow["taxRegId"] = "";
_customerRow["closingDay"] = 1;
_customerRow["signingType"] = "02";
_customerRow["signFromDay"] = 1;
_customerRow["signTillDay"] = 31;
_customerRow["sendingType"] = "02";
_customerRow["sendFromDay"] = 1;
_customerRow["sendTillDay"] = UTextHelper.SNG.SigningDay;
_customerRow["reportingType"] = "02";
_customerRow["reportFromDay"] = 1;
_customerRow["reportTillDay"] = UTextHelper.SNG.ReportingDay;
_customerRow["reportCondition"] = "01";
g_customerTbl.Rows.Add(_customerRow);
}
// TB_eTAX_PARTNER 정보를 넣는다.
string _filterPartner = String.Format("userId='{0}' AND customerId='{1}'", p_createdBy, Convert.ToString(_row[10]));
DataRow[] _oldpartnerRow = g_partnerTbl.Select(_filterPartner);
if (_oldpartnerRow.Length == 0)
{
_partnerRow["userId"] = p_createdBy;
_partnerRow["customerId"] = Convert.ToString(_row[10]);
_partnerRow["name"] = Convert.ToString(_row[12]);
_partnerRow["person"] = Convert.ToString(_row[13]);
_partnerRow["address"] = Convert.ToString(_row[14]);
_partnerRow["type"] = Convert.ToString(_row[15]);
_partnerRow["class"] = Convert.ToString(_row[16]);
_partnerRow["department1"] = "";
_partnerRow["contactor1"] = "";
_partnerRow["phone1"] = "";
_partnerRow["eMail1"] = Convert.ToString(_row[17]);
_partnerRow["department2"] = "";
_partnerRow["contactor2"] = "";
_partnerRow["phone2"] = "";
_partnerRow["eMail2"] = Convert.ToString(_row[18]);
g_partnerTbl.Rows.Add(_partnerRow);
}
g_invoiceTbl.Rows.Add(_invoiceRow);
// TB_eTAX_LINEITEM 1Row 입력
if (String.IsNullOrEmpty(_row[27].ToString()) == false && Convert.ToDecimal(_row[27].ToString()) > 0)
{
DataRow _lineitemRow = g_lineitemTbl.NewRow();
_lineitemRow["issueId"] = _IssueId;
_lineitemRow["seqNo"] = 1;
_lineitemRow["purchaseDate"] = Convert.ToDateTime(Convert.ToDateTime(_row[1]).ToString("yyyy-MM-") + Convert.ToInt32(_row[22]).ToString("00"));
_lineitemRow["itemName"] = Convert.ToString(_row[23]);
_lineitemRow["information"] = Convert.ToString(_row[24]);
_lineitemRow["quantity"] = Convert.ToDecimal(_row[25]);
_lineitemRow["unitPrice"] = Convert.ToDecimal(_row[26]);
_lineitemRow["invoiceAmount"] = Convert.ToDecimal(_row[27]);
_lineitemRow["taxAmount"] = Convert.ToDecimal(_row[28]);
_lineitemRow["description"] = Convert.ToString(_row[29]);
g_lineitemTbl.Rows.Add(_lineitemRow);
}
// TB_eTAX_LINEITEM 2Row 입력
if (String.IsNullOrEmpty(_row[35].ToString()) == false && Convert.ToDecimal(_row[35].ToString()) > 0)
{
DataRow _lineitemRow = g_lineitemTbl.NewRow();
_lineitemRow["issueId"] = _IssueId;
_lineitemRow["seqNo"] = 2;
_lineitemRow["purchaseDate"] = Convert.ToDateTime(Convert.ToDateTime(_row[1]).ToString("yyyy-MM-") + Convert.ToInt32(_row[30]).ToString("00"));
_lineitemRow["itemName"] = Convert.ToString(_row[31]);
_lineitemRow["information"] = Convert.ToString(_row[32]);
_lineitemRow["quantity"] = Convert.ToDecimal(_row[33]);
_lineitemRow["unitPrice"] = Convert.ToDecimal(_row[34]);
_lineitemRow["invoiceAmount"] = Convert.ToDecimal(_row[35]);
_lineitemRow["taxAmount"] = Convert.ToDecimal(_row[36]);
_lineitemRow["description"] = Convert.ToString(_row[37]);
g_lineitemTbl.Rows.Add(_lineitemRow);
}
// TB_eTAX_LINEITEM 3Row 입력
if (String.IsNullOrEmpty(_row[43].ToString()) == false && Convert.ToDecimal(_row[43].ToString()) > 0)
{
DataRow _lineitemRow = g_lineitemTbl.NewRow();
_lineitemRow["issueId"] = _IssueId;
_lineitemRow["seqNo"] = 3;
_lineitemRow["purchaseDate"] = Convert.ToDateTime(Convert.ToDateTime(_row[1]).ToString("yyyy-MM-") + Convert.ToInt32(_row[38]).ToString("00"));
_lineitemRow["itemName"] = Convert.ToString(_row[39]);
_lineitemRow["information"] = Convert.ToString(_row[40]);
_lineitemRow["quantity"] = Convert.ToDecimal(_row[41]);
_lineitemRow["unitPrice"] = Convert.ToDecimal(_row[42]);
_lineitemRow["invoiceAmount"] = Convert.ToDecimal(_row[43]);
_lineitemRow["taxAmount"] = Convert.ToDecimal(_row[44]);
_lineitemRow["description"] = Convert.ToString(_row[45]);
g_lineitemTbl.Rows.Add(_lineitemRow);
}
// TB_eTAX_LINEITEM 4Row 입력
if (String.IsNullOrEmpty(_row[51].ToString()) == false && Convert.ToDecimal(_row[51].ToString()) > 0)
{
DataRow _lineitemRow = g_lineitemTbl.NewRow();
_lineitemRow["issueId"] = _IssueId;
_lineitemRow["seqNo"] = 4;
_lineitemRow["purchaseDate"] = Convert.ToDateTime(Convert.ToDateTime(_row[1]).ToString("yyyy-MM-") + Convert.ToInt32(_row[46]).ToString("00"));
_lineitemRow["itemName"] = Convert.ToString(_row[47]);
_lineitemRow["information"] = Convert.ToString(_row[48]);
_lineitemRow["quantity"] = Convert.ToDecimal(_row[49]);
_lineitemRow["unitPrice"] = Convert.ToDecimal(_row[50]);
_lineitemRow["invoiceAmount"] = Convert.ToDecimal(_row[51]);
_lineitemRow["taxAmount"] = Convert.ToDecimal(_row[52]);
_lineitemRow["description"] = Convert.ToString(_row[53]);
g_lineitemTbl.Rows.Add(_lineitemRow);
}
}
LDltaHelper.InsertDeltaTbl(UAppHelper.ConnectionString, g_customerTbl);
LDltaHelper.InsertDeltaTbl(UAppHelper.ConnectionString, g_partnerTbl);
LDltaHelper.InsertDeltaTbl(UAppHelper.ConnectionString, g_invoiceTbl);
LDltaHelper.InsertDeltaTbl(UAppHelper.ConnectionString, g_lineitemTbl);
_result = true;
}
catch (Exception ex)
{
ELogger.SNG.WriteLog(ex);
}
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));
}
}
}
}
/// <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);
}
}
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));
}
}
}
}
