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