//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が設定されている行があります");
}
}