public string SearchByProductTypeId(SerialProductTypeProperty dataItem)
{
sql = @"SELECT * FROM `serial_product_type`
WHERE PRODUCT_TYPE_ID = 'dataItem.PRODUCT_TYPE_ID.ID'";
sql = sql.Replace("dataItem.PRODUCT_TYPE_ID.ID", dataItem.PRODUCT_TYPE.ID);
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 InsertSerialProductType(SerialProductTypeProperty dataItem)
{
sql = @"INSERT INTO `serial_product_type` (
ID
, PRODUCT_TYPE_ID
, SERIAL_TYPE_ID
, INUSE
, DESCRIPTION
, CREATE_USER
)
(
SELECT 1 + coalesce((SELECT max(Id) FROM serial_product_type), 0)
, (SELECT id FROM product_type WHERE PRODUCT_TITLE = 'dataItem.SERIAL_TYPE.PRODUCT_TITLE' AND PRODUCT_SUB_CODE = 'dataItem.SERIAL_TYPE.PRODUCT_SUB_CODE')
, (SELECT id FROM serial_type WHERE SERIAL_FORMAT = 'dataItem.SERIAL_TYPE.SERIAL_FORMAT')
, 1
, 'dataItem.DESCRIPTION'
, 'dataItem.CREATE_USER'
)";
sql = sql.Replace("dataItem.ID", dataItem.ID);
sql = sql.Replace("dataItem.SERIAL_TYPE.PRODUCT_TITLE", dataItem.PRODUCT_TYPE.PRODUCT_TITLE);
sql = sql.Replace("dataItem.SERIAL_TYPE.PRODUCT_SUB_CODE", dataItem.PRODUCT_TYPE.PRODUCT_SUB_CODE);
sql = sql.Replace("dataItem.SERIAL_TYPE.SERIAL_FORMAT", dataItem.SERIAL_TYPE.SERIAL_FORMAT);
sql = sql.Replace("dataItem.INUSE", dataItem.INUSE);
sql = sql.Replace("dataItem.DESCRIPTION", dataItem.DESCRIPTION);
sql = sql.Replace("dataItem.CREATE_USER", dataItem.CREATE_USER);
sql = sql.Replace("dataItem.LAST_USER", dataItem.LAST_USER);
sql = sql.Replace("dataItem.CREATE_DATE", dataItem.CREATE_DATE);
sql = sql.Replace("dataItem.LAST_DATE", dataItem.LAST_DATE);
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 UpdateInuseSerialProductType(SerialProductTypeProperty dataItem)
{
sql = @"UPDATE `serial_product_type`
SET INUSE = '0'
WHERE INUSE = '1'
AND PRODUCT_TYPE_ID = 'dataItem.PRODUCT_TYPE_ID'
";
sql = sql.Replace("dataItem.PRODUCT_TYPE_ID", dataItem.PRODUCT_TYPE.ID);
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 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);
}
private void btnSet_Click(object sender, EventArgs e)
{
if (string.IsNullOrWhiteSpace(this.txtProductTitle.Text.Trim()))
{
MessageBox.Show("Can't set serial title because not found serial title", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (string.IsNullOrWhiteSpace(this.txtProductFormat.Text.Trim()))
{
MessageBox.Show("Can't set serial format because not found serial format", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (string.IsNullOrWhiteSpace(this.lblShowExFormatSerial.Text.ToString().Trim()))
{
MessageBox.Show("Can't set serial format because not found serial detail format", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (listInuseFormat.Count == 0)
{
MessageBox.Show("Can't set serial title because not found Product Type", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
SerialProductTypeProperty setFormat = new SerialProductTypeProperty
{
PRODUCT_TYPE = new ProductTypeProperty {
PRODUCT_TITLE = this.txtProductTitle.Text.Trim(), ID = (lstProductType.SelectedItem as ListBoxItem).Value.ToString(), PRODUCT_SUB_CODE = (cmbProduct.SelectedItem as ComboboxItem).Value.ToString()
}
,
SERIAL_TYPE = new SerialTypeProperty
{
ID = listInuseFormat[0].SERIAL_TYPE.ID,
SERIAL_FORMAT = this.txtProductFormat.Text.Trim()
,
DETAIL = this.lblShowExFormatSerial.Text.ToString().Trim()
}
};
if (_controller.UpdateSerialFormat(setFormat))
{
listSerialTypeProperty = _serialTypeController.Search();
this.Load_ProductType();
}
}
public string InsertProductTitle(SerialProductTypeProperty dataItem)
{
sql = @"INSERT INTO `product_type` (
ID
,PRODUCT_TITLE
)
(
SELECT CASE WHEN COUNT(`ID`) = 0 THEN 1 ELSE MAX(`ID`)+1 END AS `ID`
,'" + dataItem.PRODUCT_TYPE.PRODUCT_TITLE + @"' AS `PRODUCT_TITLE`
FROM `product_type`
);";
return(sql);
}
public List <SerialProductTypeProperty> SearchByProductTypeId(SerialProductTypeProperty dataItem)
{
List <SerialProductTypeProperty> _result = new List <SerialProductTypeProperty>();
try
{
_resultData = _models.SearchByProductTypeId(dataItem);
if (_resultData.StatusOnDb == true)
{
if (_resultData.ResultOnDb.Rows.Count > 0)
{
for (int i = 0; i < _resultData.ResultOnDb.Rows.Count; i++)
{
SerialProductTypeProperty SerialProductTypeProperty = new SerialProductTypeProperty()
{
ID = _resultData.ResultOnDb.Rows[i]["ID"].ToString(),
PRODUCT_TYPE = new ProductTypeProperty()
{
ID = _resultData.ResultOnDb.Rows[i]["PRODUCT_TYPE_ID"].ToString()
},
SERIAL_TYPE = new SerialTypeProperty()
{
ID = _resultData.ResultOnDb.Rows[i]["SERIAL_TYPE_ID"].ToString()
},
INUSE = _resultData.ResultOnDb.Rows[i]["INUSE"].ToString(),
DESCRIPTION = _resultData.ResultOnDb.Rows[i]["DESCRIPTION"].ToString(),
CREATE_USER = _resultData.ResultOnDb.Rows[i]["CREATE_USER"].ToString(),
LAST_USER = _resultData.ResultOnDb.Rows[i]["LAST_USER"].ToString(),
CREATE_DATE = _resultData.ResultOnDb.Rows[i]["CREATE_DATE"].ToString(),
LAST_DATE = _resultData.ResultOnDb.Rows[i]["LAST_DATE"].ToString(),
};
_result.Add(SerialProductTypeProperty);
}
}
}
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 <SerialProductTypeProperty> SearchSerialProductType()
{
List <SerialProductTypeProperty> _result = new List <SerialProductTypeProperty>();
try
{
_resultData = _models.SearchProductType();
if (_resultData.StatusOnDb == true)
{
if (_resultData.ResultOnDb.Rows.Count > 0)
{
for (int i = 0; i < _resultData.ResultOnDb.Rows.Count; i++)
{
ProductTypeProperty _productType = new ProductTypeProperty
{
ID = _resultData.ResultOnDb.Rows[i]["PRODUCT_TYPE_ID"].ToString(),
PRODUCT_TITLE = _resultData.ResultOnDb.Rows[i]["PRODUCT_TITLE"].ToString()
};
SerialTypeProperty _serialType = new SerialTypeProperty
{
ID = _resultData.ResultOnDb.Rows[i]["SERIAL_TYPE_ID"].ToString(),
SERIAL_FORMAT = _resultData.ResultOnDb.Rows[i]["SERIAL_FORMAT"].ToString(),
DETAIL = _resultData.ResultOnDb.Rows[i]["DETAIL"].ToString()
};
SerialProductTypeProperty _serialProductType = new SerialProductTypeProperty
{
PRODUCT_TYPE = _productType,
SERIAL_TYPE = _serialType
};
_result.Add(_serialProductType);
}
}
}
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 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);
}
private void lstProductType_Click(object sender, EventArgs e)
{
if (lstProductType.SelectedItem != null)
{
SerialProductTypeProperty SerialProductTypeProperty = new SerialProductTypeProperty()
{
PRODUCT_TYPE = new ProductTypeProperty()
{
ID = (lstProductType.SelectedItem as ListBoxItem).Value.ToString()
}
};
List <SerialProductTypeProperty> listSerialProductTypeProperty = _serialProductTypeController.SearchByProductTypeId(SerialProductTypeProperty);
listHistoryFormat = listSerialProductTypeProperty.FindAll(x => x.INUSE == "False");
listInuseFormat = listSerialProductTypeProperty.FindAll(x => x.INUSE == "True");
string title = (lstProductType.SelectedItem as ListBoxItem).Text.ToString();
//int indexInList = listInuseFormat.FindIndex(a => a.PRODUCT_TYPE.ID.Contains(title));
this.txtProductTitle.Text = (lstProductType.SelectedItem as ListBoxItem).Text.ToString();
//กรณีเพิ่ม Product type ใหม่
if (listInuseFormat.Count > 0)
{
this.txtProductFormat.Text = listSerialTypeProperty.Find(x => x.ID == listInuseFormat[0].SERIAL_TYPE.ID).SERIAL_FORMAT;
}
else
{
this.txtProductFormat.Text = "";
}
lstHis.Items.Clear();
foreach (SerialProductTypeProperty item in listHistoryFormat)
{
lstHis.Items.Add(listSerialTypeProperty.Find(x => x.ID == item.SERIAL_TYPE.ID).SERIAL_FORMAT);
}
this.lstHis.Enabled = this.lstHis.Items.Count == 0 ? false : true;
}
}
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);
}
private void btnCreate_Click(object sender, EventArgs e)
{
SerialProductTypeProperty _insertProductType = new SerialProductTypeProperty();
_insertProductType.PRODUCT_TYPE = new ProductTypeProperty();
_insertProductType.SERIAL_TYPE = new SerialTypeProperty();
//********** GET DATA **********//
_insertProductType.PRODUCT_TYPE.PRODUCT_TITLE = this.txtProductTitle.Text.ToString().Trim();
_insertProductType.PRODUCT_TYPE.PRODUCT_SUB_CODE = (cmbProduct.SelectedItem as ComboboxItem).Value.ToString();
_insertProductType.SERIAL_TYPE.SERIAL_FORMAT = this.txtProductFormat.Text.ToString().Trim();
_insertProductType.SERIAL_TYPE.DETAIL = this.lblShowExFormatSerial.Text.ToString().Trim();
//********** Check PRODUCT TITLE **********//
if (_insertProductType.PRODUCT_TYPE.PRODUCT_TITLE == null || _insertProductType.PRODUCT_TYPE.PRODUCT_TITLE == "")
{
MessageBox.Show("Not Found PRODUCT_TITLE" + "\n\n" + "Please Check TITLE BOX ", "Stop", MessageBoxButtons.OK, MessageBoxIcon.Stop); return;
}
//********* Check SERIAL FORMAT **********//
if (_insertProductType.SERIAL_TYPE.SERIAL_FORMAT == null || _insertProductType.SERIAL_TYPE.SERIAL_FORMAT == "")
{
MessageBox.Show("Not Found SERIAL_FORMAT" + "\n\n" + "Please Check FORMAT BOX ", "Stop", MessageBoxButtons.OK, MessageBoxIcon.Stop); return;
}
//********* Check SERIAL DETIAL **********//
if (_insertProductType.SERIAL_TYPE.DETAIL == null || _insertProductType.SERIAL_TYPE.DETAIL == "")
{
MessageBox.Show("Not Found SERIAL_DETIAL" + "\n\n" + "Please Check FORMAT BOX ", "Stop", MessageBoxButtons.OK, MessageBoxIcon.Stop); return;
}
//Insert Product Type//
if (_controller.InsertProductType(_insertProductType))
{
listSerialTypeProperty = _serialTypeController.Search();
this.Load_ProductType();
}
}
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 bool UpdateSerialFormat(SerialProductTypeProperty dataItem)
{
bool _result = false;
try
{
_resultData = _models.UpdateSerialFormat(dataItem);
if (_resultData.StatusOnDb == true)
{
MessageBox.Show(_resultData.MessageOnDb, "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
_result = true;
}
else
{
MessageBox.Show(_resultData.MessageOnDb, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return(_result);
}
public string SearchExistSerialProductType(SerialProductTypeProperty dataItem)
{
sql = @"SELECT
ID
, PRODUCT_TYPE_ID
, SERIAL_TYPE_ID
, INUSE
, DESCRIPTION
, CREATE_USER
, LAST_USER
, CREATE_DATE
, LAST_DATE
FROM `serial_product_type`
WHERE
PRODUCT_TYPE_ID = 'dataItem.PRODUCT_TYPE_ID'
AND SERIAL_TYPE_ID = 'dataItem.SERIAL_TYPE_ID'
AND INUSE = 1;
";
sql = sql.Replace("dataItem.PRODUCT_TYPE_ID", dataItem.PRODUCT_TYPE.ID);
sql = sql.Replace("dataItem.SERIAL_TYPE_ID", dataItem.SERIAL_TYPE.ID);
return(sql);
}
public OutputOnDbProperty SearchProductTitle(SerialProductTypeProperty dataItem)
{
sql = _sqlFactoryProductType.SearchProductTitle(dataItem.PRODUCT_TYPE);
resultData = base.SearchBySql(sql);
return(resultData);
}
public OutputOnDbProperty UpdateSerialFormat(SerialProductTypeProperty dataItem)
{
OutputOnDbProperty resultData_InSide = new OutputOnDbProperty();
List <string> _listSQL = new List <string>();
//## Check product Title. ######################################################################
resultData_InSide = this.SearchExistProductType(dataItem);
if (resultData_InSide.StatusOnDb == true)
{
//Check Serial Title in DB.
if (resultData_InSide.ResultOnDb.Rows.Count <= 0)
{
resultData_InSide.StatusOnDb = false;
resultData_InSide.MessageOnDb = "Not found Serial Title.";
return(resultData_InSide);
}
}
else
{
return(resultData_InSide);
}
//## Check Serial Format. ######################################################################
resultData_InSide = this.SearchSerialFormat(dataItem);
if (resultData_InSide.StatusOnDb == true)
{
//Check Serial Format in DB.
if (resultData_InSide.ResultOnDb.Rows.Count <= 0)
{
//insert serial format
_listSQL.Add(_sqlFactorySerialType.InsertSerialFormat(dataItem.SERIAL_TYPE));
}
}
else
{
return(resultData_InSide);
}
// Update inuse = 0 table SerialproductType
_listSQL.Add(_sqlFactorySerialProductType.UpdateInuseSerialProductType(dataItem));
//## Check SerialproductType. ######################################################################
//resultData_InSide = this.SearchExistSerialProductType(dataItem);
//if (resultData_InSide.StatusOnDb == true)
//{
// //Check SerialproductType.
// if (resultData_InSide.ResultOnDb.Rows.Count <= 0)
// {
// _sqlFactorySerialType.SearchSerialFormat(new SerialTypeProperty
// {
// SERIAL_FORMAT = dataItem.SERIAL_TYPE.SERIAL_FORMAT
// });
// // insert SerialproductType
// _listSQL.Add(_sqlFactorySerialProductType.InsertSerialProductType(dataItem));
// }
//}
//else
//{
// return resultData_InSide;
//}
_sqlFactorySerialType.SearchSerialFormat(new SerialTypeProperty
{
SERIAL_FORMAT = dataItem.SERIAL_TYPE.SERIAL_FORMAT
});
// insert SerialproductType
_listSQL.Add(_sqlFactorySerialProductType.InsertSerialProductType(dataItem));
resultData = base.InsertBySqlList(_listSQL);
return(resultData);
}
// by BOAT 01/11/2019
public OutputOnDbProperty SearchSerialFormat(SerialProductTypeProperty dataItem)
{
sql = _sqlFactorySerialType.SearchSerialFormat(dataItem.SERIAL_TYPE);
resultData = base.SearchBySql(sql);
return(resultData);
}
public OutputOnDbProperty SearchByProductTypeId(SerialProductTypeProperty dataItem)
{
_resultData = _services.SearchByProductTypeId(dataItem);
return(_resultData);
}
public OutputOnDbProperty UpdateSerialFormat(SerialProductTypeProperty dataItem)
{
_resultData = _services.UpdateSerialFormat(dataItem);
return(_resultData);
}
public OutputOnDbProperty InsertProductType(SerialProductTypeProperty dataItem)
{
_resultData = _services.InsertProductType(dataItem);
return(_resultData);
}
public OutputOnDbProperty SearchExistSerialProductType(SerialProductTypeProperty dataItem)
{
sql = _sqlFactorySerialProductType.SearchExistSerialProductType(dataItem);
resultData = base.SearchBySql(sql);
return(resultData);
}
//by BOAT 01/11/2019
public OutputOnDbProperty InsertProductType(SerialProductTypeProperty dataItem)
{
//SerialProductTypeProperty _serialType = new SerialProductTypeProperty { PART_NO = dataItem.PART_NO };
OutputOnDbProperty resultData_InSide = new OutputOnDbProperty();
List <string> _listSQL = new List <string>();
//## Check Serial Format. ######################################################################
resultData_InSide = SearchSerialFormat(dataItem);
if (resultData_InSide.StatusOnDb == true)
{
//No Serial Format in DB.
if (resultData_InSide.ResultOnDb.Rows.Count <= 0)
{
//Get Qry Insert Serial Format to sqlList.
_listSQL.Add(_sqlFactorySerialType.InsertSerialFormat(dataItem.SERIAL_TYPE));
}
}
else
{
return(resultData_InSide);
}
//## Check ProductTitle . ######################################################################
resultData_InSide = this.SearchProductTitle(dataItem);
if (resultData_InSide.StatusOnDb == true)
{
//No ProductTitle in DB.
if (resultData_InSide.ResultOnDb.Rows.Count <= 0)
{
//Get Qry Insert ProductTitle to sqlList.
_listSQL.Add(_sqlFactoryProductType.InsertProductTitle(dataItem.PRODUCT_TYPE));
}
}
else
{
return(resultData_InSide);
}
//Check Serial Type In database
resultData_InSide = this.SearchExistProductType(dataItem);
if (resultData_InSide.StatusOnDb == true)
{
//No Serial product type in DB.
if (resultData_InSide.ResultOnDb.Rows.Count <= 0)
{
//Get Qry Insert Serial product type to sqlList.
_listSQL.Add(_sqlFactorySerialProductType.InsertSerialProductType(dataItem));
}
else
{
resultData_InSide.StatusOnDb = false;
resultData_InSide.MessageOnDb = "Duplicate Product Type.";
return(resultData_InSide);
}
}
else
{
return(resultData_InSide);
}
resultData = base.InsertBySqlList(_listSQL);
return(resultData);
}