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