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