//Update database set status to 1, for Syukahyo public bool UpdateParameter(DataTable ds, DataGridView gr) { DEV10G2U dev = new DEV10G2U(); StringBuilder sql = new StringBuilder(); DataTable tb = ds; foreach (DataRow rows in tb.Rows) { sql.Append("UPDATE T_KDHSINFO SET "); sql.Append(" STATUS = 2,"); sql.Append(" LUDATE = to_date('" + DateTime.Now); sql.Append("' , 'yyyy-mm-dd hh24:mi:ss'), LUWSID = '" + Environment.MachineName); sql.Append("', LUUSERID = '" + Environment.UserName + "'"); sql.Append(" WHERE "); string denpyonoSql = "T_KDHSINFO.DENPYONO IN( "; string sykFileNMSql = "AND T_KDHSINFO.SYKFILENM IN ( "; string seqNoSql = "AND T_KDHSINFO.SEQNO IN ( "; for (int i = 0; i < gr.RowCount; i++) { if ((bool)gr.Rows[i].Cells[0].EditedFormattedValue == true) { denpyonoSql += "'" + gr.Rows[i].Cells[5].Value + "'" + ","; sykFileNMSql += "'" + gr.Rows[i].Cells["SYKFILENM"].Value + "'" + ","; seqNoSql += gr.Rows[i].Cells["SEQNO"].Value + ","; } } denpyonoSql = denpyonoSql.Remove(denpyonoSql.Length - 1); sql.Append(denpyonoSql + ") "); sykFileNMSql = sykFileNMSql.Remove(sykFileNMSql.Length - 1); sql.Append(sykFileNMSql + ") "); seqNoSql = seqNoSql.Remove(seqNoSql.Length - 1); sql.Append(seqNoSql + ") "); var xml = XDocument.Load(@"..\Mitsui.xml"); var queryC = xml.Descendants("sokocd") .Attributes("val") .Select(element => element.Value).ToList(); var SOKOCD = queryC[0].ToString(); sql.Append(" AND T_KDHSINFO.SOKOCD =" + "'" + SOKOCD + "'"); sql.Append(" AND T_KDHSINFO.STATUS = 1"); return(dev.executeUpdateQuery(sql.ToString())); } return(true); }
//Update database set status to 5 public void UpdateUnchin(List <string[]> vs) { DEV10G2U dev = new DEV10G2U(); foreach (string[] row in vs) { StringBuilder sql = new StringBuilder(); sql.Append("UPDATE T_KDHSINFO SET "); sql.Append(" STATUS = 5,"); sql.Append(" LUDATE = to_date('" + DateTime.Now); sql.Append("' , 'yyyy-mm-dd hh24:mi:ss'), LUWSID = '" + Environment.MachineName); sql.Append("', LUUSERID = '" + Environment.UserName); sql.Append("' WHERE "); sql.Append(" SOKOCD = '" + row[0] + "' AND"); sql.Append(" SYKFILENM = '" + row[1] + "' AND"); sql.Append(" SEQNO = '" + row[2] + "' AND"); sql.Append(" DENPYONO = '" + row[3] + "'"); dev.executeUpdateQuery(sql.ToString()); } }
//Update to database public void UpdateByDatable(string path) { bool ErrFlg = false; DataTable dt = ReadExcel(path); DEV10G2U dev = new DEV10G2U(); var xml = XDocument.Load(@"..\Mitsui.xml"); var queryC = xml.Root.Descendants("rndflg") .Elements("col") .Select(a => a.Value); var queryS = xml.Root.Descendants("rndflg") .Elements("set") .Select(a => a.Value); string SOKOCD = Tools.getSokocd(); List <string> cols = new List <string>(); List <string> sets = new List <string>(); foreach (var element in queryC) { cols = element.Split(',').ToList(); } foreach (var element in queryS) { sets = element.Split(',').ToList(); } foreach (DataRow rw in dt.Rows) { StringBuilder sql = new StringBuilder(); string ZNK = rw[((int)'J' % 32) - 1].ToString(); string HAISO = rw[((int)'I' % 32) - 1].ToString(); if (ZNK == "Y" && HAISO != "") { ErrFlg = true; continue; } sql.Append("UPDATE T_KDHSINFO SET "); for (int i = 0; i < cols.Count; i++) { var col = cols[i]; var set = sets[i]; var colVal = rw[((int)char.Parse(col) % 32) - 1]; sql.Append(set + " = '" + colVal + "',"); } sql.Append(" STATUS = 1,"); sql.Append(" LUDATE = to_date('" + DateTime.Now); sql.Append("' , 'yyyy-mm-dd hh24:mi:ss'), LUWSID = '" + Environment.MachineName); sql.Append("', LUUSERID = '" + Environment.UserName); var SYKFILENM = rw[((int)'S' % 32) - 1]; var SEQNO = rw[((int)'T' % 32) - 1]; var DENPYONO = rw[((int)'O' % 32) - 1]; // sql.Append("' WHERE "); sql.Append(" SOKOCD = '" + SOKOCD + "' AND"); sql.Append(" SYKFILENM = '" + SYKFILENM + "' AND"); sql.Append(" SEQNO = '" + SEQNO + "' AND"); sql.Append(" DENPYONO = '" + DENPYONO + "'"); //sql.Append(" STATUS = '1'"); // dev.executeUpdateQuery(sql.ToString()); } if (ErrFlg) { MessageBox.Show("残貨ありが設定されているが配送便Noが設定されている行があります"); } }