private SearchSerialInputProperty GetDataSearch()
{
SearchSerialInputProperty _result = new SearchSerialInputProperty();
_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 Purchase.
_result.PURCHASE = this.cmbPurchase.Items.Contains(this.cmbPurchase.Text.Trim()) ? this.cmbPurchase.Text.Trim() : "";
if (_result.PURCHASE == "")
{
this.cmbPurchase.Text = "";
}
//## 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 = this.txtFFTOrder.Text.Trim() != "" ? _result.GROUP : "";
//## Get INUSE
_result.INUSE = this.chkUse.Checked && !this.chkCancel.Checked ? "1" : "0";
_result.INUSE = this.chkUse.Checked && this.chkCancel.Checked ? "" : _result.INUSE;
_result.INUSE = !this.chkUse.Checked && !this.chkCancel.Checked ? "" : _result.INUSE;
//## Get INPUT_DATE
string dateInput = this.dtpInputDate_Start.Value.ToString("yyyy-MM-dd");
_result.INPUT_DATE = this.dtpInputDate_Start.Checked ? dateInput : "";
//## Get INPUT_DATE_TO
string dateInputTo = this.dtpInputDate_End.Value.ToString("yyyy-MM-dd");
_result.INPUT_DATE_TO = this.dtpInputDate_End.Checked ? dateInputTo : "";
_result.INPUT_DATE_TO = this.dtpInputDate_Start.Checked ? _result.INPUT_DATE_TO : "";
return(_result);
}
public DataTable SearchSerialInput(SearchSerialInputProperty dataItem)
{
DataTable _result = new DataTable();
try
{
_resultData = _models.SearchSerialInput(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 SearchSerialInput(SearchSerialInputProperty dataItem)
{
string select = @"SELECT `tb_2`.`SERIAL` AS `SERIAL`
,IF(`tb_1`.INUSE = '1','Use','Cancel') AS `STATUS`
,`tb_1`.`DATE` AS `INPUT_DATE`
,`tb_1`.`FFT_ORDER_ID` AS `FFT_ORDER_ID`
,`tb_1`.`GROUP` AS `GROUP`
,`tb_10`.`INPUT_TYPE` AS `INPUT_TYPE`
,`tb_3`.`PURCHASE_NO` AS `PURCHASE_NO`
,`tb_4`.`FLOW_NAME` AS `FLOW_NAME`
,`tb_1`.`EMP_ID` AS `EMP_ID`
, tb11.PRODUCT_SUB_NAME AS SUB_PRODUCT
,`tb_7`.`PROCESS_NAME` AS `CURRENT_PROCESS`
,`tb_8`.`RESULT` AS `STATUS_IN_PROCESS`
FROM `input_serial` AS `tb_1`
INNER JOIN `serial` AS `tb_2`
ON (`tb_2`.ID = `tb_1`.SERIAL_ID)
INNER JOIN `purchase` AS `tb_3`
ON (`tb_3`.ID = `tb_1`.PURCHASE_ID)
INNER JOIN `flow` AS `tb_4`
ON (`tb_4`.`ID` = `tb_1`.`FLOW_ID`)
LEFT JOIN (SELECT `SERIAL_ID`
,`FFT_ORDER_ID`
,`GROUP`
,`FLOW_ID`
,MAX(`NO`) AS `MAX_NO`
,`PROCESS_ID`
FROM `progress`
GROUP BY `SERIAL_ID`,`FFT_ORDER_ID`,`GROUP`) AS `tb_5`
ON (`tb_5`.`SERIAL_ID` = `tb_1`.`SERIAL_ID`
AND `tb_5`.`FFT_ORDER_ID` = `tb_1`.`FFT_ORDER_ID`
AND `tb_5`.`GROUP` = `tb_1`.`GROUP`
AND `tb_5`.`FLOW_ID` = `tb_1`.`FLOW_ID`)
LEFT JOIN `progress` AS `tb_6`
ON (`tb_6`.`SERIAL_ID` = `tb_5`.`SERIAL_ID`
AND `tb_6`.`NO` = `tb_5`.`MAX_NO`)
LEFT JOIN `process` AS `tb_7`
ON (`tb_7`.`ID` = `tb_6`.`PROCESS_ID`)
LEFT JOIN `result` AS `tb_8`
ON(`tb_8`.`ID` = `tb_6`.`RESULT_ID`)
INNER JOIN `input` AS `tb_9`
ON(`tb_9`.`FFT_ORDER_ID` = `tb_1`.`FFT_ORDER_ID` AND `tb_9`.`GROUP` = `tb_1`.`GROUP`)
INNER JOIN `input_type` AS `tb_10`
ON(`tb_10`.`ID` = `tb_9`.`INPUT_TYPE_ID`)
INNER JOIN `product_sub` AS tb11
ON(tb11.PRODUCT_SUB_CODE = tb_9.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_2`.`SERIAL` IN (" + whereSerial + @") ";
where = whereSerial;
}
//## WHERE Purchase.
if (dataItem.PURCHASE != "")
{
string wherePurchase = "";
if (where != "")
{
wherePurchase = @"
AND `tb_3`.`PURCHASE_NO` = '" + dataItem.PURCHASE + @"' ";
where += wherePurchase;
}
else
{
wherePurchase = @"`tb_3`.`PURCHASE_NO` = '" + dataItem.PURCHASE + @"' ";
where = wherePurchase;
}
}
//## 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 SUB_PRODUCT
//if (dataItem.PRODUCT_SUB_NAME != "")
//{
// string whereSubProduct = "";
// if (where != "")
// {
// whereSubProduct = @"
// AND `tb11`.`SUB_PRODUCT` = '" + dataItem.PRODUCT_SUB_NAME + @"' ";
// where += whereSubProduct;
// }
// else
// {
// whereSubProduct = @"`tb11`.`SUB_PRODUCT` = '" + dataItem.PRODUCT_SUB_NAME + @"' ";
// where = whereSubProduct;
// }
//}
//## WHERE INUSE.
if (dataItem.INUSE != "")
{
string whereINUSE = "";
if (where != "")
{
whereINUSE = @"
AND `tb_1`.`INUSE` = '" + dataItem.INUSE + @"' ";
where += whereINUSE;
}
else
{
whereINUSE = @"`tb_1`.`INUSE` = '" + dataItem.INUSE + @"' ";
where = whereINUSE;
}
}
//## WHERE DATE/TO
if (dataItem.INPUT_DATE != "" && dataItem.INPUT_DATE_TO != "")
{
string whereInputDate = "";
if (where != "")
{
whereInputDate = @"
AND `tb_1`.`DATE` >= '" + dataItem.INPUT_DATE + @"'
AND `tb_1`.`DATE` <= '" + dataItem.INPUT_DATE_TO + "' ";
where += whereInputDate;
}
else
{
whereInputDate = @"`tb_1`.`DATE` >= '" + dataItem.INPUT_DATE + @"'
AND `tb_1`.`DATE` <= '" + dataItem.INPUT_DATE_TO + "' ";
where = whereInputDate;
}
}
//## WHERE DATE
else if (dataItem.INPUT_DATE != "" && dataItem.INPUT_DATE_TO == "")
{
string whereInputDate = "";
if (where != "")
{
whereInputDate = @"
AND `tb_1`.`DATE` = '" + dataItem.INPUT_DATE + @"' ";
where += whereInputDate;
}
else
{
whereInputDate = @"`tb_1`.`DATE` = '" + dataItem.INPUT_DATE + @"' ";
where = whereInputDate;
}
}
if (where == "")
{
sql = select + @"
WHERE SUBSTR(`tb_1`.`DATE`,1,4) = SUBSTR(NOW(),1,4)
ORDER BY
`tb_2`.`SERIAL`
,`tb_1`.`DATE`
,`tb_1`.`GROUP`;";
}
else
{
sql = select + @"
WHERE " + where + @"
ORDER BY
`tb_2`.`SERIAL`
,`tb_1`.`DATE`
,`tb_1`.`GROUP`;";
}
return(sql);
}
private void SearchSerialInput()
{
_dataSearch = GetDataSearch();
this.bindingSourceSerialInput.DataSource = _controllers.SearchSerialInput(_dataSearch);
this.advgListSerialInput.DataSource = this.bindingSourceSerialInput;
}
public OutputOnDbProperty SearchSerialInput(SearchSerialInputProperty dataItem)
{
_resultData = _services.SearchSerialInput(dataItem);
return(_resultData);
}
public OutputOnDbProperty SearchSerialInput(SearchSerialInputProperty dataItem)
{
sql = _sqlFactory.SearchSerialInput(dataItem);
resultData = base.SearchBySql(sql);
return(resultData);
}
