public DataTable searchBy(DataGridView gr)
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT ");
sql.Append("t.DENPYONO, ");
sql.Append("t.SYUKABI, ");
sql.Append("t.NOUKIBI, ");
sql.Append("t.IRIGSYCD, ");
sql.Append("m.SOKONM, ");
sql.Append("t.NSNNM, ");
sql.Append("t.NUKNNM, ");
sql.Append("t.CHIKUCD, ");
sql.Append("t.POSTCD, ");
sql.Append("t.ADDRESS, ");
sql.Append("t.TELNO, ");
sql.Append("t.KOSU, ");
sql.Append("t.TANI, ");
sql.Append("t.WT, ");
sql.Append("t.SCNDHSTNNM, ");
sql.Append("t.OKURINO, ");
sql.Append("t.BIKO, ");
sql.Append("t.KURAGO ");
sql.Append("FROM ");
sql.Append("T_KDHSINFO t ");
sql.Append("INNER JOIN M_SOKO m ");
sql.Append("ON m.SOKOCD = t.IRIGSYCD ");
sql.Append(" WHERE ");
string denpyonoSql = "t.DENPYONO IN( ";
string sykFileNMSql = "AND t.SYKFILENM IN ( ";
string seqNoSql = "AND t.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 + ") ");
sql.Append(" AND t.SOKOCD = '");
var SOKOCD = Tools.getSokocd();
sql.Append(SOKOCD + "'");
sql.Append(" AND t.STATUS = 2");
DEV10G2U dev = new DEV10G2U();
return(dev.executeSelectQuery(sql.ToString()));
}
//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が設定されている行があります");
}
}
public void SearchParameter(DataGridView gr)
{
try
{
DEV10G2U dev = new DEV10G2U();
StringBuilder sql = new StringBuilder();
sql.Append("SELECT ");
sql.Append("T_KDHSINFO.AREACD, ");
sql.Append("T_KDHSINFO.IRIGSYCD, ");
sql.Append("T_KDHSINFO.KURAGO, ");
sql.Append("T_KDHSINFO.SYUKABI, ");
sql.Append("T_KDHSINFO.SYUKABI, ");
sql.Append("T_KDHSINFO.NOUKIBI, ");
sql.Append("T_UNCHIN.DHYDENPYONO, ");
sql.Append("T_KDHSINFO.NUKNNM, ");
sql.Append("T_KDHSINFO.ADDRESS, ");
sql.Append("T_UNCHIN.KOSU, ");
sql.Append("T_UNCHIN.WT, ");
//sql.Append("T_KDHSINFO.SOKOCD, ");
sql.Append("T_UNCHIN.SKYUNCHIN, ");
sql.Append("T_UNCHIN.TYUKEIRYO, ");
sql.Append("T_UNCHIN.SNTUNCHINCD1, ");
sql.Append("T_UNCHIN.SNTUNCHINGAK1, ");
sql.Append("T_UNCHIN.SNTUNCHINCD2, ");
sql.Append("T_UNCHIN.SNTUNCHINGAK2, ");
sql.Append("T_UNCHIN.SNTUNCHINCD3, ");
sql.Append("T_UNCHIN.SNTUNCHINGAK3 ");
sql.Append("FROM ");
sql.Append("T_KDHSINFO ");
sql.Append("INNER JOIN T_UNCHIN ");
sql.Append("ON T_UNCHIN.SOKOCD = T_KDHSINFO.SOKOCD ");
sql.Append("AND T_UNCHIN.DENPYONO = T_KDHSINFO.DENPYONO ");
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 SOKOCD = Tools.getSokocd();
sql.Append(" AND T_KDHSINFO.SOKOCD =" + "'" + SOKOCD + "'");
sql.Append(" AND T_KDHSINFO.ZNKFLG IS NULL ");
DataTable dataSet = dev.executeSelectQuery(sql.ToString());
UnchinDownLoad(dataSet, gr);
}
catch
{
throw;
}
}
public DataTable SearchParameter(bool MNR, bool SNK, bool TYU, bool CYU, bool APU, bool Status1, bool Status2, bool Status3, bool Status4, bool Status5, bool Status6, bool Status7,
string SyukaBi1, string SyukaBi2, string Exlsrd1, string Exlsrd2, string Unchin1, string Unchin2,
string OrderNo, string HaisoBnNO, bool Zanka, object Area, bool SyukaFlgL, bool SyukaFlgR, bool ExlFlgL, bool ExlFlgR, bool UnchinFlgL, bool UnchinFlgR)
{
try
{
DEV10G2U dev = new DEV10G2U();
StringBuilder sql = new StringBuilder();
sql.Append("SELECT ");
sql.Append("T_KDHSINFO.AREACD, ");
sql.Append("T_KDHSINFO.CHIKUCD, ");
sql.Append("T_KDHSINFO.NUKNNM, ");
sql.Append("T_KDHSINFO.SYUKABI, ");
sql.Append("T_KDHSINFO.NOUKIBI, ");
sql.Append("T_KDHSINFO.KOSU, ");
sql.Append("T_KDHSINFO.WT, ");
sql.Append("T_KDHSINFO.HAISONO, ");
sql.Append("T_KDHSINFO.ZNKFLG, ");
sql.Append("T_KDHSINFO.IRIGSYCD, ");
//sql.Append("M_SOKO.SOKONM, ");
sql.Append("T_KDHSINFO.KURAGO, ");
sql.Append("T_KDHSINFO.SKYHINSYUCD, ");
sql.Append("T_KDHSINFO.SKYHINSYUNM, ");
sql.Append("T_KDHSINFO.DENPYONO, ");
sql.Append("T_KDHSINFO.ADDRESS, ");
sql.Append("T_KDHSINFO.TELNO, ");
sql.Append("T_KDHSINFO.BIKO, ");
sql.Append("T_KDHSINFO.SYKFILENM, ");
sql.Append("T_KDHSINFO.SEQNO, ");
sql.Append("T_KDHSINFO.SOKOCD ");
sql.Append("FROM ");
sql.Append("T_KDHSINFO ");
//sql.Append("INNER JOIN M_SOKO ");
//sql.Append("ON M_SOKO.SOKOCD = T_KDHSINFO.IRIGSYCD ");
if (Area != null)
{
sql.Append(" ,(SELECT ");
sql.Append("AREANM , AREACD ");
sql.Append("FROM ");
sql.Append("M_AREA ");
sql.Append("GROUP BY AREANM, AREACD ");
sql.Append(")AREA ");
}
List <string> sqlWhere = new List <string>();
if (Area != null)
{
sqlWhere.Add(" AREA.AREACD = T_KDHSINFO.AREACD" + " AND AREA.AREANM= '" + Area.ToString() + "'");
}
var companySql = " CODE_SOKO.KBN1 IN( ";
bool companySelected = false;
if (MNR)
{
companySql += "1,";
companySelected = true;
}
if (SNK)
{
companySql += "2,";
companySelected = true;
}
if (TYU)
{
companySql += "3,";
companySelected = true;
}
if (CYU)
{
companySql += "4,";
companySelected = true;
}
if (APU)
{
companySql += "5,";
companySelected = true;
}
if (companySelected)
{
companySql = companySql.Remove(companySql.Length - 1);
sqlWhere.Add(companySql + ")");
}
//check if unchin has been sent
bool unchinFlg = false;
if (Status1)
{
sqlWhere.Add("STATUS=" + '0');
}
if (Status2)
{
sqlWhere.Add("STATUS=" + '1');
}
if (Status3)
{
sqlWhere.Add("STATUS=" + '2');
}
if (Status4)
{
sqlWhere.Add("STATUS=" + '3');
}
if (Status5)
{
sqlWhere.Add("STATUS=" + '5');
}
if (Status7)
{
unchinFlg = true;
sqlWhere.Add("STATUS=" + '4');
}
if (Status6)
{
unchinFlg = true;
}
if (SyukaBi1.Length > 0 && SyukaFlgL)
{
sqlWhere.Add("SYUKABI>= " + "'" + SyukaBi1.Substring(0, 10) + "'");
}
if (SyukaBi2.Length > 0 && SyukaFlgR)
{
sqlWhere.Add("SYUKABI <=" + "'" + SyukaBi2.Substring(0, 10) + "'");
}
if (Exlsrd1.Length > 0 && ExlFlgL)
{
sqlWhere.Add("RDDATE>= " + "'" + Exlsrd1.Substring(0, 10) + "'");
}
if (Exlsrd2.Length > 0 && ExlFlgR)
{
sqlWhere.Add("RDDATE<= " + "'" + Exlsrd2.Substring(0, 10) + "'");
}
if (unchinFlg)
{
if (Unchin1.Length > 0 && UnchinFlgL)
{
sqlWhere.Add("UNCHISNDDT>= " + "'" + Unchin1.Substring(0, 10) + "'");
}
if (Unchin2.Length > 0 && UnchinFlgR)
{
sqlWhere.Add("UNCHISNDDT < = " + "'" + Unchin2.Substring(0, 10) + "'");
}
}
if (OrderNo.Length > 0)
{
sqlWhere.Add("DENPYONO= '" + OrderNo + "'");
}
if (HaisoBnNO.Length > 0)
{
sqlWhere.Add("HAISONO= '" + HaisoBnNO + "'");
}
if (Zanka)
{
sqlWhere.Add("ZNKFLG= 'Y' ");
}
else
{
//sqlWhere.Add("ZNKFLG IS NULL ");
}
if (sqlWhere.Count > 0)
{
string count = string.Join(" AND ", sqlWhere.ToArray());
sql.Append(" WHERE " + count);
}
return(dev.executeSelectQuery(sql.ToString()));
}
catch
{
throw;
}
}
//Search database from the information from interface
public void SearchParameter(DataGridView gr)
{
try
{
DEV10G2U dev = new DEV10G2U();
StringBuilder sql = new StringBuilder();
sql.Append("SELECT ");
sql.Append("T_KDHSINFO.AREACD, ");
sql.Append("T_KDHSINFO.IRIGSYCD, ");
sql.Append("T_KDHSINFO.SYUKABI, ");
sql.Append("T_KDHSINFO.DENPYONO, ");
sql.Append("T_KDHSINFO.NOUKIBI, ");
sql.Append("T_KDHSINFO.NUKNNM, ");
sql.Append("T_KDHSINFO.CHIKUCD, ");
sql.Append("T_KDHSINFO.KOSU, ");
sql.Append("T_KDHSINFO.WT, ");
sql.Append("T_KDHSINFO.BIKO, ");
sql.Append("AREA.AREANM, ");
sql.Append("SOKO.SOKONM ");
sql.Append("FROM ");
sql.Append("T_KDHSINFO ");
sql.Append("INNER JOIN (SELECT DISTINCT AREACD, AREANM FROM M_AREA)AREA ");
sql.Append("ON AREA.AREACD = T_KDHSINFO.AREACD ");
sql.Append("INNER JOIN (SELECT DISTINCT SOKOCD, SOKONM FROM M_SOKO)SOKO ");
sql.Append("ON SOKO.SOKOCD = T_KDHSINFO.IRIGSYCD ");
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 SOKOCD = Tools.getSokocd();
sql.Append(" AND T_KDHSINFO.SOKOCD =" + "'" + SOKOCD + "'");
DataTable dataSet = dev.executeSelectQuery(sql.ToString());
UpdateParameter(dataSet, gr);
SyuKa(dataSet, SOKOCD);
}
catch
{
throw;
}
}
