private void sbQuery_Click(object sender, EventArgs e)
{
_MajorDt = null;
// 判斷日期
if (deStart.Text.Length == 0 || deEnd.Text.Length == 0)
{
MessageBox.Show("請先選取日期區間");
return;
}
// 判斷start date 不能比end date晚
DateTime dStart = DateTime.Parse(deStart.Text);
DateTime dEnd = DateTime.Parse(deEnd.Text);
if (DateTime.Compare(dStart, dEnd) > 0)
{
MessageBox.Show("起始日不可大於結束日");
return;
}
// 判斷區間不要超過一個月
TimeSpan ts = dEnd - dStart;
int differenceInDays = ts.Days;
if (differenceInDays > 180)
{
MessageBox.Show("日期區間請不要超過180天");
return;
}
try
{
using (SqlConnection connection = MvDbConnector.getErpDbConnection(MvCompanySite.MACHVISION))
{
connection.Open();
//_majorDt = MvDbDao.CollectData_CustomerOrder(connection, _customerOrderTypes, "20200125", "20200523");
_MajorDt = MvDbDao.CollectData_CustomerOrder(connection, _CustomerOrderTypes, deStart.DateTime.ToString("yyyyMMdd"), deEnd.DateTime.ToString("yyyyMMdd"));
}
}
catch (SqlException)
{
MessageBox.Show("Cant fetch data from ERP, Please inform MIS to check this issue");
return;
}
gridControl1.DataSource = _MajorDt;
gridView1.OptionsBehavior.Editable = false;
gridView1.RefreshData();
}
private void getErpNoteList()
{
using (SqlConnection conn = MvDbConnector.getErpDbConnection(GlobalMvVariable.MvAdCompany))
{
DataTable tempDt = null;
conn.Open();
string tempSql = "SELECT MQ001, MQ001+' '+MQ002 NOTE FROM CMSMQ WHERE MQ003='31' ORDER BY MQ001";
tempDt = MvDbConnector.queryDataBySql(conn, tempSql);
if (tempDt == null || tempDt.Rows.Count == 0)
{
MessageBox.Show("取得表單資料有問題");
return;
}
cboErpNotePr.DataSource = tempDt;
cboErpNotePr.ValueMember = "MQ001";
cboErpNotePr.DisplayMember = "NOTE";
cboErpNotePr.Text = "";
cboErpNotePr.SelectedValue = "";
}
}
private void sbOk_Click(object sender, EventArgs e)
{
if (deEnd.Text.Length == 0)
{
MessageBox.Show("請先選取付款截止日");
return;
}
string aa = deEnd.DateTime.ToString("yyyyMMdd");
// 取得內容
DataTable tempDt = null;
StringBuilder sb = new StringBuilder();
using (SqlConnection conn = MvDbConnector.getErpDbConnection(MvCompanySite.MACHVISION))
{
conn.Open();
// 取得excel 內容
sb.Clear();
sb.Append(" SELECT C.TC004 AS TA004, A.MA003, A.MA027, B.MO006, A.MA028, CAST(Sum(D.TD015) AS numeric(21,0)) AS TA037, C.TC023 AS TA036 ")
.Append(" FROM PURMA A LEFT JOIN DSCSYS.dbo.CMSMO B ON A.MA027=B.MO001, ACPTC C, ACPTD D ")
.Append("WHERE C.TC001=D.TD001 ")
.Append(" AND C.TC002=D.TD002 ")
.Append(" AND C.TC004=A.MA001 ")
.Append(string.Format("AND C.TC003='{0}' ", deEnd.DateTime.ToString("yyyyMMdd")))
.Append(" AND D.TD004=-1 ")
.Append(" AND C.TC008='Y' ")
.Append(" AND C.TC009='N' ")
.Append(" AND (TD008 LIKE '1103-0620%') ")
.Append("GROUP BY C.TC004,A.MA003, A.MA027, B.MO006, A.MA028, D.TD008, C.TC023 ")
.AppendLine("ORDER BY C.TC004");
tempDt = MvDbConnector.queryDataBySql(conn, sb.ToString());
}
// 重新調整TA036內容
string matchHead = "008"; // 華南銀行代碼為008開頭
string bankId = string.Empty;
long amount = 0;
if (tempDt != null && tempDt.Rows.Count > 0)
{
long div = 0;
foreach (DataRow dr in tempDt.Rows)
{
bankId = dr["MA027"].ToString();
amount = Convert.ToInt64(dr["TA037"]);
if (bankId.StartsWith(matchHead) == true)
{
dr["TA036"] = 0;
}
else
{
div = amount / 1000000;
dr["TA036"] = (div >= 2) ? (div + 1) * 5 : 10;
}
}
}
// 不開放編輯功能, 或隱藏欄位等
treeList1.DataSource = tempDt;
treeList1.OptionsView.AutoWidth = false;
treeList1.OptionsBehavior.ReadOnly = true;
treeList1.OptionsBehavior.Editable = false;
setColumnsCaption(ref treeList1);
// 命名每個Pages
xtraTabControl1.TabPages[0].Text = "AcpP01";
xtraTabControl1.SelectedTabPage = xtraTabControl1.TabPages[0];
treeList1.BestFitColumns();
}
private void sbCreateNote_Click(object sender, EventArgs e)
{
if (cachePrDt == null)
{
MessageBox.Show("請先確認已有選取正確的請購單匯入檔案");
return;
}
string result = cboErpNotePr.SelectedValue == null ? "" : cboErpNotePr.SelectedValue.ToString();
if (result.Length == 0)
{
MessageBox.Show("請先選取請購單別");
return;
}
// 轉換單別
ErpNoteHead erpNoteHead = ErpNoteHead.None;
erpNoteHead = MvErpNoteBo.getEprNoteEnum("H_" + result);
if (erpNoteHead == ErpNoteHead.None)
{
MessageBox.Show("不支援開立此單別, 請找MIS人員提供規格並建置");
return;
}
DataTable tempDt = null;
// 暫時, debug用
//GlobalMvVariable.MvAdUserName = "******";
// 暫時, debug用
string erpUserGroup = string.Empty;
DataSet tempDs = null;
// 匯入PR
// 測試用
GlobalMvVariable.MvAdUserName = "******";
string noteNumber = string.Empty;
using (SqlConnection conn = MvDbConnector.Connection_ERPDB2_Dot_MVTEST)
{
conn.Open();
noteNumber = MvErpNoteBo.getMaxErpNote(conn, erpNoteHead);
}
// 測試用
SplashScreenManager.ShowDefaultWaitForm();
using (SqlConnection conn = MvDbConnector.getErpDbConnection(GlobalMvVariable.MvAdCompany))
{
conn.Open();
// 取得ad帳號及user group
// 這裡取的其實是deptId, 但因為William哥的VB程式匯入已經都是匯入deptId了
// 就先依照舊規則匯入, 之後如果要再調整, 再改取別的欄位即可
string tempSql = string.Format("SELECT A.MF004,A.MF005,B.MG006,B.MG004 FROM ADMMF A, ADMMG B WHERE A.MF001=B.MG001 AND MF001='{0}' AND B.MG002='PURA01K' ORDER BY MG002 ", GlobalMvVariable.MvAdUserName);
tempDt = MvDbConnector.queryDataBySql(conn, tempSql);
if (tempDt == null || tempDt.Rows.Count == 0)
{
MessageBox.Show(string.Format("你沒有操作開立PR的權限{0}UserName : {1}", Environment.NewLine, GlobalMvVariable.MvAdUserName));
return;
}
erpUserGroup = tempDt.Rows[0]["MF004"].ToString();
//tempDs = MvErpNoteBo.createErpNote_PR(conn, erpNoteHead, cachePrDt, DateTime.Today, GlobalMvVariable.MvAdUserName, erpUserGroup);
// 測試用
tempDs = MvErpNoteBo.createErpNote_PR(GlobalMvVariable.MvAdCompany, conn, erpNoteHead, cachePrDt, DateTime.Today, GlobalMvVariable.MvAdUserName, erpUserGroup, noteNumber);
// 測試用
}
// 取出來比對內容
if (tempDs.Tables.Count < 2)
{
MessageBox.Show("產生PR Fail, 請重新操作");
clearAllCacheData();
return;
}
// 確認沒有問題, 再重新至DB撈出來並顯示在Grid上, 確保資料已進DB
DataRow tempDr = tempDs.Tables["PURTA"].Rows[0];
textBox1.Text = tempDr["TA001"].ToString();
textBox2.Text = tempDr["TA002"].ToString();
textBox3.Text = tempDr["TA006"].ToString();
textBox4.Text = tempDr["TA004"].ToString();
textBox5.Text = tempDr["TA012"].ToString();
textBox6.Text = tempDr["TA013"].ToString();
textBox7.Text = tempDr["TA200"].ToString();
// 不開放編輯功能, 或隱藏欄位等
treeList2.DataSource = tempDs.Tables["PURTA"];
hidePurtaColumns(ref treeList2);
treeList2.OptionsView.AutoWidth = false;
treeList2.OptionsBehavior.ReadOnly = true;
treeList2.OptionsBehavior.Editable = false;
treeList3.DataSource = tempDs.Tables["PURTB"];
hidePurtbColumns(ref treeList3);
treeList3.OptionsView.AutoWidth = false;
treeList3.OptionsBehavior.ReadOnly = true;
treeList3.OptionsBehavior.Editable = false;
// 命名每個Pages
xtraTabControl1.TabPages[1].Text = "Imported PR";
xtraTabControl1.SelectedTabPage = xtraTabControl1.TabPages[1];
treeList2.BestFitColumns();
treeList3.BestFitColumns();
SplashScreenManager.CloseForm(false);
}
