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