/// <summary> /// 新增生產工程的班次內工程生產資料 /// </summary> /// <param name="model">model</param> /// <param name="tran">SqlTransaction</param> /// <returns></returns> public bool AddShiftProjectProductionData(ShiftProjectProductionData model, SqlTransaction tran) { try { MappingHelper.HandleNullProperty(model); StringBuilder strSql = new StringBuilder(); strSql.Append("insert into ShiftProjList_spl("); strSql.Append("spl_RecordID,spl_PPJID,spl_SIFOID,spl_dBeginTime,spl_dEndTime,spl_dProdBegin,spl_iBeginQTY,spl_iEndQTY,spl_iCalQTY,spl_iQTY,spl_iPrepairTime,spl_iStopTime,spl_iStopTimes,spl_iRunTime,spl_iProdSumTime,spl_iQCTime,spl_iQCTimes,spl_iWastQTY,spl_iPalletQty,spl_lSync,spl_dAddDate,spl_dLastDate,spl_iDefectiveQty,spl_SpareStatusID,spl_iRenewalGoldPaperTimes)"); strSql.Append(" values ("); strSql.Append("@spl_RecordID,@spl_PPJID,@spl_SIFOID,@spl_dBeginTime,@spl_dEndTime,@spl_dProdBegin,@spl_iBeginQTY,@spl_iEndQTY,@spl_iCalQTY,@spl_iQTY,@spl_iPrepairTime,@spl_iStopTime,@spl_iStopTimes,@spl_iRunTime,@spl_iProdSumTime,@spl_iQCTime,@spl_iQCTimes,@spl_iWastQTY,@spl_iPalletQty,@spl_lSync,@spl_dAddDate,@spl_dLastDate,@spl_iDefectiveQty,@spl_SpareStatusID,@spl_iRenewalGoldPaperTimes)"); SqlParameter[] parameters = { DbHelperSQL.MakeInParam("@spl_RecordID", SqlDbType.UniqueIdentifier,16, model.RecordID), DbHelperSQL.MakeInParam("@spl_PPJID", SqlDbType.UniqueIdentifier,16, model.spl_PPJID), DbHelperSQL.MakeInParam("@spl_SIFOID", SqlDbType.UniqueIdentifier,16, model.spl_SIFOID), DbHelperSQL.MakeInParam("@spl_dBeginTime", SqlDbType.DateTime, 0, model.spl_dBeginTime), DbHelperSQL.MakeInParam("@spl_dEndTime", SqlDbType.DateTime, 0, model.spl_dEndTime), DbHelperSQL.MakeInParam("@spl_dProdBegin", SqlDbType.DateTime, 0, model.spl_dProdBegin), DbHelperSQL.MakeInParam("@spl_iBeginQTY", SqlDbType.Int,4, model.spl_iBeginQTY), DbHelperSQL.MakeInParam("@spl_iEndQTY", SqlDbType.Int,4, model.spl_iEndQTY), DbHelperSQL.MakeInParam("@spl_iCalQTY", SqlDbType.Int,4, model.spl_iCalQTY), DbHelperSQL.MakeInParam("@spl_iQTY", SqlDbType.Int,4, model.spl_iQTY), DbHelperSQL.MakeInParam("@spl_iPrepairTime", SqlDbType.Int,4, model.spl_iPrepairTime), DbHelperSQL.MakeInParam("@spl_iStopTime", SqlDbType.Int,4, model.spl_iStopTime), DbHelperSQL.MakeInParam("@spl_iStopTimes", SqlDbType.Int,4, model.spl_iStopTimes), DbHelperSQL.MakeInParam("@spl_iRunTime", SqlDbType.Int,4, model.spl_iRunTime), DbHelperSQL.MakeInParam("@spl_iProdSumTime", SqlDbType.Int,4, model.spl_iProdSumTime), DbHelperSQL.MakeInParam("@spl_iQCTime", SqlDbType.Int,4, model.spl_iQCTime), DbHelperSQL.MakeInParam("@spl_iQCTimes", SqlDbType.Int,4, model.spl_iQCTimes), DbHelperSQL.MakeInParam("@spl_iWastQTY", SqlDbType.Int,4, model.spl_iWastQTY), DbHelperSQL.MakeInParam("@spl_iPalletQty", SqlDbType.Int,4, 0), //model.spl_iPalletQty; DbHelperSQL.MakeInParam("@spl_lSync", SqlDbType.Bit,1, 0),//model.spl_lSync; DbHelperSQL.MakeInParam("@spl_dAddDate", SqlDbType.DateTime, 0, DateTime.Now), DbHelperSQL.MakeInParam("@spl_dLastDate", SqlDbType.DateTime, 0, DateTime.Now), DbHelperSQL.MakeInParam("@spl_iDefectiveQty", SqlDbType.Int,4, model.spl_iDefectiveQty), DbHelperSQL.MakeInParam("@spl_SpareStatusID", SqlDbType.UniqueIdentifier,16, model.spl_SpareStatusID), DbHelperSQL.MakeInParam("@spl_iRenewalGoldPaperTimes", SqlDbType.Int,4, model.spl_iRenewalGoldPaperTimes) }; DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, strSql.ToString(), parameters); return true; } catch (Exception ex) { throw ex; } }
/// <summary> /// 更新生產工程的班次內工程生產資料(只更新部份*務必請進入看清楚只更新哪些欄位*) /// </summary> /// <param name="model">model</param> /// <param name="tran">SqlTransaction</param> /// <returns></returns> public bool UpdateShiftProjectProductionData2(ShiftProjectProductionData model, SqlTransaction tran) { try { ShiftProjectProductionData data_DB = this.GetShiftProjectProductionData("spl_RecordID='" + model.RecordID + "'").FirstOrDefault(); StringBuilder strSql = new StringBuilder(); strSql.Append("update ShiftProjList_spl set "); if (data_DB != null && data_DB.spl_dProdBegin == null) strSql.Append("spl_dProdBegin=" + Common.General.HandleSqlFormatDateTime(model.spl_dProdBegin) + ","); strSql.Append("spl_dLastDate=" + Common.General.HandleSqlFormatDateTime(DateTime.Now) + ","); strSql.Append("spl_iCalQTY=" + model.spl_iCalQTY + ","); strSql.Append("spl_iQTY=" + model.spl_iQTY); strSql.Append(" where spl_RecordID='" + model.RecordID + "' "); DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, strSql.ToString(), null); return true; } catch (Exception ex) { throw ex; } }
/// <summary> /// 对象实体绑定数据(ShiftProjectProductionData) /// </summary> private ShiftProjectProductionData ReaderBind_SPL(IDataReader dataReader) { ShiftProjectProductionData model = new ShiftProjectProductionData(); object ojb; ojb = dataReader["spl_RecordID"]; if (ojb != null && ojb != DBNull.Value) { model.RecordID = new Guid(ojb.ToString()); } ojb = dataReader["spl_PPJID"]; if (ojb != null && ojb != DBNull.Value) { model.spl_PPJID = new Guid(ojb.ToString()); } ojb = dataReader["spl_SIFOID"]; if (ojb != null && ojb != DBNull.Value) { model.spl_SIFOID = new Guid(ojb.ToString()); } ojb = dataReader["spl_dBeginTime"]; if (ojb != null && ojb != DBNull.Value) { model.spl_dBeginTime = (DateTime)ojb; } ojb = dataReader["spl_dEndTime"]; if (ojb != null && ojb != DBNull.Value) { model.spl_dEndTime = (DateTime)ojb; } ojb = dataReader["spl_dProdBegin"]; if (ojb != null && ojb != DBNull.Value) { model.spl_dProdBegin = (DateTime)ojb; } ojb = dataReader["spl_iBeginQTY"]; if (ojb != null && ojb != DBNull.Value) { model.spl_iBeginQTY = (int)ojb; } ojb = dataReader["spl_iEndQTY"]; if (ojb != null && ojb != DBNull.Value) { model.spl_iEndQTY = (int)ojb; } ojb = dataReader["spl_iCalQTY"]; if (ojb != null && ojb != DBNull.Value) { model.spl_iCalQTY = (int)ojb; } ojb = dataReader["spl_iQTY"]; if (ojb != null && ojb != DBNull.Value) { model.spl_iQTY = (int)ojb; } ojb = dataReader["spl_iPrepairTime"]; if (ojb != null && ojb != DBNull.Value) { model.spl_iPrepairTime = (int)ojb; } ojb = dataReader["spl_iStopTime"]; if (ojb != null && ojb != DBNull.Value) { model.spl_iStopTime = (int)ojb; } ojb = dataReader["spl_iStopTimes"]; if (ojb != null && ojb != DBNull.Value) { model.spl_iStopTimes = (int)ojb; } ojb = dataReader["spl_iRunTime"]; if (ojb != null && ojb != DBNull.Value) { model.spl_iRunTime = (int)ojb; } ojb = dataReader["spl_iProdSumTime"]; if (ojb != null && ojb != DBNull.Value) { model.spl_iProdSumTime = (int)ojb; } ojb = dataReader["spl_iQCTime"]; if (ojb != null && ojb != DBNull.Value) { model.spl_iQCTime = (int)ojb; } ojb = dataReader["spl_iQCTimes"]; if (ojb != null && ojb != DBNull.Value) { model.spl_iQCTimes = (int)ojb; } ojb = dataReader["spl_iWastQTY"]; if (ojb != null && ojb != DBNull.Value) { model.spl_iWastQTY = (int)ojb; } ojb = dataReader["spl_iPalletQty"]; if (ojb != null && ojb != DBNull.Value) { //model.spl_iPalletQty = (int)ojb; } ojb = dataReader["spl_lSync"]; if (ojb != null && ojb != DBNull.Value) { //model.spl_lSync = (bool)ojb; } ojb = dataReader["spl_dAddDate"]; if (ojb != null && ojb != DBNull.Value) { model.AddDate = (DateTime)ojb; } ojb = dataReader["spl_dLastDate"]; if (ojb != null && ojb != DBNull.Value) { model.LastDate = (DateTime)ojb; } ojb = dataReader["spl_iDefectiveQty"]; if (ojb != null && ojb != DBNull.Value) { model.spl_iDefectiveQty = (int)ojb; } return model; }
/// <summary> /// 測試代碼 /// </summary> public void Test() { bool l_lIsSuess = false; using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString)) { SqlTransaction transaction = null; conn.Open(); //開啟連接 transaction = conn.BeginTransaction(); //開啟事務 try { //this.AddProjectExtractionReason(Guid.NewGuid().ToString(), new Guid("35239144-0BB7-4A7A-AC12-008E11157C29"), "test", "testtest", DateTime.Now, transaction); //this.AddProjectExtractionReason(Guid.NewGuid().ToString(), new Guid("35239144-0BB7-4A7A-AC12-008E11157C29"), "test", "testtest", null, transaction); //ShiftProjectProductionData data_DB = this.GetShiftProjectProductionData("spl_RecordID='" + "BDF30E72-65EE-4601-BBEB-0067C8490653" + "'").FirstOrDefault(); //data_DB.spl_iDinnerTime = 10; //this.UpdateShiftProjectProductionData(data_DB, transaction); ShiftProjectProductionData model = new ShiftProjectProductionData() { RecordID = new Guid("BDF30E72-65EE-4601-BBEB-0067C8490653"), AddDate = DateTime.Now, LastDate = DateTime.Now.AddDays(-1), }; StringBuilder strSql = new StringBuilder(); strSql.Append(" select null as datetimeNull, getdate() as getdatetime, null as nullstring "); using (IDataReader dataReader = DbHelperSQL.ExecuteReader(strSql.ToString())) { while (dataReader.Read()) { object ojb; ojb = dataReader["datetimeNull"]; ojb = dataReader["getdatetime"]; ojb = dataReader["nullstring"]; } } //strSql.Append("update ShiftProjList_spl set "); //strSql.Append("spl_dAddDate='" + model.AddDate + "',"); //strSql.Append("spl_dLastDate='" + model.LastDate + "'"); //strSql.Append(" where spl_RecordID='" + model.RecordID + "' "); //DbHelperSQL.ExecuteNonQuery(transaction, CommandType.Text, strSql.ToString(), null); l_lIsSuess = true; } catch (Exception exTran) { l_lIsSuess = false; throw exTran; } finally { if (l_lIsSuess) transaction.Commit(); else transaction.Rollback(); } } }