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