public DataTable SearchSerialProgress(SearchSerialProgressProperty dataItem)
{
DataTable _result = new DataTable();
try
{
if (dataItem.SELECT_SHOW_CURRENT_PROCESS)
{
_resultData = _models.SearchCurrentProgress(dataItem);
}
else
{
_resultData = _models.SearchProgressAllProcess(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 SearchCurrentProgress(SearchSerialProgressProperty dataItem)
{
string select = @"SELECT `tb_3`.`SERIAL` AS `SERIAL`
,`tb_1`.`NO` AS `NO`
,`tb_1`.`FFT_ORDER_ID` AS `FFT_ORDER_ID`
,`tb_1`.`GROUP` AS `GROUP`
,`tb_9`.`INPUT_TYPE` AS `INPUT_TYPE`
,`tb_4`.`FLOW_NAME` AS `FLOW_NAME`
,IF(`tb_7`.`NO` IS NULL OR `tb_7`.`NO` = '','N/A',`tb_7`.`NO`) AS `PROCESS_NO`
,`tb_5`.`PROCESS_NAME` AS `PROCESS_NAME`
,tb10.PRODUCT_SUB_NAME AS `SUB_PRODUCT`
,`tb_6`.`RESULT` AS `RESULT`
,`tb_1`.`ID` AS `PROGRESS_ID`
,`tb_1`.`LAST_UPDATE` AS `LAST_UPDATE`
FROM `progress` AS `tb_1`
INNER JOIN (SELECT `SERIAL_ID`,MAX(`NO`) AS `MAX_NO`FROM `progress` GROUP BY `SERIAL_ID`) AS `tb_2`
ON (`tb_2`.`SERIAL_ID` = `tb_1`.`SERIAL_ID` AND `tb_2`.`MAX_NO` = `tb_1`.`NO`)
INNER JOIN `serial` AS `tb_3`
ON (`tb_3`.`ID` = `tb_1`.`SERIAL_ID`)
INNER JOIN `flow` AS `tb_4`
ON (`tb_4`.`ID` = `tb_1`.`FLOW_ID`)
INNER JOIN `process` AS `tb_5`
ON (`tb_5`.`ID` = `tb_1`.PROCESS_ID)
INNER JOIN `result` AS `tb_6`
ON (`tb_6`.`ID` = `tb_1`.`RESULT_ID`)
LEFT JOIN `process_flow` AS `tb_7`
ON (`tb_7`.`FLOW_ID` = `tb_1`.`FLOW_ID` AND `tb_7`.`PROCESS_ID` = `tb_1`.`PROCESS_ID`)
INNER JOIN `input` AS `tb_8`
ON (`tb_8`.`FFT_ORDER_ID` = `tb_1`.FFT_ORDER_ID AND `tb_8`.`GROUP` = `tb_1`.`GROUP`)
INNER JOIN `input_type` AS `tb_9`
ON (`tb_9`.`ID` = `tb_8`.`INPUT_TYPE_ID`)
INNER JOIN `product_sub` AS tb10
ON (tb10.PRODUCT_SUB_CODE = tb_8.PRODUCT_SUB_CODE)";
string where = "";
//## WHERE Serial.
string whereSerial = "";
foreach (string _serial in dataItem.SERIAL)
{
if (_serial.Trim() != "")
{
if (whereSerial == "")
{
whereSerial = "'" + _serial + "'";
}
else
{
whereSerial += ",'" + _serial + "'";
}
}
}
if (whereSerial != "")
{
whereSerial = @"`tb_3`.`SERIAL` IN (" + whereSerial + @") ";
where = whereSerial;
}
//## WHERE Process.
if (dataItem.PROCESS.PROCESS_NAME != "")
{
string whereProcess = "";
if (where != "")
{
whereProcess = @"
AND `tb_5`.`PROCESS_NAME` = '" + dataItem.PROCESS.PROCESS_NAME + @"' ";
where += whereProcess;
}
else
{
whereProcess = @"`tb_5`.`PROCESS_NAME` = '" + dataItem.PROCESS.PROCESS_NAME + @"' ";
where = whereProcess;
}
}
////## WHERE SUB_PRODUCT.
//if (dataItem.PRODUCT_SUB_NAME != "")
//{
// string whereSubProduct = "";
// if (where != "")
// {
// whereSubProduct = @"
// AND `tb10`.`SUB_PRODUCT` = '" + dataItem.PRODUCT_SUB_NAME + @"' ";
// where += whereSubProduct;
// }
// else
// {
// whereSubProduct = @"`tb10`.`SUB_PRODUCT` = '" + dataItem.PRODUCT_SUB_NAME + @"' ";
// where = whereSubProduct;
// }
//}
//## WHERE Status(Result).
if (dataItem.STATUS.RESULT != "")
{
string whereStatus = "";
if (where != "")
{
whereStatus = @"
AND `tb_6`.`RESULT` = '" + dataItem.STATUS.RESULT + @"' ";
where += whereStatus;
}
else
{
whereStatus = @"`tb_6`.`RESULT` = '" + dataItem.STATUS.RESULT + @"' ";
where = whereStatus;
}
}
//## WHERE FFT_ORDER_ID/GROUP.
if (dataItem.FFT_ORDER_ID != "")
{
string whereFFT_Oder = "";
if (where != "")
{
whereFFT_Oder = @"
AND `tb_1`.`FFT_ORDER_ID` = '" + dataItem.FFT_ORDER_ID + @"' ";
where += whereFFT_Oder;
}
else
{
whereFFT_Oder = @"`tb_1`.`FFT_ORDER_ID` = '" + dataItem.FFT_ORDER_ID + @"' ";
where = whereFFT_Oder;
}
if (dataItem.GROUP != "")
{
where += @"
AND `tb_1`.`GROUP` = '" + dataItem.GROUP + "' ";
}
}
//## WHERE DATE/TO
if (dataItem.INPUT_DATE != "" && dataItem.INPUT_DATE_TO != "")
{
string whereInputDate = "";
if (where != "")
{
whereInputDate = @"
AND SUBSTR(`tb_1`.`ID`,1,6) >= DATE_FORMAT('" + dataItem.INPUT_DATE + @"','%y%m%d')
AND SUBSTR(`tb_1`.`ID`,1,6) <= DATE_FORMAT('" + dataItem.INPUT_DATE_TO + "','%y%m%d') ";
where += whereInputDate;
}
else
{
whereInputDate = @"SUBSTR(`tb_1`.`ID`,1,6) >= DATE_FORMAT('" + dataItem.INPUT_DATE + @"','%y%m%d')
AND SUBSTR(`tb_1`.`ID`,1,6) <= DATE_FORMAT('" + dataItem.INPUT_DATE_TO + "','%y%m%d') ";
where = whereInputDate;
}
}
//## WHERE DATE
else if (dataItem.INPUT_DATE != "" && dataItem.INPUT_DATE_TO == "")
{
string whereInputDate = "";
if (where != "")
{
whereInputDate = @"
AND SUBSTR(`tb_1`.`ID`,1,6) = DATE_FORMAT('" + dataItem.INPUT_DATE + @"','%y%m%d') ";
where += whereInputDate;
}
else
{
whereInputDate = @"SUBSTR(`tb_1`.`ID`,1,6) = DATE_FORMAT('" + dataItem.INPUT_DATE + @"','%y%m%d') ";
where = whereInputDate;
}
}
sql = where == "" ?
select + @"
WHERE SUBSTR(`tb_1`.`ID`,1,4) = DATE_FORMAT(NOW(), '%y%m')
GROUP BY
`tb_1`.`SERIAL_ID`
ORDER BY
`tb_1`.`SERIAL_ID` DESC
,`tb_1`.`NO`;" :
select + @"
WHERE " + where + @"
GROUP BY
`tb_1`.`SERIAL_ID`
ORDER BY
`tb_1`.`SERIAL_ID` DESC
,`tb_1`.`NO`;";
return(sql);
}
public OutputOnDbProperty SearchCurrentProgress(SearchSerialProgressProperty dataItem)
{
_resultData = _services.SearchCurrentProgress(dataItem);
return(_resultData);
}
public OutputOnDbProperty SearchCurrentProgress(SearchSerialProgressProperty dataItem)
{
sql = _sqlFactory.SearchCurrentProgress(dataItem);
resultData = base.SearchBySql(sql);
return(resultData);
}
private SearchSerialProgressProperty GetDataSearch()
{
SearchSerialProgressProperty _result = new SearchSerialProgressProperty();
_result.SERIAL = new List <string>();
//## Get Serial.
if (this.rtxtSerial.Text != "")
{
int countSerial = this.rtxtSerial.Lines.Count();
for (int i = 0; i < countSerial; i++)
{
string _serial = Convert.ToString(this.rtxtSerial.Lines[i]);
if (_serial != "")
{
if (!_result.SERIAL.Contains(_serial))
{
_result.SERIAL.Add(_serial.Trim());
}
}
}
}
//## Get Status
_result.STATUS = new ResultProperty {
RESULT = this.cmbStatus.Items.Contains(this.cmbStatus.Text.Trim()) ? this.cmbStatus.Text.Trim() : ""
};
if (_result.STATUS.RESULT == "")
{
this.cmbStatus.Text = "";
}
//## Get Process
_result.PROCESS = new ProcessProperty {
PROCESS_NAME = this.cmbProcess.Items.Contains(this.cmbProcess.Text.Trim()) ? this.cmbProcess.Text.Trim() : ""
};
if (_result.PROCESS.PROCESS_NAME == "")
{
this.cmbProcess.Text = "";
}
//## Get Type Of Show Progress.
_result.SELECT_SHOW_CURRENT_PROCESS = this.rdoCurrent.Checked;
////## Get FFT_ORDER_ID.
_result.FFT_ORDER_ID = this.txtFFTOrder.Text.Trim() != "" ? this.txtFFTOrder.Text.Trim() : "";
//## Get GROUP.
_result.GROUP = this.txtGroup.Text.Trim() != "" ? this.txtGroup.Text.Trim() : "";
_result.GROUP = _result.FFT_ORDER_ID != "" ? _result.GROUP : "";
//## Get INPUT_DATE
string dateInput = this.dtpDate_Start.Value.ToString("yyyy-MM-dd");
_result.INPUT_DATE = this.dtpDate_Start.Checked ? dateInput : "";
//## Get INPUT_DATE_TO
string dateInputTo = this.dtpDate_End.Value.ToString("yyyy-MM-dd");
_result.INPUT_DATE_TO = this.dtpDate_End.Checked ? dateInputTo : "";
_result.INPUT_DATE_TO = this.dtpDate_Start.Checked ? _result.INPUT_DATE_TO : "";
return(_result);
}
private void SearchSerialProgress()
{
_dataSearch = GetDataSearch();
this.bindingSourceSerialProgress.DataSource = _controllers.SearchSerialProgress(_dataSearch);
this.advgListSerialProgress.DataSource = this.bindingSourceSerialProgress;
}