/// <summary>
/// wf_20232_1 (上市)
/// </summary>
/// <param name="filename"></param>
/// <param name="txtDate">yyyyMM</param>
/// <returns></returns>
private DataTable wf_20232_1(string filename, string txtDate, DataTable dt)
{
DataTable dtSource = new DataTable();
try {
//1. 檢查excel的年月是否跟外面txt設定的年月相同
Workbook workbook = new Workbook();
workbook.LoadDocument(filename);
Worksheet worksheet = workbook.Worksheets[0];
string tmpDate = worksheet.Cells[0, 1].Value.AsDateTime().ToString("yyyyMM");
if (tmpDate != txtDate)
{
MessageDisplay.Error(string.Format("轉檔檔案之年月= {0} ,與輸入條件= {1} 不符", tmpDate, txtDate), GlobalInfo.ErrorText);
return(null);
}
//1.1 刪除資料
int delRes = dao20232.DeletePls3(tmpDate, "1");
//1.2 判斷重複
DataTable dtDup = dao20232.GetDataList(tmpDate, "2"); //上櫃的資料
DataTable dtTemp = dtDup.Clone();
string ls_sid = "", ls_val = "", ls_kind = " ";
decimal ld_val = 0, valNum = 0;
foreach (DataRow dr in dt.Rows)
{
if (ls_sid == txtEnd.Text)
{
break;
}
ls_sid = dr["col1"].AsString();
if (string.IsNullOrEmpty(ls_sid))
{
ls_sid = "";
}
ls_val = dr["col2"].AsString();
if (string.IsNullOrEmpty(ls_val))
{
ls_val = "";
}
bool res = decimal.TryParse(ls_val, out valNum);
if (res)
{
ld_val = valNum;
}
if (ls_sid == "" || ls_val == "" || res == false)
{
int sidNum = 0;
bool num = int.TryParse(ls_sid.SubStr(0, 2), out sidNum);
if (num)
{
ls_kind = ls_sid.SubStr(0, 2);
}
continue;
}
ls_sid = ls_sid.SubStr(0, 6);
//尋找重覆
int found = -1;
DataColumn[] keys = new DataColumn[1];
keys[0] = dtDup.Columns[1];
dtDup.PrimaryKey = keys;
found = dtDup.Rows.IndexOf(dtDup.Select("pls3_sid='" + ls_sid + "'").FirstOrDefault());
DataRow drNew = dtTemp.NewRow();
drNew["pls3_pid"] = "1";
drNew["pls3_ym"] = txtDate;
drNew["pls3_sid"] = ls_sid;
drNew["pls3_kind"] = ls_kind;
dtTemp.Rows.Add(drNew);
//成交值
if (found >= 0)
{
ld_val += dtDup.Rows[found]["pls3_amt"].AsDecimal();
dtDup.Rows[found]["pls3_amt"] = ld_val;
}
int count = dtTemp.Rows.Count;
dtTemp.Rows[count - 1]["pls3_amt"] = ld_val;
//成交量
ls_val = dr["col3"].AsString();
decimal valDec;
bool num2 = decimal.TryParse(ls_val, out valDec);
if (num2)
{
ld_val = valDec;
}
if (found >= 0)
{
ld_val += dtDup.Rows[found]["pls3_qnty"].AsDecimal();
dtDup.Rows[found]["pls3_qnty"] = ld_val;
}
dtTemp.Rows[count - 1]["pls3_qnty"] = ld_val;
//成交筆數
ls_val = dr["col4"].AsString();
decimal valDec2;
bool num3 = decimal.TryParse(ls_val, out valDec2);
if (num3)
{
ld_val = valDec2;
}
if (found >= 0)
{
ld_val += dtDup.Rows[found]["pls3_cnt"].AsDecimal();
dtDup.Rows[found]["pls3_cnt"] = ld_val;
}
dtTemp.Rows[count - 1]["pls3_cnt"] = ld_val;
if (found >= 0)
{
dtTemp.Rows[count - 1]["pls3_pid"] = "1";
dtDup.Rows.RemoveAt(found);
}
}
////2.把excel轉成dataTable
//DataColumn[] columns = { new DataColumn("pls3_ym" , typeof(string)) ,
// new DataColumn("pls3_sid" , typeof(string)) ,
// new DataColumn("pls3_kind" , typeof(string)) ,
// new DataColumn("pls3_amt" , typeof(decimal)) ,
// new DataColumn("pls3_qnty" , typeof(decimal)) ,
// new DataColumn("pls3_cnt" , typeof(decimal)) ,
// new DataColumn("pls3_pid" , typeof(string)) };
//dtSource.Columns.AddRange(columns);
//string kind = " ";
//int pos = 0;
//int space = 0;
//for (int k = 6 ; k < 9999 ; k++) {
// pos++;
// ShowMsg(string.Format("訊息：{0} 轉TXT，處理筆數：{1}" , filename , pos));
// string sid = worksheet.Cells[k , 0].Value.AsString();
// if (string.IsNullOrEmpty(sid)) {
// space++;
// continue;
// }
// if (space > 10 || sid == txtEnd.Text)
// break;
// DataRow drNew = dtSource.NewRow();
// if (!string.IsNullOrEmpty(worksheet.Cells[k , 1].Value.AsString()))
// drNew["pls3_amt"] = worksheet.Cells[k , 1].Value.AsDecimal();
// else
// drNew["pls3_amt"] = 0;
// if (!string.IsNullOrEmpty(worksheet.Cells[k , 2].Value.AsString()))
// drNew["pls3_qnty"] = worksheet.Cells[k , 2].Value.AsDecimal();
// else
// drNew["pls3_qnty"] = 0;
// if (!string.IsNullOrEmpty(worksheet.Cells[k , 3].Value.AsString()))
// drNew["pls3_cnt"] = worksheet.Cells[k , 3].Value.AsDecimal();
// else
// drNew["pls3_cnt"] = 0;
// if (string.IsNullOrEmpty(sid) || drNew["pls3_amt"].AsDecimal() == 0) {
// if (sid.SubStr(0 , 2).AsInt() > 0)
// kind = sid.SubStr(0 , 2);
// continue;
// }
// drNew["pls3_ym"] = txtDate;
// drNew["pls3_sid"] = sid.SubStr(0 , 6).Trim();
// drNew["pls3_kind"] = kind;
// drNew["pls3_pid"] = "1";
// dtSource.Rows.Add(drNew);
//}
//3.將dataTable to db table
////好幾個步驟,包含create temp table/insert temp/delete pls3/insert pls3 use group by/drop temp table
//int rowCount = dao20232.ImportDataToPls3(dtTemp , txtDate , "1");
//servername登入可決定是否產此txt檔
if (chkTest.Checked)
{
string testFilenameTxt = string.Format("20232_1({0})_{1}.txt", txtDate, DateTime.Now.ToString("yyyy.MM.dd-HH.mm.ss"));
testFilenameTxt = Path.Combine(GlobalInfo.DEFAULT_REPORT_DIRECTORY_PATH, testFilenameTxt);
Common.Helper.ExportHelper.ToText(dtSource, testFilenameTxt);
}
int updateDtDup = dao20232.ImportDataToPls3(dtDup, txtDate, "2"); //先update dtDup(若有重複資料會在這邊被刪除)
int updateDtTemp = dao20232.ImportDataToPls3(dtTemp, txtDate, "1"); //新的上市資料update
return(dtTemp);
} catch (Exception ex) {
WriteLog(ex);
}
return(null);
}
/// <summary>
/// wf_20232_1
/// </summary>
/// <param name="filename"></param>
/// <param name="txtDate">yyyyMM</param>
/// <returns></returns>
private DataTable wf_20232_1(string filename, string txtDate)
{
DataTable dtSource = new DataTable();
try {
//1. 檢查excel的年月是否跟外面txt設定的年月相同
Workbook workbook = new Workbook();
workbook.LoadDocument(filename);
Worksheet worksheet = workbook.Worksheets[0];
string tmpDate = worksheet.Cells[0, 1].Value.AsDateTime().ToString("yyyyMM");
//string tmpDate = (tmp.Substring(0 , 3).AsInt() - 1911).AsString() + tmp.Substring(3 , 2);
//string tmpDate = "201810";
if (tmpDate != txtDate)
{
MessageDisplay.Error(string.Format("轉檔檔案之年月= {0} ,與輸入條件= {1} 不符", tmpDate, txtDate), GlobalInfo.ErrorText);
return(null);
}
//2.把excel轉成dataTable
DataColumn[] columns = { new DataColumn("pls3_ym", typeof(string)),
new DataColumn("pls3_sid", typeof(string)),
new DataColumn("pls3_kind", typeof(string)),
new DataColumn("pls3_amt", typeof(decimal)),
new DataColumn("pls3_qnty", typeof(decimal)),
new DataColumn("pls3_cnt", typeof(decimal)),
new DataColumn("pls3_pid", typeof(string)) };
dtSource.Columns.AddRange(columns);
string kind = " ";
int pos = 0;
int space = 0;
for (int k = 6; k < 9999; k++)
{
pos++;
ShowMsg(string.Format("訊息：{0} 轉TXT，處理筆數：{1}", filename, pos));
string sid = worksheet.Cells[k, 0].Value.AsString();
if (string.IsNullOrEmpty(sid))
{
space++;
continue;
}
if (space > 10 || sid == txtEnd.Text)
{
break;
}
DataRow drNew = dtSource.NewRow();
if (!string.IsNullOrEmpty(worksheet.Cells[k, 1].Value.AsString()))
{
drNew["pls3_amt"] = worksheet.Cells[k, 1].Value.AsDecimal();
}
else
{
drNew["pls3_amt"] = 0;
}
if (!string.IsNullOrEmpty(worksheet.Cells[k, 2].Value.AsString()))
{
drNew["pls3_qnty"] = worksheet.Cells[k, 2].Value.AsDecimal();
}
else
{
drNew["pls3_qnty"] = 0;
}
if (!string.IsNullOrEmpty(worksheet.Cells[k, 3].Value.AsString()))
{
drNew["pls3_cnt"] = worksheet.Cells[k, 3].Value.AsDecimal();
}
else
{
drNew["pls3_cnt"] = 0;
}
if (string.IsNullOrEmpty(sid) || drNew["pls3_amt"].AsDecimal() == 0)
{
if (sid.SubStr(0, 2).AsInt() > 0)
{
kind = sid.SubStr(0, 2);
}
continue;
}
drNew["pls3_ym"] = txtDate;
drNew["pls3_sid"] = sid.SubStr(0, 6).Trim();
drNew["pls3_kind"] = kind;
drNew["pls3_pid"] = "1";
dtSource.Rows.Add(drNew);
}
//3.將dataTable to db table
//好幾個步驟,包含create temp table/insert temp/delete pls3/insert pls3 use group by/drop temp table
int rowCount = dao20232.ImportDataToPls3(dtSource, txtDate, "1");
//servername登入可決定是否產此txt檔
if (chkTest.Checked)
{
string testFilenameTxt = string.Format("20232_1({0})_{1}.txt", txtDate, DateTime.Now.ToString("yyyy.MM.dd-HH.mm.ss"));
testFilenameTxt = Path.Combine(GlobalInfo.DEFAULT_REPORT_DIRECTORY_PATH, testFilenameTxt);
Common.Helper.ExportHelper.ToText(dtSource, testFilenameTxt);
}
} catch (Exception ex) {
WriteLog(ex);
}
return(dtSource);
}