private void btnSearch_Click(object sender, EventArgs e)
{
search = new SearchOrderDetailProperty
{
FFT_ORDER_ID = new List <string>(),
CUSTOMER_NAME = (this.txtCustomer.Text != "") ? this.txtCustomer.Text : "",
PRODUCT_SUB_NAME = (this.txtProductSub.Text != "") ? this.txtProductSub.Text : "",
INPUT_DATE = (this.dtpDate_Start.Checked) ? Convert.ToString(dtpDate_Start.Value.ToString("yyyy-MM-dd")) : "",
INPUT_DATE_TO = (this.dtpDate_End.Checked) ? Convert.ToString(dtpDate_End.Value.ToString("yyyy-MM-dd")) : "",
THIS_YEAR = this.chkThisYear.Checked
};
foreach (string _doc in rtxtFFTOrder.Lines)
{
//check dupiclate
if (!search.FFT_ORDER_ID.Contains(_doc))
{
search.FFT_ORDER_ID.Add(_doc.Trim());
}
}
this.bindingSourceData.DataSource = _controllers.SearchOrderDetail(search);
this.advgDataProcess.DataSource = this.bindingSourceData;
}
public DataTable SearchOrderDetail(SearchOrderDetailProperty dataItem)
{
DataTable _result = new DataTable();
try
{
_resultData = _models.SearchOrderDetail(dataItem);
if (_resultData.StatusOnDb == true)
{
_result = _resultData.ResultOnDb;
}
else
{
MessageBox.Show(_resultData.MessageOnDb, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return(_result);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(_result);
}
}
public string SearchOrderDetail(SearchOrderDetailProperty dataItem)
{
sql = @"SELECT tb2.INPUT_TYPE
,tb7.PRODUCT_SUB_NAME
,tb1.FFT_ORDER_ID
,tb1.`GROUP`
,tb4.CUSTOMER_NAME
,tb5.WORK_ORDER
,tb6.PART_NO
,tb1.EMP_ID
,DATE_FORMAT(tb1.INPUT_DATE, '%Y-%m-%d') AS `INPUT_DATE`
FROM `input` AS tb1
INNER JOIN input_type AS tb2
ON (tb2.ID = tb1.INPUT_TYPE_ID)
INNER JOIN `condition` AS tb3
ON (tb3.ID = tb1.CONDITION_ID)
INNER JOIN `customer` AS tb4
ON (tb4.ID = tb3.CUSTOMER_ID)
INNER JOIN work_order AS tb5
ON (tb5.ID = tb3.WORK_ORDER_ID)
INNER JOIN part_no AS tb6
ON (tb6.ID = tb3.PART_NO_ID)
INNER JOIN product_sub AS tb7
ON (tb7.PRODUCT_SUB_CODE = tb1.PRODUCT_SUB_CODE)
";
string where = "";
string whereOrder = "";
foreach (string order in dataItem.FFT_ORDER_ID)
{
if (order.Trim() != "")
{
if (whereOrder == "")
{
whereOrder = "'" + order + "'";
}
else
{
whereOrder += ",'" + order + "'";
}
}
}
if (whereOrder != "")
{
where = "WHERE tb1.FFT_ORDER_ID IN (" + whereOrder + ")";
}
if (dataItem.PRODUCT_SUB_NAME != "")
{
if (where == "")
{
where = "WHERE tb7.PRODUCT_SUB_NAME = 'dataItem.PRODUCT_SUB_NAME'".Replace("dataItem.PRODUCT_SUB_NAME", dataItem.PRODUCT_SUB_NAME);
}
else
{
where += " AND tb7.PRODUCT_SUB_NAME = 'dataItem.PRODUCT_SUB_NAME'".Replace("dataItem.PRODUCT_SUB_NAME", dataItem.PRODUCT_SUB_NAME);
}
}
if (dataItem.CUSTOMER_NAME != "")
{
if (where == "")
{
where = "WHERE tb4.CUSTOMER_NAME = 'dataItem.CUSTOMER_NAME'".Replace("dataItem.CUSTOMER_NAME", dataItem.CUSTOMER_NAME);
}
else
{
where += " AND tb4.CUSTOMER_NAME = 'dataItem.CUSTOMER_NAME'".Replace(" dataItem.CUSTOMER_NAME", dataItem.CUSTOMER_NAME);
}
}
//## WHERE DATE/TO
if (dataItem.INPUT_DATE != "" && dataItem.INPUT_DATE_TO != "")
{
string whereInputDate = "";
if (where != "")
{
whereInputDate = @"AND tb1.INPUT_DATE >= '" + dataItem.INPUT_DATE + @"'
AND tb1.INPUT_DATE <= '" + dataItem.INPUT_DATE_TO + "%" + "' ";
where += whereInputDate;
}
else
{
whereInputDate = @" WHERE tb1.INPUT_DATE >= '" + dataItem.INPUT_DATE + @"'
AND tb1.INPUT_DATE <= '" + dataItem.INPUT_DATE_TO + "%" + "'";
where = whereInputDate;
}
}
//## WHERE DATE
else if (dataItem.INPUT_DATE != "" && dataItem.INPUT_DATE_TO == "")
{
if (where != "")
{
where += @" AND tb1.INPUT_DATE LIKE '" + dataItem.INPUT_DATE + "%" + @"' ";
}
else
{
where = @" WHERE tb1.INPUT_DATE LIKE '" + dataItem.INPUT_DATE + "%" + @"' ";
}
}
if (dataItem.THIS_YEAR)
{
if (where == "")
{
where += @"WHERE YEAR(tb1.INPUT_DATE) = YEAR(NOW())";
}
else
{
where += @" AND YEAR(tb1.INPUT_DATE) = YEAR(NOW())";
}
}
sql += where;
return(sql);
}
public OutputOnDbProperty SearchOrderDetail(SearchOrderDetailProperty dataItem)
{
_resultData = _services.SearchOrderDetail(dataItem);
return(_resultData);
}
public OutputOnDbProperty SearchOrderDetail(SearchOrderDetailProperty dataItem)
{
sql = _sqlFactory.SearchOrderDetail(dataItem);
resultData = base.SearchBySql(sql);
return(resultData);
}
