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); }