private void btnSearch_Click(object sender, EventArgs e) { SearchPurchaseProperty search = new SearchPurchaseProperty { Document = new List <string>(), FFT_CODE = this.txtFFTCode.Text, PART_NO = this.txtPartNo.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 rtxtDocumentNo.Lines) { //check dupiclate if (!search.Document.Contains(_doc)) { search.Document.Add(_doc.Trim()); } } this.bindingSourceData.DataSource = searchController.SearchPurchase(search); this.advgDataProcess.DataSource = this.bindingSourceData; }
public DataTable SearchPurchase(SearchPurchaseProperty dataItem) { DataTable _result = new DataTable(); try { _resultData = _models.SearchPurchase(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 SearchPurchase(SearchPurchaseProperty dataItem) { sql = @"SELECT `tb_1`.`PURCHASE_NO` AS `DOCUMENT_NO` ,IF(`tb_6`.`FFT_CODE` IS NULL OR `tb_6`.`FFT_CODE` = '', 'N/A',`tb_6`.`FFT_CODE`) AS `FFT_CODE` ,`tb_2`.`PART_NO` AS `PART_NO` ,`tb_3`.`PRODUCT_NAME` AS `PRODUCT_NAME` ,`tb_4`.`MODEL_NO` AS `MODEL_NO` ,DATE_FORMAT(`tb_1`.`DATE`, '%Y-%m-%d') AS `CREATE_DATE` ,`tb_1`.`EMP_ID` AS `EMP_CODE` ,IF(`tb_9`.`PATH_PROCESS_CARD` IS NULL OR `tb_9`.`PATH_PROCESS_CARD` = '', 'N/A',`tb_9`.`PATH_PROCESS_CARD`) AS `PATH_PROCESS_CARD` FROM `purchase` AS `tb_1` INNER JOIN `part_no` AS `tb_2` ON(`tb_2`.`ID` = `tb_1`.`PART_NO_ID`) INNER JOIN `product_purchase` AS `tb_3` ON(`tb_3`.`ID` = `tb_1`.`PURCHASE_PRODUCT_ID`) INNER JOIN `model` AS `tb_4` ON(`tb_4`.`ID` = `tb_1`.`MODEL_ID`) LEFT JOIN `purchase_fft_code` AS `tb_5` ON(`tb_5`.`PURCHASE_ID` = `tb_1`.`ID`) LEFT JOIN `fft_code` AS `tb_6` ON(`tb_6`.`ID` = `tb_5`.`FFT_CODE_ID`) LEFT JOIN `flow_process` AS `tb_7` ON (`tb_7`.PURCHASE_ID = `tb_1`.ID AND `tb_7`.INUSE = 1) LEFT JOIN `flow` AS `tb_8` ON (`tb_8`.ID = `tb_7`.FLOW_ID) LEFT JOIN `path_processcard` AS `tb_9` ON (`tb_9`.`PURCHASE_ID` = `tb_1`.`ID`) "; string where = ""; string whereDocument = ""; foreach (string doc in dataItem.Document) { if (doc.Trim() != "") { if (whereDocument == "") { whereDocument = "'" + doc + "'"; } else { whereDocument += ",'" + doc + "'"; } } } if (whereDocument != "") { where = "WHERE tb_1.PURCHASE_NO IN (" + whereDocument + ")"; } //FFT_CODE if (dataItem.FFT_CODE != "") { if (where == "") { where = "WHERE tb_6.FFT_CODE = 'dataItem.FFT_CODE'".Replace("dataItem.FFT_CODE", dataItem.FFT_CODE); } else { where += " AND tb_6.FFT_CODE = 'dataItem.FFT_CODE'".Replace("dataItem.FFT_CODE", dataItem.FFT_CODE); } } if (dataItem.PART_NO != "") { if (where == "") { where = "WHERE tb_2.PART_NO = 'dataItem.PART_NO'".Replace("dataItem.PART_NO", dataItem.PART_NO); } else { where += " AND tb_2.PART_NO = 'dataItem.PART_NO'".Replace(" dataItem.PART_NO", dataItem.PART_NO); } } //## 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 = @" WHERE 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 == "") { if (where != "") { where += @" AND tb_1.DATE LIKE '" + dataItem.INPUT_DATE + "%" + @"' "; } else { where = @" WHERE tb_1.DATE LIKE '" + dataItem.INPUT_DATE + "%" + @"' "; } } if (dataItem.THIS_YEAR) { if (where == "") { where += @"WHERE YEAR(tb_1.DATE) = YEAR(NOW())"; } else { where += @" AND YEAR(tb_1.DATE) = YEAR(NOW())"; } } sql += where; return(sql); }
public OutputOnDbProperty SearchPurchase(SearchPurchaseProperty dataItem) { _resultData = _services.SearchPurchase(dataItem); return(_resultData); }
public OutputOnDbProperty SearchPurchase(SearchPurchaseProperty dataItem) { sql = _sqlFactory.SearchPurchase(dataItem); resultData = base.SearchBySql(sql); return(resultData); }