public int ExecuteSql(string TableName, string[] ColNames, string[] ColValues)
{
string SQL1, SQL2;
if (ColNames.Length != ColValues.Length)
{
return(0);
}
try
{
con.Open();
SqlCommand cmd = new SqlCommand();
SQL1 = "";
SQL2 = "";
for (int i = 0; i < ColNames.Length; i++)
{
if (SQL1.ToString() == "")
{
SQL1 = ColNames[i].ToString();
SQL2 = "@" + ColNames[i].ToString();
}
else
{
SQL1 = SQL1 + ", " + ColNames[i].ToString();
SQL2 = SQL2 + ", " + "@" + ColNames[i].ToString();
}
// add parameter value
cmd.Parameters.AddWithValue("@" + ColNames[i], ColValues[i]); // (@param name, value)
}
string strCommand = "Insert into " + TableName + " ( " + SQL1 + " ) " + " VALUES ( " + SQL2 + " )";
cmd.CommandText = strCommand;
cmd.Connection = con;
return(cmd.ExecuteNonQuery());
}
catch (Exception ex)
{
return(0);
}
finally
{
con.Close();
}
}
public string SaveDataSimple(string TableName, string[] ColNames, string[] ColValues)
{
string SQL;
string SQL1, SQL2;
try
{
con.Open();
//SqlConnection con = new SqlConnection();
//conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString1"].ConnectionString;
SqlCommand cmd = new SqlCommand();
SQL1 = "";
SQL2 = "";
for (int i = 0; i < ColNames.Length; i++)
{
if (SQL1.ToString() == "")
{
SQL1 = ColNames[i].ToString();
SQL2 = "@" + ColNames[i].ToString();
}
else
{
SQL1 = SQL1 + ", " + ColNames[i].ToString();
SQL2 = SQL2 + ", " + "@" + ColNames[i].ToString();
}
// add parameter value
cmd.Parameters.AddWithValue("@" + ColNames[i], ColValues[i]); // (@param name, value)
}
string strCommand = "Insert into " + TableName + " ( " + SQL1 + " ) " + " VALUES ( " + SQL2 + " )";
cmd.CommandText = strCommand;
cmd.Connection = con;
cmd.ExecuteNonQuery();
return("1");
}
catch (Exception ex)
{
return("0");
}
finally
{
con.Close();
}
}
private void ValidDetailsHasError(ErrorInfoModel errorInfo)
{
var detailsError = errorInfo.ErrorBuffer.Where(o => o.Key == "B0401D").ToList();
if (detailsError.Count > 0)
{
using (var sqlAdapter = new SQL1())
{
foreach (var error in detailsError)
{
var detail = error.Value.Details.First();
string index = (error.Value.Details.IndexOf(detail) + 1).ToString();
sqlAdapter.GoLogsAll(error.Key, ProcessName, detail.SourceFile, detail.ErrorMessage, index, detail.ErrorLevel, false);
if (detail.ErrorGroupKey == 1 || detail.ErrorGroupKey == 3)
{
continue;
}
sqlAdapter.GoToSTemp("B0401D", " MAllowanceNumber='" + detail.EinvoiceNumber + "' ");
sqlAdapter.GoToSTemp("B0401H", " MAllowanceNumber='" + detail.EinvoiceNumber + "' ");
}
}
}
}
public void Begin2(string path, string sKind0, ErrorInfoModel errorInfo, string identityKey)
{
if (!string.IsNullOrEmpty(sKind0))
{
sKind0 = sKind0.ToUpper();
}
string[] sArr;
using (SQL1 sqlAdapter = new SQL1())
{ sqlAdapter.ReturnArr(out sArr); }
string sPaPartition = sArr[3];
using (SQL1 sqlAdapter = new SQL1())
{
List <EinvoiceB0401DTemp> tempData = new List <EinvoiceB0401DTemp>();
var query = sqlAdapter.Kind1SelectTbl2("*", "FILE_TEMP", " EINVOICE_TP='B0401D' and IDENT_KEY = '" + identityKey + "'", "", "");
if (query != null)
{
var rows = query.Table.Rows;
for (int i = 0; i < rows.Count; i++)
{
tempData.Add(new EinvoiceB0401DTemp()
{
EinvoiceContent = rows[i]["FILE_CONTENT"].ToString(),
EinvoiceFIlePath = rows[i]["FILE_NM"].ToString(),
});
}
}
else
{
return;
}
string sPgSN = DateTime.Now.ToString("yyyyMMddHHmmssfff");
sqlAdapter.GoLogsAll(sPgSN, ProcessName, "FILE_TEMP", "", "", 1);
//抓 B0401D*.* 的所有檔案
foreach (var data in tempData)
{
int index = tempData.IndexOf(data) + 1;
string sourceFile = data.EinvoiceFIlePath;
string einvoiceNumber = string.Empty;
string einvoiceDescription = string.Empty;
string errorMsg = string.Empty;
try
{
string line = "";
List <Hashtable> einvoiceDataList = new List <Hashtable>();
line = data.EinvoiceContent;
if (line.Trim() != "")
{
string[] CutS = { sPaPartition };
string[] charA = line.Split(CutS, StringSplitOptions.None);
einvoiceNumber = charA[0];
einvoiceDescription = charA[1];
//字串尾要分號//共13個分號
if (charA.Length == 13)
{
#region 檢查Head有無寫入資料 有的話不寫入明細
var chkHeadHasError = EinvoiceRepository.CheckHeadHasError(sKind0, einvoiceNumber, errorInfo);
if (chkHeadHasError)
{
errorMsg = "[正式][" + sKind0 + "]" + einvoiceNumber + "[折讓證明單Head寫入時有錯，明細資料不寫入]";
EinvoiceRepository.AddEinvoiceToErrorBuffer(sKind0, einvoiceNumber, errorMsg, 1, 51, ref errorInfo, ProcessName, sourceFile, einvoiceDescription);
continue;
}
#endregion 檢查Head有無寫入資料 有的話不寫入明細
if (sqlAdapter.Kind1SelectTbl3("B0401DSN", "MAllowanceNumber='" + charA[0].ToString() + "' And DAllowanceSequenceNumber='" + charA[10].ToString() + "'", "B0401D") == 0)
{
if (charA[1].ToString().Trim() != "" && charA[2].ToString().Trim() != "")
{
Console.WriteLine(string.Format("{0} 寫入正式資料庫 第{1}筆 發票號碼:{2}開始.", sKind0, index, einvoiceNumber));
Hashtable hashTable = new Hashtable();
hashTable["MAllowanceNumber"] = charA[0].ToString().Trim();
hashTable["DOriginalInvoiceDate"] = charA[1].ToString().Trim();
hashTable["DOriginalInvoiceNumber"] = charA[2].ToString().Trim();
hashTable["DOriginalSequenceNumber"] = charA[3].ToString().Trim();
hashTable["DOriginalDescription"] = charA[4].ToString().Trim();
hashTable["DQuantity"] = charA[5].ToString().Trim();
hashTable["DUnit"] = charA[6].ToString().Trim();
hashTable["DUnitPrice"] = charA[7].ToString().Trim();
hashTable["DAmount"] = charA[8].ToString().Trim();
hashTable["DTax"] = charA[9].ToString().Trim();
hashTable["DAllowanceSequenceNumber"] = charA[10].ToString().Trim();
hashTable["DTaxType"] = charA[11].ToString().Trim();
hashTable["TxFileNmae"] = sourceFile;
string insertMsg = sqlAdapter.InsertDataNonKey("B0401D", hashTable);
#region 寫入有錯誤之處理
if (!string.IsNullOrEmpty(insertMsg))
{
errorMsg = "[正式][" + sKind0 + "]" + einvoiceNumber + "[TXT寫入正式資料庫發生錯誤，資料不寫入]" + insertMsg;
EinvoiceRepository.AddEinvoiceToErrorBuffer(sKind0, einvoiceNumber, errorMsg, 2, 51, ref errorInfo, ProcessName, sourceFile, einvoiceDescription);
}
else
{
Console.WriteLine(string.Format("{0} 寫入正式資料庫 第{1}筆 發票號碼:{2}結束.", sKind0, index, einvoiceNumber));
}
#endregion 寫入有錯誤之處理
}
else
{
errorMsg = "[MInvoiceNumber:" + charA[0].ToString().Trim() + "][明細排列序號:" + charA[10].ToString().Trim() + "]";
EinvoiceRepository.AddEinvoiceToErrorBuffer(sKind0, einvoiceNumber, errorMsg + "[必要欄位沒值!!][DOriginalInvoiceDate][DOriginalInvoiceNumber]", 3, 17, ref errorInfo, ProcessName, sourceFile, einvoiceDescription);
}
}
else
{
errorMsg = "[MInvoiceNumber:" + charA[0].ToString().Trim() + "][明細排列序號:" + charA[10].ToString().Trim() + "]";
EinvoiceRepository.AddEinvoiceToErrorBuffer(sKind0, einvoiceNumber, errorMsg + "[匯入商品細項的文字檔發生錯誤/此商品已存在!!]", 3, 16, ref errorInfo, ProcessName, sourceFile, einvoiceDescription);
}
}
else
{
errorMsg = "[正式][折讓證明單號碼:" + einvoiceNumber + "][字串尾要分號，共10個分號]";
EinvoiceRepository.AddEinvoiceToErrorBuffer(sKind0, einvoiceNumber, errorMsg, 4, 12, ref errorInfo, ProcessName, sourceFile, einvoiceDescription);
}
}
else
{
errorMsg = "[正式][折讓證明單號碼:" + einvoiceNumber + "][讀取資料內容為空白]";
EinvoiceRepository.AddEinvoiceToErrorBuffer(sKind0, einvoiceNumber, errorMsg, 4, 12, ref errorInfo, ProcessName, sourceFile, einvoiceDescription);
}
}
catch (Exception ex)
{
errorMsg = "[正式][未知錯誤]";
EinvoiceRepository.AddEinvoiceToErrorBuffer(sKind0, einvoiceNumber, errorMsg, 5, 15, ref errorInfo, ProcessName, sourceFile, einvoiceDescription);
}
}
sqlAdapter.GoLogsAll(sPgSN, ProcessName, "FILE_TEMP", "", "", 2);
ValidDetailsHasError(errorInfo);
}
}
public void Begin2(string path, string sKind0, ErrorInfoModel errorInfo, string identityKey)
{
if (!string.IsNullOrEmpty(sKind0))
{
sKind0 = sKind0.ToUpper();
}
string[] sArr;
using (SQL1 sqlAdapter = new SQL1())
{ sqlAdapter.ReturnArr(out sArr); }
string sPaPartition = sArr[3];
using (SQL1 sqlAdapter = new SQL1())
{
List <EinvoiceC0701Temp> tempData = new List <EinvoiceC0701Temp>();
var query = sqlAdapter.Kind1SelectTbl2("*", "FILE_TEMP", " EINVOICE_TP='C0701' and IDENT_KEY = '" + identityKey + "'", "", "");
if (query != null)
{
var rows = query.Table.Rows;
for (int i = 0; i < rows.Count; i++)
{
tempData.Add(new EinvoiceC0701Temp()
{
EinvoiceContent = rows[i]["FILE_CONTENT"].ToString(),
EinvoiceFIlePath = rows[i]["FILE_NM"].ToString(),
});
}
}
else
{
return;
}
string sPgSN = DateTime.Now.ToString("yyyyMMddHHmmssfff");
sqlAdapter.GoLogsAll(sPgSN, ProcessName, "FILE_TEMP", "", "", 1);
foreach (var data in tempData)
{
int index = tempData.IndexOf(data) + 1;
string sourceFile = data.EinvoiceFIlePath;
string einvoiceNumber = string.Empty;
string errorMsg = string.Empty;
try
{
string line = "";
List <Hashtable> einvoiceDataList = new List <Hashtable>();
line = data.EinvoiceContent;
if (line.Trim() != "")
{
string[] CutS = { sPaPartition };
string[] charA = line.Split(CutS, StringSplitOptions.None);
einvoiceNumber = charA[0].ToUpper();
//字串尾要分號//共9個分號
if (charA.Length == 9)
{
if (sqlAdapter.Kind1SelectTbl3("D0501SN", "CancelAllowanceNumber='" + charA[0].ToString() + "'", "D0501") == 0)
{
Console.WriteLine(string.Format("{0} 寫入正式資料庫 第{1}筆 發票號碼:{2}開始.", sKind0, index, einvoiceNumber));
Hashtable hashData = SetEinvoiceToHashtable(charA, sourceFile);
string insertMsg = sqlAdapter.InsertDataNonKey("D0501", hashData);
#region 寫入有錯誤之處理
if (!string.IsNullOrEmpty(insertMsg))
{
errorMsg = "[正式][" + sKind0 + "]" + einvoiceNumber + "[TXT寫入正式資料庫發生錯誤，資料不寫入]" + insertMsg;
sqlAdapter.GoLogsAll(sPgSN, ProcessName, sourceFile, errorMsg, index.ToString(), 51, false);
EinvoiceRepository.AddEinvoiceToErrorBuffer(sKind0, einvoiceNumber, errorMsg, 2, 51, ref errorInfo, ProcessName);
}
else
{
Console.WriteLine(string.Format("{0} 寫入正式資料庫 第{1}筆 發票號碼:{2}結束.", sKind0, index, einvoiceNumber));
}
#endregion 寫入有錯誤之處理
}
else
{
errorMsg = "[正式][作廢折讓證明單號碼:" + einvoiceNumber + "][此發票號碼已存在,資料不寫入]";
sqlAdapter.GoLogsAll(sPgSN, ProcessName, sourceFile, errorMsg, index.ToString(), 11, false);
EinvoiceRepository.AddEinvoiceToErrorBuffer(sKind0, einvoiceNumber, errorMsg, 3, 11, ref errorInfo, ProcessName);
}
}
else
{
errorMsg = "[正式][作廢折讓證明單號碼:" + einvoiceNumber + "][字串尾要分號，共9個分號]";
sqlAdapter.GoLogsAll(sPgSN, ProcessName, sourceFile, "[正式]", index.ToString(), 12);
EinvoiceRepository.AddEinvoiceToErrorBuffer(sKind0, einvoiceNumber, errorMsg, 4, 12, ref errorInfo, ProcessName);
}
}
}
catch (Exception ex)
{
errorMsg = "[正式][未知錯誤]";
sqlAdapter.GoLogsAll(sPgSN, ProcessName, sourceFile, errorMsg + ex.ToString(), "", 15, false);
EinvoiceRepository.AddEinvoiceToErrorBuffer(sKind0, einvoiceNumber, errorMsg, 5, 15, ref errorInfo, ProcessName);
}
}
sqlAdapter.GoLogsAll(sPgSN, ProcessName, "FILE_TEMP", "", "", 2);
}
}
public void Begin2(string path, string sKind0, ErrorInfoModel errorInfo, string identityKey)
{
Dictionary <string, List <string> > pdfNumList = new Dictionary <string, List <string> >();
if (!string.IsNullOrEmpty(sKind0))
{
sKind0 = sKind0.ToUpper();
}
string[] sArr;
using (SQL1 sqlAdapter = new SQL1())
{ sqlAdapter.ReturnArr(out sArr); }
string sPaPartition = sArr[3];
using (var sqlAdapter = new SQL1())
{
List <EinvoiceA0401HTemp> tempData = new List <EinvoiceA0401HTemp>();
var query = sqlAdapter.Kind1SelectTbl2("*", "FILE_TEMP", " EINVOICE_TP='A0401H' and IDENT_KEY = '" + identityKey + "'", "", "");
if (query != null)
{
var rows = query.Table.Rows;
for (int i = 0; i < rows.Count; i++)
{
tempData.Add(new EinvoiceA0401HTemp()
{
EinvoiceContent = rows[i]["FILE_CONTENT"].ToString(),
EinvoiceFIlePath = rows[i]["FILE_NM"].ToString(),
});
}
}
else
{
return;
}
string sPgSN = DateTime.Now.ToString("yyyyMMddHHmmssfff");
sqlAdapter.GoLogsAll(sPgSN, ProcessName, "FILE_TEMP", "", "", 1);
foreach (var data in tempData)
{
int index = tempData.IndexOf(data) + 1;
string sourceFile = data.EinvoiceFIlePath;
string einvoiceNumber = string.Empty;
string errorMsg = string.Empty;
try
{
string line = "";
List <Hashtable> einvoiceDataList = new List <Hashtable>();
line = data.EinvoiceContent;
if (line.Trim() != "")
{
string[] CutS = { sPaPartition };
string[] charA = line.Split(CutS, StringSplitOptions.None);
einvoiceNumber = charA[0].ToUpper();
//字串尾要分號//共48個分號
if (charA.Length == 48)
{
if (sqlAdapter.Kind1SelectTbl3("A0401SN", "MInvoiceNumber='" + charA[0].ToString() + "'", "A0401H") == 0)
{
Console.WriteLine(string.Format("{0} 寫入正式資料庫 第{1}筆 發票號碼:{2}開始.", sKind0, index, einvoiceNumber));
Dictionary <bool, string> validErrorBuffer = ValidInvoiceData(charA);
if (validErrorBuffer.Count > 0 && validErrorBuffer.Keys.First() == false)
{
errorMsg = validErrorBuffer.Values.First() + "[正式]驗證失敗,資料不寫入";
sqlAdapter.GoLogsAll(sPgSN, ProcessName, sourceFile, errorMsg, index.ToString(), 11, false);
EinvoiceRepository.AddEinvoiceToErrorBuffer(sKind0, einvoiceNumber, errorMsg, 1, 11, ref errorInfo, ProcessName);
continue;
}
Hashtable hashData = SetEinvoiceToHashtable(charA, sourceFile);
string insertMsg = sqlAdapter.InsertDataNonKey("A0401H", hashData);
#region 寫入有錯誤之處理
if (!string.IsNullOrEmpty(insertMsg))
{
errorMsg = "[正式][" + sKind0 + "]" + einvoiceNumber + "[TXT寫入正式資料庫發生錯誤，資料不寫入]" + insertMsg;
sqlAdapter.GoLogsAll(sPgSN, ProcessName, sourceFile, errorMsg, index.ToString(), 51, false);
EinvoiceRepository.AddEinvoiceToErrorBuffer(sKind0, einvoiceNumber, errorMsg, 2, 51, ref errorInfo, ProcessName);
Console.WriteLine(errorMsg);
}
else
{
if (!pdfNumList.ContainsKey(sKind0))
{
pdfNumList[sKind0] = new List <string>();
}
pdfNumList[sKind0].Add(einvoiceNumber);
Console.WriteLine(string.Format("{0} 寫入正式資料庫 第{1}筆 發票號碼:{2}完成.", sKind0, index, einvoiceNumber));
}
#endregion 寫入有錯誤之處理
}
else
{
errorMsg = "[正式][發票號碼:" + einvoiceNumber + "][此發票號碼已存在,資料不寫入]";
sqlAdapter.GoLogsAll(sPgSN, ProcessName, sourceFile, errorMsg, index.ToString(), 11, false);
EinvoiceRepository.AddEinvoiceToErrorBuffer(sKind0, einvoiceNumber, errorMsg, 3, 11, ref errorInfo, ProcessName);
}
}
else
{
errorMsg = "[正式][發票號碼:" + einvoiceNumber + "][字串尾要分號，共48個分號]";
sqlAdapter.GoLogsAll(sPgSN, ProcessName, sourceFile, "[正式]", index.ToString(), 12);
EinvoiceRepository.AddEinvoiceToErrorBuffer(sKind0, einvoiceNumber, errorMsg, 4, 12, ref errorInfo, ProcessName);
}
}
}
catch (Exception ex)
{
errorMsg = "[正式][未知錯誤]";
sqlAdapter.GoLogsAll(sPgSN, ProcessName, sourceFile, errorMsg + ex.ToString(), "", 15, false);
EinvoiceRepository.AddEinvoiceToErrorBuffer(sKind0, einvoiceNumber, errorMsg, 5, 15, ref errorInfo, ProcessName);
}
}
sqlAdapter.GoLogsAll(sPgSN, ProcessName, "FILE_TEMP", "", "", 2);
foreach (var inv in pdfNumList)
{
foreach (var item in inv.Value)
{
Hashtable hashTable = new Hashtable();
hashTable["PRINT_METHOD"] = inv.Key;
hashTable["PRINT_EINV_NUM"] = item;
//hashTable["MAKE_FILE_DATE"] = DateTime.UtcNow;
hashTable["WRITE_DATE"] = DateTime.UtcNow;
hashTable["IDEN_KEY"] = identityKey;
string insertMsg = sqlAdapter.InsertDataNonKey("PRINT_TEMP", hashTable);
}
}
}
}
/// <summary>
/// 處理錯誤通知mail
/// </summary>
/// <param name="errorInfo"></param>
private void SendErrorEmail(ErrorInfoModel errorInfo)
{
SQL1 sqlAdaper = new SQL1();
StringBuilder htmlContent = new StringBuilder();
string propertiesHtml = string.Empty;
var groupData = errorInfo.ErrorBuffer.OrderBy(o => o.Key)
.GroupBy(o => o.Key, o => o.Value).ToList();
//分出A0401H，C0401H
foreach (var group in groupData)
{
var dataTotalCount = group.Sum(s => s.Details.Count);
string einvoiceType = group.Key;
htmlContent.AppendLine(@"<div style=""border:solid black 1px;padding:7px;"">");
htmlContent.AppendLine(@"<p style=""font-size:23px;"">");
htmlContent.AppendLine("發票處理程序：" + einvoiceType + " 資料筆數：" + dataTotalCount);
htmlContent.AppendLine("<p>");
var errorsGroup = group.First().Details.GroupBy(g => g.ErrorGroupKey).Select(s => s);
//分出錯誤群組
foreach (var error in errorsGroup)
{
var erKey = error.Key;
htmlContent.AppendLine("<p>");
htmlContent.AppendLine("錯誤分類：" + erKey);
htmlContent.AppendLine("<p>");
int dataIndex = 1;
foreach (var er in error)
{
htmlContent.AppendLine(@"<div style=""border:solid #ccc 1px;padding:3px;"">");
htmlContent.AppendLine("<p>");
htmlContent.AppendLine("項目：" + dataIndex);
htmlContent.AppendLine("<p>");
htmlContent.AppendLine("<p>");
htmlContent.AppendLine("錯誤發票：" + er.EinvoiceNumber);
htmlContent.AppendLine("<p>");
htmlContent.AppendLine("<p>");
htmlContent.AppendLine("錯誤訊息：" + er.ErrorMessage);
htmlContent.AppendLine("<p>");
htmlContent.AppendLine("<p>");
htmlContent.AppendLine("其他訊息：" + er.OtherMessage);
htmlContent.AppendLine("<p>");
htmlContent.AppendLine("<p>");
htmlContent.AppendLine("處理程序：" + er.ProcessName);
htmlContent.AppendLine("<p>");
htmlContent.AppendLine("</div>");
dataIndex++;
}
}
htmlContent.AppendLine("</div>");
}
var viewHtml = htmlContent.ToString();
XMLClass oXMLeParamts = new XMLClass();
string eToWho1 = oXMLeParamts.GetParaXml("eToWho");
string eFromWho1 = oXMLeParamts.GetParaXml("eFromWho");
sqlAdaper.AutoEMail(eToWho1, "", eFromWho1, "", htmlContent.ToString());
sqlAdaper.Dispose();
}
/// <summary>
/// 移動D:\
/// </summary>
public string MoveToTempDatabase(List <string> getEinvoiceTypes, string dirID = null, bool isTest = false)
{
var today = DateTime.UtcNow.AddHours(8);
string dateSn = string.Concat(
today.Year,
today.Month.ToString().PadLeft(2, '0'),
today.Day.ToString().PadLeft(2, '0'), ".",
today.Hour.ToString().PadLeft(2, '0'),
today.Minute.ToString().PadLeft(2, '0'),
".");
string tempID = dateSn + new Random().Next(22, 555);
string txtSource = dirID == null || dirID == "ALL" ? @"D:\ImInputERP" : @"D:\ImInputERP\" + dirID;
string tempDir = @"D:\eInvoiceFile\historyText\";
string virtualTempDir = string.Concat(tempDir, tempID, "\\");
//bool isEmpty = !Directory.EnumerateFiles(txtSource).Any();
//if (isEmpty)
// return null;
if (!Directory.Exists(virtualTempDir))
{
Directory.CreateDirectory(virtualTempDir);
}
if (!Directory.Exists(txtSource))
{
throw new Exception("[寫入暫存]無法取得目錄，是否尚未建立." + txtSource);
}
using (SQL1 query = new SQL1())
{
string catchFilePath = string.Empty;
try
{
Console.WriteLine("開始搬檔...");
Console.WriteLine(string.Format("從{0}移動至{1}", txtSource, virtualTempDir));
//根據條件搬移指定的檔名
foreach (var type in getEinvoiceTypes)
{
int index = 1;
//move all files to temp directory
foreach (string file in Directory.GetFileSystemEntries(txtSource, type + "*.*"))
{
var extension = Path.GetExtension(file);
if (isTest)
{
if (file.IndexOf("ExXml") == -1)
{
if (file.ToUpper().IndexOf("TEST") >= 0 || string.IsNullOrEmpty(extension))
{
if (file.ToUpper().IndexOf("TESTTEST") == -1)
{
continue;
}
}
}
}
else
{
if (file.IndexOf("ExXml") == -1)
{
if (file.ToUpper().IndexOf("TEST") >= 0 || string.IsNullOrEmpty(extension))
{
continue;
}
}
}
Console.WriteLine(string.Format("{0}第{1}筆 開始.", type, index));
catchFilePath = file;
//File.Copy(file, Path.Combine(virtualTempDir, Path.GetFileName(file)));
File.Move(file, Path.Combine(virtualTempDir, Path.GetFileName(file)));
Console.WriteLine(string.Format("{0}第{1}筆 完成.", type, index));
index++;
}
}
}
catch (Exception ex)
{
query.GoLogsAll(dateSn, ProcessName, catchFilePath + "[寫入暫存]請檢視檔案是否完成移動至暫存目錄.", ex.Message, "0", 13);
}
}
return(virtualTempDir);
}
/// <summary>
/// 將TXT寫入暫存資料庫
/// </summary>
/// <param name="path">暫存目錄路徑</param>
/// <param name="fileType">發票種類 ex:A0401H</param>
private void WriteDataToDatabase(string path, string fileType, string identityKey)
{
using (SQL1 query = new SQL1())
{
foreach (string sourceFile in System.IO.Directory.GetFileSystemEntries(path, fileType + "*.*"))
{
int index = 1;
string einvoiceNumber = string.Empty;
using (StreamReader txtFile = new StreamReader(sourceFile, Encoding.Default))
{
try
{
string line = "";
while ((line = txtFile.ReadLine()) != null)
{
Console.WriteLine(string.Format(fileType + "寫入暫存資料庫 第{0}筆 開始.", index));
if (line.Trim() != "")
{
string[] charA = line.Split(new string[] { ";" }, StringSplitOptions.None);
einvoiceNumber = charA[0];
#region 檢查Head是否寫入時有錯誤 若有，明細一併不寫入
string headString = fileType.Replace("H", "").Replace("D", "") + "H";
if (ErrorEinvoiceNums.ContainsKey(headString))
{
var errEinvoice = ErrorEinvoiceNums[headString].Where(o => o == charA[0]).ToList();
if (errEinvoice.Count > 0)
{
continue;
}
}
#endregion 檢查Head是否寫入時有錯誤 若有，明細一併不寫入
Hashtable hashTable = new Hashtable();
hashTable["IDENT_KEY"] = identityKey;
hashTable["FILE_NM"] = sourceFile;
hashTable["FILE_CONTENT"] = line;
hashTable["REMARK"] = "";
hashTable["EINVOICE_TP"] = fileType;
hashTable["EINVOICE_NUM"] = charA[0].ToUpper();
hashTable["BUD_DATE"] = DateTime.UtcNow.AddHours(8);
string insertMsg = query.InsertDataNonKey("FILE_TEMP", hashTable);
#region 寫入有錯誤之處理
if (!string.IsNullOrEmpty(insertMsg))
{
query.GoLogsAll(fileType, ProcessName, sourceFile, "[寫入暫存][TXT寫入暫存發生錯誤，若有明細將不寫入]" + insertMsg, index.ToString(), 51);
if (!ErrorEinvoiceNums.ContainsKey(fileType))
{
ErrorEinvoiceNums.Add(fileType, new List <string>());
}
ErrorEinvoiceNums[fileType].Add(einvoiceNumber);
}
#endregion 寫入有錯誤之處理
}
Console.WriteLine(string.Format(fileType + "寫入暫存資料庫 第{0}筆 完成.", index));
index++;
einvoiceNumber = "";
}
}
catch (Exception ex)
{
query.GoLogsAll(fileType, ProcessName, sourceFile, "[寫入暫存" + ex.Message, index.ToString(), 13);
}
}
}
}
}
public void Begin(string sKind0)
{
string[] sArr;
using (SQL1 query = new SQL1())
{ query.ReturnArr(out sArr); }
string sFPathN = sArr[0];
string sFPathP = sArr[1];
string sFPathY = sArr[2];
string sPaPartition = sArr[3];
//Console.WriteLine(sFPathN);
//抓 A0401D*.* 的所有檔案
foreach (string OkFName in System.IO.Directory.GetFileSystemEntries(sFPathN, sKind0 + "*.*"))
{
Console.WriteLine("檔案名稱1:" + OkFName);
string sPgSN = DateTime.Now.ToString("yyyyMMddHHmmssfff");
try
{
System.IO.File.Move(OkFName, OkFName.Replace(sFPathN, sFPathP));
string OkFNameP = OkFName.Replace(sFPathN, sFPathP);
string line = "";
int counter = 0;
using (System.IO.StreamReader txtFile = new System.IO.StreamReader(OkFNameP, System.Text.Encoding.Default))
{
Console.WriteLine("檔案名稱2:" + OkFNameP);
using (NSysDB.NTSQL.SQL1 query = new NSysDB.NTSQL.SQL1())
{
//開始匯入
query.GoLogsAll(sPgSN, System.Diagnostics.Process.GetCurrentProcess().MainModule.FileName, OkFName, "", "", 1);
}
while ((line = txtFile.ReadLine()) != null)
{
if (line.Trim() != "")
{
//Console.WriteLine(line);
string[] CutS = { sPaPartition };
string[] charA = line.Split(CutS, StringSplitOptions.None);
//字串尾要分號//共10個分號
if (charA.Length == 10)
{
Console.WriteLine("發票號碼:" + charA[0].ToString());
//for (int i = 0; i < charA.Length-1; i++)
//{
// Console.WriteLine("Index : {0}, 字串 : {1}", (i + 1), charA[i]);
//}
using (SQL1 query = new SQL1())
{
try
{
if (query.Kind1SelectTbl3("A0401DSN", "MInvoiceNumber='" + charA[0].ToString() + "' And DSequenceNumber='" + charA[6].ToString() + "'", "A0401D") == 0)
{
System.Collections.Hashtable data = new System.Collections.Hashtable();
data["MInvoiceNumber"] = charA[0].ToString().Trim();
data["DDescription"] = charA[1].ToString().Trim();
data["DQuantity"] = charA[2].ToString().Trim();
data["DUnit"] = charA[3].ToString().Trim();
data["DUnitPrice"] = charA[4].ToString().Trim();
data["DAmount"] = charA[5].ToString().Trim();
data["DSequenceNumber"] = charA[6].ToString().Trim();
data["DRemark"] = charA[7].ToString().Trim();
data["DRelateNumber"] = charA[8].ToString().Trim();
data["TxFileNmae"] = OkFName.ToString().Trim();
query.InsertDataNonKey("A0401D", data);
data = null;
}
else
{
query.GoLogsAll(sPgSN, System.Diagnostics.Process.GetCurrentProcess().MainModule.FileName, OkFName, "[MInvoiceNumber:" + charA[0].ToString().Trim() + "][明細排列序號:" + charA[6].ToString().Trim() + "]", (counter + 1).ToString(), 16);
query.GoToSTemp("A0401D", " MInvoiceNumber='" + charA[0].ToString() + "' ");
query.GoToSTemp("A0401H", " MInvoiceNumber='" + charA[0].ToString() + "' ");
}
}
catch (Exception ex)
{
query.GoLogsAll(sPgSN, System.Diagnostics.Process.GetCurrentProcess().MainModule.FileName, OkFName, ex.ToString(), (counter + 1).ToString(), 11);
query.GoToSTemp("A0401D", " MInvoiceNumber='" + charA[0].ToString() + "' ");
query.GoToSTemp("A0401H", " MInvoiceNumber='" + charA[0].ToString() + "' ");
}
}
}
else
{
using (NSysDB.NTSQL.SQL1 query = new NSysDB.NTSQL.SQL1())
{
query.GoLogsAll(sPgSN, System.Diagnostics.Process.GetCurrentProcess().MainModule.FileName, OkFName, "", (counter + 1).ToString(), 12);
}
}
Console.WriteLine("間隔數:" + charA.Length.ToString());
counter++;
}
}
using (NSysDB.NTSQL.SQL1 query = new NSysDB.NTSQL.SQL1())
{
//結束匯入
query.GoLogsAll(sPgSN, System.Diagnostics.Process.GetCurrentProcess().MainModule.FileName, OkFName, "", "", 2);
}
}
Console.WriteLine("筆數:" + counter.ToString());
System.IO.File.Move(OkFNameP, OkFNameP.Replace(sFPathP, sFPathY).Replace(".txt", "_" + sPgSN + ".txt"));
}
catch (Exception ex)
{
using (NSysDB.NTSQL.SQL1 query = new NSysDB.NTSQL.SQL1())
{
query.GoLogsAll(sPgSN, System.Diagnostics.Process.GetCurrentProcess().MainModule.FileName, OkFName, "此檔案已存於:" + sFPathP + " [" + ex + "]", "", 13);
}
}
//try
//{
// //檔案已存在的FileMove
// Console.WriteLine(OkFName);
// Console.WriteLine(OkFName.Replace(sFPathN, sFPathY));
// System.IO.File.Move(OkFName, OkFName.Replace(sFPathN, sFPathY) + sPgSN);
// //Exception未處理,檔案已存在時，無法建立該檔案。
//}
//catch (Exception ex)
//{
// Console.WriteLine("檔案已存在!!");
// using (NSysDB.NTSQL.SQL1 query = new NSysDB.NTSQL.SQL1())
// {
// query.GoLogsAll(sPgSN, System.Diagnostics.Process.GetCurrentProcess().MainModule.FileName, OkFName, ex.ToString(), "", 13);
// }
//}
}
//}
//catch (Exception ex)
//{
// using (NSysDB.NTSQL.SQL1 query = new NSysDB.NTSQL.SQL1())
// {
// // query.GoException(sPgSN, ex.ToString(), "[" + GetType().Assembly.Location + "] [" + System.Reflection.MethodInfo.GetCurrentMethod().ToString() + "]");
// query.GoLogsAll(sPgSN, System.Diagnostics.Process.GetCurrentProcess().MainModule.FileName, "", ex.ToString(), "", 14);
// }
//}
}
