public List <PurchaseProperty> UsedPurchase(ShipmentProperty dataItem)
{
List <PurchaseProperty> _result = new List <PurchaseProperty>();
try
{
_resultData = _models.UsedPurchase(dataItem);
if (_resultData.StatusOnDb == true)
{
if (_resultData.ResultOnDb.Rows.Count > 0)
{
for (int i = 0; i < _resultData.ResultOnDb.Rows.Count; i++)
{
PurchaseProperty _purchase = new PurchaseProperty();
_purchase.FLOW = new FlowProperty();
_purchase.PURCHASE_NO = _resultData.ResultOnDb.Rows[i]["PURCHASE_NO"].ToString();
_purchase.FLOW.FLOW_NAME = _resultData.ResultOnDb.Rows[i]["FLOW_NAME"].ToString();
_result.Add(_purchase);
}
}
}
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 InsertNewFlowProcess(PurchaseProperty dataItem)
{
sql = @"INSERT INTO `flow_process`
(`PURCHASE_ID`
, `FLOW_ID`
, `NO`
, `INUSE`
, `EMP_ID`
)
(
SELECT tb2.ID AS PURCEASE_ID
,'" + dataItem.FLOW.ID + @"' AS FLOW_ID
,MAX(tb1.`NO`)+1 AS `NO`
,'1' AS INUSE
,'" + dataItem.EMP.code + @"' AS EMP_ID
FROM `flow_process` AS tb1
INNER JOIN purchase AS tb2
ON (tb2.ID = tb1.PURCHASE_ID)
WHERE tb2.PURCHASE_NO = 'TEST_DOC'
);
";
return(sql);
}
public string InsertPurchase(PurchaseProperty dataItem)
{
sql = @"INSERT INTO `purchase` (
`ID`
,`PURCHASE_NO`
,`PART_NO_ID`
,`PURCHASE_PRODUCT_ID`
,`MODEL_ID`
,`DATE`
,`EMP_ID`
)
(
SELECT CASE WHEN COUNT(`ID`) = 0 THEN 1 ELSE MAX(`ID`)+1 END AS `ID`
,'" + dataItem.PURCHASE_NO + @"'
,(SELECT `ID` FROM `part_no` WHERE REPLACE(PART_NO, ' ', '') = REPLACE('" + dataItem.PART_NO + @"', ' ', '')) AS `PART_NO_ID`
,(SELECT `ID` FROM `product_purchase` WHERE REPLACE(PRODUCT_NAME, ' ', '') = REPLACE('" + dataItem.PRODUCT_PURCHASE + @"', ' ', '')) AS `PURCHASE_PRODUCT_ID`
,(SELECT `ID` FROM `model` WHERE REPLACE(MODEL_NO, ' ', '') = REPLACE('" + dataItem.MODEL + @"', ' ', '')) AS `MODEL_ID`
,NOW() AS `DATE`
,'" + dataItem.EMP.code + @"' AS `EMP_ID`
FROM `purchase`
); ";
return(sql);
}
private void cmbPurchase_SelectedIndexChanged(object sender, EventArgs e)
{
// ***** After Select Purchase *****
//Clear cmb Flow & list process
//Query by purchase , get flow name & process
//Load Flow name in cmb Flow & set cmb.text = Flow (1.2)
this.cmbFlow.Text = "";
this.cmbFlow.Items.Clear();
this.treeViewSetProcess.Nodes.Clear();
if (this.cmbPurchase.Text != "")
{
_purchase = new PurchaseProperty();
{
_purchase.PURCHASE_NO = this.cmbPurchase.Text.ToString();
}
_flowProcess = _flowControllers.SearchFlowNameByPurchase(_purchase);
if (_flowProcess.FLOW == null)
{
MessageBox.Show("Not found Process flow " + "\n" + "Please check name purchase", "Stop", MessageBoxButtons.OK, MessageBoxIcon.Stop);
return;
}
cmbFlow.Items.Add(_flowProcess.FLOW.FLOW_NAME);
cmbFlow.Text = _flowProcess.FLOW.FLOW_NAME;
}
createTree();
}
public pageUpdateProcessCard(frmMain obj, PurchaseProperty obj2)
{
InitializeComponent();
_frmMain = obj;
_updateOrder = obj2;
_updateOrderForCopy = obj2;
}
private void LoadPurchaseAll()
{
//this.lblPurchase.Text = defaultlblPurchase;
//this.lblShowFileName.Text = defaultlblPurchase;
//this.txtPathProcessCard.Clear();
//this.bindingSourcePurcAll.DataSource = _controllers.LoadPurchaseAll();
//this.advgListPurchase.DataSource = this.bindingSourcePurcAll;
//// Check and visible columns["ID"].
//var _columnVis_ID = advgListPurchase.Columns["ID"];
//if (_columnVis_ID != null && _columnVis_ID.Visible)
//{ advgListPurchase.Columns["ID"].Visible = false; }
this.lblPurchase.Text = _updateOrder.PURCHASE_NO;
this.lblFFTCode.Text = _updateOrder.FFT_CODE;
this.txtFlow.Text = _updateOrder.FLOW.FLOW_NAME;
this.lblPART_NO.Text = _updateOrder.PART_NO;
this.lblModelNO.Text = _updateOrder.MODEL;
this.lblProductName.Text = _updateOrder.PRODUCT_PURCHASE;
this.lblShowFileName.Text = _updateOrder.PATH_PROCESS_CARD == "N/A" ? defaultlblPurchase : Path.GetFileName(_updateOrder.PATH_PROCESS_CARD);
this.txtPathProcessCard.Text = _updateOrder.PATH_PROCESS_CARD;
//Set new Property
_updatePurchase = new PurchaseProperty();
}
private void cmbSelect_PurchaseNo_SelectedIndexChanged(object sender, EventArgs e)
{
cmbProcess.Items.Clear();
cmbProcess.Text = "";
cmbProcess.Items.Add("- Select Process -");
cmbProcess.SelectedItem = 0;
if (cmbSelect_PurchaseNo.SelectedIndex != 0)
{
cmbProcess.SelectedIndex = 0;
_purchase = new PurchaseProperty();
{
_purchase.PURCHASE_NO = this.cmbSelect_PurchaseNo.Text.ToString();
}
//_flowProcess = _controllerFlow.SearchFlowNameByPurchase(_purchase);
//txtFlowName.Text = _flowProcess.FLOW.FLOW_NAME;
//foreach (ProcessProperty _process in _flowProcess.PROCESS)
//{
// cmbProcess.Items.Add(_process.PROCESS_NAME);
//}
}
else
{
this.txtFlowName.Clear();
}
}
public string SearchFlowNameByPurchase(PurchaseProperty dataItem)
{
sql = @"SELECT tb_1.`PURCHASE_NO`
,tb_3.FLOW_NAME
,tb_4.PROCESS_ID
,tb_5.PROCESS_NAME
FROM `purchase` AS tb_1
INNER JOIN `flow_process` AS tb_2
ON (tb_2.PURCHASE_ID = tb_1.ID )
INNER JOIN `flow` AS tb_3
ON (tb_3.ID = tb_2.FLOW_ID)
INNER JOIN `process_flow` AS tb_4
ON(tb_4.FLOW_ID = tb_3.ID)
INNER JOIN `process` AS tb_5
ON( tb_5.ID = tb_4.PROCESS_ID)
WHERE REPLACE(tb_1.`PURCHASE_NO`, ' ', '') = REPLACE('" + dataItem.PURCHASE_NO + @"', ' ', '')
AND tb_2.INUSE = 1
AND tb_3.INUSE = 1
ORDER BY tb_4.`NO`";
return(sql);
}
private void advgListPurchase_CellClick(object sender, DataGridViewCellEventArgs e)
{
try
{
if (e.RowIndex >= 0 && e.ColumnIndex >= 0)
{
if (this.advgListPurchase.Rows[e.RowIndex].Cells[e.ColumnIndex].Value != null)
{
purchaseData = new PurchaseProperty
{
PURCHASE_NO = this.advgListPurchase.Rows[e.RowIndex].Cells["DOCUMENT_NO"].FormattedValue.ToString()
,
FFT_CODE = this.advgListPurchase.Rows[e.RowIndex].Cells["FFT_CODE"].FormattedValue.ToString()
,
FLOW = new FlowProperty {
FLOW_NAME = this.advgListPurchase.Rows[e.RowIndex].Cells["FLOW_NAME"].FormattedValue.ToString()
}
,
PART_NO = this.advgListPurchase.Rows[e.RowIndex].Cells["PART_NO"].FormattedValue.ToString()
,
PRODUCT_PURCHASE = this.advgListPurchase.Rows[e.RowIndex].Cells["PRODUCT_NAME"].FormattedValue.ToString()
,
MODEL = this.advgListPurchase.Rows[e.RowIndex].Cells["MODEL_NO"].FormattedValue.ToString()
,
PATH_PROCESS_CARD = this.advgListPurchase.Rows[e.RowIndex].Cells["PATH_PROCESS_CARD"].FormattedValue.ToString()
};
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Stop", MessageBoxButtons.OK, MessageBoxIcon.Stop);
}
}
private void btnUpdate_Click(object sender, EventArgs e)
{
if (cmbFlow.Text == "-Select Flow-")
{
MessageBox.Show("Please Select Flow", "Stop", MessageBoxButtons.OK, MessageBoxIcon.Stop);
return;
}
int index = _listflow.FindIndex(x => x.FLOW_NAME == this.txtFlow.Text);
_updateOrder.FLOW.FLOW_NAME = _listflow[index].FLOW_NAME;
_updateOrder.FLOW.ID = _listflow[index].ID;
_updateOrder.EMP = _frmMain._empLogin;
if (_updateOrder.PURCHASE_NO != "" && _updateOrder.PURCHASE_NO != null)
{
if (this.txtPathProcessCard.Text != "")
{
_updateOrder.PATH_PROCESS_CARD = this.txtPathProcessCard.Text.Replace(@"\", @"\\");
PurchaseProperty _updatePurchase = _updateOrderForCopy;
FlowProperty _flowDetail = _flowControllers.SearchFlowByFlowName(new FlowProperty {
FLOW_NAME = cmbFlow.Text
});
_updatePurchase.FLOW.FLOW_NAME = cmbFlow.Text;
_updatePurchase.FLOW.ID = _flowDetail.ID;
if (_controllers.UpdateProcessCardPurchase(_updateOrder, _updatePurchase))
{
this.LoadPurchaseAll();
}
//if (_updateOrder.FLOW.FLOW_NAME != cmbFlow.Text)
//{
// FlowProperty _flowDetail = _flowControllers.SearchFlowByFlowName(new FlowProperty { FLOW_NAME = cmbFlow.Text });
// _updateOrder.FLOW.FLOW_NAME = cmbFlow.Text;
// _updateOrder.FLOW.ID = _flowDetail.ID;
// if (_controllers.UpdateProcessCardPurchase(_updateOrder))
// {
// this.LoadPurchaseAll();
// }
//}
}
else
{
MessageBox.Show("Please Choose file Processcaed ", "Stop", MessageBoxButtons.OK, MessageBoxIcon.Stop);
}
}
else
{
MessageBox.Show("Please Select Purchase", "Stop", MessageBoxButtons.OK, MessageBoxIcon.Stop);
}
}
public string SearchFFTCode(PurchaseProperty dataItem)
{
sql = @"SELECT ID
,FFT_CODE
FROM `fft_code`
WHERE REPLACE(FFT_CODE, ' ', '') = REPLACE('" + dataItem.FFT_CODE + "', ' ', '');";
return(sql);
}
public string SearchProductPurchase(PurchaseProperty dataItem)
{
sql = @"SELECT ID
,PRODUCT_NAME
FROM `product_purchase`
WHERE REPLACE(PRODUCT_NAME, ' ', '') = REPLACE('" + dataItem.PRODUCT_PURCHASE + "', ' ', '');";
return(sql);
}
public string SearchModelNO(PurchaseProperty dataItem)
{
sql = @"SELECT ID
,MODEL_NO
FROM `model`
WHERE REPLACE(MODEL_NO, ' ', '') = REPLACE('" + dataItem.MODEL + "', ' ', '');";
return(sql);
}
public string UpdateUSEFlowProcess(PurchaseProperty dataItem)
{
sql = @"UPDATE `flow_process`
SET (
INUSE = 1
)
WHERE FLOW_ID = (SELECT `ID` FROM `flow` WHERE REPLACE(FLOW_NAME, ' ', '') = REPLACE('" + dataItem.FLOW.FLOW_NAME + @"', ' ', ''))
AND PURCHASE_ID = (SELECT `ID` FROM `purchase` WHERE REPLACE(PURCHASE_NO, ' ', '') = REPLACE('" + dataItem.PURCHASE_NO + @"', ' ', ''))";
return(sql);
}
public string UpdateProcessCardPurchase(PurchaseProperty dataItem)
{
sql = @"UPDATE path_processcard As tb1
INNER JOIN purchase As tb2 ON (tb2.ID = tb1.PURCHASE_ID)
SET tb1.PATH_PROCESS_CARD = '" + dataItem.PATH_PROCESS_CARD + @"'
WHERE tb2.PURCHASE_NO = '" + dataItem.PURCHASE_NO + @"' ";
return(sql);
}
public string SearchFlowProcess(PurchaseProperty dataItem)
{
sql = @"SELECT NO
,INUSE
,FLOW_ID
,PURCHASE_ID
FROM `flow_process`
WHERE FLOW_ID = (SELECT `ID` FROM `flow` WHERE REPLACE(FLOW_NAME, ' ', '') = REPLACE('" + dataItem.FLOW.FLOW_NAME + @"', ' ', ''))
AND PURCHASE_ID = (SELECT `ID` FROM `purchase` WHERE REPLACE(PURCHASE_NO, ' ', '') = REPLACE('" + dataItem.PURCHASE_NO + @"', ' ', ''))";
return(sql);
}
public string InsertPurchase_FFT_Code(PurchaseProperty dataItem)
{
sql = @"INSERT INTO `purchase_fft_code` (
`PURCHASE_ID`
,`FFT_CODE_ID`
)
(
SELECT (SELECT `ID` FROM `purchase` WHERE REPLACE(PURCHASE_NO, ' ', '') = REPLACE('" + dataItem.PURCHASE_NO + @"', ' ', '')) AS `PURCHASE_ID`
,(SELECT `ID` FROM `fft_code` WHERE REPLACE(FFT_CODE, ' ', '') = REPLACE('" + dataItem.FFT_CODE + @"', ' ', '')) AS `FFT_CODE_ID`
); ";
return(sql);
}
public string InsertPathProcessCard(PurchaseProperty dataItem)
{
sql = @"INSERT INTO `path_processcard` (
`PURCHASE_ID`
,`PATH_PROCESS_CARD`
)
(
SELECT (SELECT `ID` FROM `purchase` WHERE REPLACE(PURCHASE_NO, ' ', '') = REPLACE('" + dataItem.PURCHASE_NO + @"', ' ', '')) AS `PURCHASE_ID`
,'" + dataItem.PATH_PROCESS_CARD + @"'
); ";
return(sql);
}
public string UpdateFlowProcessALL(PurchaseProperty dataItem)
{
sql = @"UPDATE `purchase` AS tb1
INNER JOIN flow_process AS tb2
ON tb2.PURCHASE_ID = tb1.ID
SET tb2.INUSE = '0'
WHERE tb1.PURCHASE_NO = '" + dataItem.PURCHASE_NO + @"'
;
";
return(sql);
}
public string InsertModelNO(PurchaseProperty dataItem)
{
sql = @"INSERT INTO `model` (
`ID`
,`MODEL_NO`
)
(
SELECT CASE WHEN COUNT(`ID`) = 0 THEN 1 ELSE MAX(`ID`)+1 END AS `ID`
,'" + dataItem.MODEL + @"' AS `MODEL_NO`
FROM `model`
);";
return(sql);
}
public string SearchPathProcessCard(PurchaseProperty dataItem)
{
sql = @"SELECT tb_1.PURCHASE_ID AS PURCHASE_ID
,tb_2.PURCHASE_NO AS PURCHASE_NO
,tb_1.PATH_PROCESS_CARD AS PATH_PROCESS_CARD
FROM `path_processcard` AS tb_1
INNER JOIN `purchase` AS tb_2
ON (tb_2.`ID` = tb_1.`PURCHASE_ID`)
WHERE tb_2.PURCHASE_NO = '" + dataItem.PURCHASE_NO + @"';";
return(sql);
}
//public string SearchSerialFormat(SerialProductTypeProperty dataItem)
//{
// sql = @"SELECT
// ID
// ,SERIAL_FORMAT
// ,DETAIL
// FROM
// `serial_type`
// WHERE REPLACE(serial_type.SERIAL_FORMAT , ' ' , '') = REPLACE('" + dataItem.SERIAL_TYPE.SERIAL_FORMAT + "', ' ' , '');";
// return sql;
//}
//public string InsertSerialFormat(SerialProductTypeProperty dataItem)
//{
// sql = @"INSERT INTO `serial_type` (
// ID
// ,SERIAL_FORMAT
// ,DETAIL
// )
// (
// SELECT CASE WHEN COUNT(`ID`) = 0 THEN 1 ELSE MAX(`ID`)+1 END AS `ID`
// ,'" + dataItem.SERIAL_TYPE.SERIAL_FORMAT + @"' AS `SERIAL_FORMAT`
// ,'" + dataItem.SERIAL_TYPE.DETAIL + @"' AS `DETAIL`
// FROM `serial_type`
// );";
// return sql;
//}
//public string SearchProductTitle(SerialProductTypeProperty dataItem)
//{
// sql = @"SELECT
// ID
// ,PRODUCT_TITLE
// FROM
// `product_type`
// WHERE REPLACE(PRODUCT_TITLE, ' ' , '') = REPLACE('" + dataItem.PRODUCT_TYPE.PRODUCT_TITLE + "', ' ' , '');";
// return sql;
//}
//public string InsertSerialProductType(SerialProductTypeProperty dataItem)
//{
// sql = @"INSERT INTO `serial_product_type` (
// PRODUCT_TYPE_ID
// ,SERIAL_TYPE_ID
// ,INUSE
// )
// (SELECT tb1.ID As PRODUCT_TYPE_ID
// ,tb2.ID AS SERIAL_TYPE_ID
// ,'1'
// FROM product_type AS tb1
// ,serial_type AS tb2
// WHERE REPLACE (tb2.SERIAL_FORMAT , ' ' , '') = REPLACE('" + dataItem.SERIAL_TYPE.SERIAL_FORMAT + @"',' ' , '')
// AND REPLACE (tb1.PRODUCT_TITLE , ' ' , '') = REPLACE('" + dataItem.PRODUCT_TYPE.PRODUCT_TITLE + @"' ,' ' , '')
// )";
// return sql;
//}
//public string SearchExistProductType(SerialProductTypeProperty dataItem)
//{
// sql = @"SELECT tb1.ID As PRODUCT_TYPE_ID
// FROM product_type AS tb1
// WHERE REPLACE (tb1.PRODUCT_TITLE , ' ' , '') = REPLACE('" + dataItem.PRODUCT_TYPE.PRODUCT_TITLE + @"' ,' ' , '')
// ";
// return sql;
//}
// public string SearchExistSerialProductType(SerialProductTypeProperty dataItem)
// {
// sql = @"SELECT
// tb3.PRODUCT_TITLE
// ,tb2.SERIAL_FORMAT
// ,tb2.DETAIL
// ,tb1.INUSE
// FROM `serial_product_type` AS tb1
// INNER JOIN serial_type AS tb2
// ON tb2.ID = tb1.SERIAL_TYPE_ID
// INNER JOIN product_type AS tb3
// ON tb3.ID =tb1.PRODUCT_TYPE_ID
// WHERE REPLACE (tb3.PRODUCT_TITLE , ' ' , '') = REPLACE('" + dataItem.PRODUCT_TYPE.PRODUCT_TITLE + @"' ,' ' , '')
// AND REPLACE (tb2.SERIAL_FORMAT , ' ' , '') = REPLACE('" + dataItem.SERIAL_TYPE.SERIAL_FORMAT + @"' ,' ' , '')
//;
// ";
// return sql;
// }
//public string UpdateInuseSerialProductType(SerialProductTypeProperty dataItem)
//{
// sql = @"UPDATE `serial_product_type` AS tb1
// INNER JOIN product_type AS tb2
// ON(tb2.ID = tb1.PRODUCT_TYPE_ID)
// SET tb1.INUSE = '0'
// WHERE tb2.PRODUCT_TITLE = '" + dataItem.PRODUCT_TYPE.PRODUCT_TITLE + @"'
// AND tb1.INUSE = '1'
// ;
// UPDATE serial_product_type AS tb1
// INNER JOIN product_type AS tb2
// ON (tb2.ID = tb1.PRODUCT_TYPE_ID)
// INNER JOIN serial_type AS tb3
// ON (tb3.ID = tb1.SERIAL_TYPE_ID)
// SET tb1.INUSE = '1'
// WHERE tb2.PRODUCT_TITLE = '" + dataItem.PRODUCT_TYPE.PRODUCT_TITLE + @"'
// AND tb3.SERIAL_FORMAT = '" + dataItem.SERIAL_TYPE.SERIAL_FORMAT + @"'
// AND tb1.INUSE = '0'
// ";
// return sql;
//}
public string CheckFlowProcess(PurchaseProperty dataItem)
{
sql = @"SELECT tb1.PURCHASE_NO,tb2.FLOW_ID
FROM `purchase` AS tb1
LEFT JOIN flow_process AS tb2
ON tb2.PURCHASE_ID = tb1.ID
WHERE tb1.PURCHASE_NO = '" + dataItem.PURCHASE_NO + @"'
AND tb2.FLOW_ID = '" + dataItem.FLOW.ID + @"'
;
";
return(sql);
}
public string InsertProductPurchase(PurchaseProperty dataItem)
{
sql = @"INSERT INTO `product_purchase` (
`ID`
,`PRODUCT_NAME`
)
(
SELECT CASE WHEN COUNT(`ID`) = 0 THEN 1 ELSE MAX(`ID`)+1 END AS `ID`
,'" + dataItem.PRODUCT_PURCHASE + @"' AS `PRODUCT_NAME`
FROM `product_purchase`
);";
return(sql);
}
public string InsertFFTCode(PurchaseProperty dataItem)
{
sql = @"INSERT INTO `fft_code` (
`ID`
,`FFT_CODE`
)
(
SELECT CASE WHEN COUNT(`ID`) = 0 THEN 1 ELSE MAX(`ID`)+1 END AS `ID`
,'" + dataItem.FFT_CODE + @"' AS `FFT_CODE`
FROM `fft_code`
);";
return(sql);
}
public string SearchPurchase(PurchaseProperty dataItem)
{
sql = @"SELECT
`ID`
,`PURCHASE_NO`
,`PART_NO_ID`
,`PURCHASE_PRODUCT_ID`
,`MODEL_ID`
,`DATE`
,`EMP_ID`
FROM `purchase`
WHERE REPLACE(PURCHASE_NO, ' ', '') = REPLACE('" + dataItem.PURCHASE_NO + @"', ' ', '');";
return(sql);
}
public string UpdateFlowProcess(PurchaseProperty dataItem)
{
sql = @"UPDATE `purchase` AS tb1
LEFT JOIN flow_process AS tb2
ON tb2.PURCHASE_ID = tb1.ID
SET tb2.FLOW_ID = '" + dataItem.FLOW.ID + @"'
,INUSE = '1'
WHERE tb1.PURCHASE_NO = '" + dataItem.PURCHASE_NO + @"'
;
";
return(sql);
}
public FlowProcessProperty SearchFlowNameByPurchase(PurchaseProperty dataItem)
{
FlowProcessProperty _result = new FlowProcessProperty();
List <ProcessProperty> _listProcess = new List <ProcessProperty>();
try
{
_resultData = _models.SearchFlowNameByPurchase(dataItem);
if (_resultData.StatusOnDb == true)
{
if (_resultData.ResultOnDb.Rows.Count > 0)
{
_result.FLOW = new FlowProperty
{
FLOW_NAME = _resultData.ResultOnDb.Rows[0]["FLOW_NAME"].ToString()
};
//_result.PURCHASE = new List<PurchaseProperty>();
for (int i = 0; i < _resultData.ResultOnDb.Rows.Count; i++)
{
ProcessProperty _process = new ProcessProperty
{
ID = _resultData.ResultOnDb.Rows[i]["PROCESS_ID"].ToString(),
PROCESS_NAME = _resultData.ResultOnDb.Rows[i]["PROCESS_NAME"].ToString()
};
_listProcess.Add(_process);
}
_result.PROCESS = _listProcess;
}
}
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 List <PurchaseProperty> SearchPurchasesOrder(OrderProperty dataItem)
{
List <PurchaseProperty> _result = new List <PurchaseProperty>();
try
{
_resultData = _models.SearchPurchasesOrder(dataItem);
if (_resultData.StatusOnDb == true)
{
if (_resultData.ResultOnDb.Rows.Count > 0)
{
for (int i = 0; i < _resultData.ResultOnDb.Rows.Count; i++)
{
PurchaseProperty _purchase = new PurchaseProperty
{
ID = _resultData.ResultOnDb.Rows[i]["ID"].ToString(),
PURCHASE_NO = _resultData.ResultOnDb.Rows[i]["DOCUMENT_NO"].ToString(),
FFT_CODE = _resultData.ResultOnDb.Rows[i]["FFT_CODE"].ToString(),
FLOW = new FlowProperty {
FLOW_NAME = _resultData.ResultOnDb.Rows[i]["FLOW_NAME"].ToString()
},
PRODUCT_PURCHASE = _resultData.ResultOnDb.Rows[i]["PRODUCT_NAME"].ToString(),
PART_NO = _resultData.ResultOnDb.Rows[i]["PART_NO"].ToString(),
MODEL = _resultData.ResultOnDb.Rows[i]["MODEL_NO"].ToString(),
DATE = _resultData.ResultOnDb.Rows[i]["CREATE_DATE"].ToString(),
};
_result.Add(_purchase);
}
}
}
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 InsertPurchaseProductType(PurchaseProperty dataItem)
{
sql = @"INSERT INTO `purchase_product_type` (
`PURCHASE_ID`
,`PRODUCT_TYPE_ID`
)
(
SELECT tb1.ID AS PURCHASE_ID
, tb2.ID AS PRODUCT_TYPE_ID
FROM purchase AS tb1
, product_type as tb2
WHERE REPLACE(tb1.PURCHASE_NO, ' ','') = REPLACE('" + dataItem.PURCHASE_NO + @"', ' ' , '')
AND REPLACE(tb2.PRODUCT_TITLE, ' ','') = REPLACE('" + dataItem.PRODUCT_TYPE.PRODUCT_TITLE + @"', ' ' , '')
); ";
return(sql);
}
public string SearchPurchasesByProduct(PurchaseProperty dataItem)
{
sql = @"SELECT `tb_1`.`ID` AS `ID`
,`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`
,IF(`tb_8`.`FLOW_NAME` IS NULL OR `tb_8`.`FLOW_NAME` = '','N/A',`tb_8`.`FLOW_NAME`) AS `FLOW_NAME`
,`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_ID`
,DATE_FORMAT(`tb_1`.`LAST_UPDATE`, '%Y-%m-%d %H:%i:%s') AS `LAST_UPDATE`
,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`)
INNER JOIN `purchase_product_type` AS `tb_10`
ON(`tb_1`.`ID` = `tb_10`.PURCHASE_ID)
INNER JOIN `product_type` AS `tb_11`
ON(`tb_11`.`ID` = `tb_10`.PRODUCT_TYPE_ID)
where tb_11.PRODUCT_SUB_CODE = 'dataItem.PRODUCT_TYPE.PRODUCT_SUB_CODE'
ORDER BY `tb_1`.`ID`";
sql = sql.Replace("dataItem.PRODUCT_TYPE.PRODUCT_SUB_CODE", dataItem.PRODUCT_TYPE.PRODUCT_SUB_CODE);
return(sql);
}
