public int Planning_Insert(PlanningData plnd)
{
if (plnd == null)
{
return(-1);
}
//string sqlStr = "INSERT INTO D_Planning (TaskEntryID, VersionNo, Sales, Budgets, Discussion, OfficeCode, Department) "
// + "VALUES (@tenID, @verN, @sales, @bget, @disc, @oCod, @dept)";
string sqlStr = "INSERT INTO D_Planning (TaskEntryID, VersionNo, Sales, Budgets, Discussion, OfficeCode, Department, "
+ "CreateStat, CreateMCd, CreateDate, ConfirmStat, ConfirmMCd, ConfirmDate, ScreeningStat, ScreeningMCd, ScreeningDate, "
+ "ApOfficerStat, ApOfficerMCd, ApOfficerDate, ApPresidentStat, ApPresidentMCd, ApPresidentDate, ProxyStat, ProxyMCd, ProxyDate) "
+ "VALUES (@tenID, @verN, @sales, @bget, @disc, @oCod, @dept,"
+ "@cSta, @cMCd, @cDat, @fSta, @fMCd, @fDat, @sSta, @sMCd, @sDat, @oSta, @oMCd, @oDat, @pSta, @pMCd, @pDat, @xSta, @xMCd, @xDat)";
string sqlStr2 = "SELECT PlanningID FROM D_Planning WHERE TaskEntryID = " + plnd.TaskEntryID
+ " AND VersionNo = " + plnd.VersionNo
+ " AND OfficeCode = '" + plnd.OfficeCode + "' AND Department = '" + plnd.Department + "'";
int id = -1;
using (TransactionScope tran = new TransactionScope())
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
try
{
conn.Open();
SqlCommand cmd = new SqlCommand(sqlStr, conn);
cmd = planning_cmd(cmd, plnd);
if (TryExecute(conn, cmd) < 0)
{
return(-1);
}
cmd = new SqlCommand(sqlStr2, conn);
SqlDataReader dr = TryExReader(conn, cmd);
if (dr == null)
{
return(-1);
}
while (dr.Read())
{
id = Convert.ToInt32(dr["PlanningID"]);
}
dr.Close();
}
catch (SqlException sqle)
{
MessageBox.Show("SQLエラー errorno " + Convert.ToString(sqle.Number) + " " + sqle.Message);
conn.Close();
return(-1);
}
conn.Close();
tran.Complete();
}
return(id);
}
private SqlCommand planning_cmd(SqlCommand cmd, PlanningData plnd)
{
cmd.Parameters.Add("@tenID", SqlDbType.Int);
cmd.Parameters.Add("@verN", SqlDbType.Int);
cmd.Parameters.Add("@sales", SqlDbType.Decimal);
cmd.Parameters.Add("@bget", SqlDbType.Decimal);
cmd.Parameters.Add("@disc", SqlDbType.NVarChar);
cmd.Parameters.Add("@oCod", SqlDbType.Char);
cmd.Parameters.Add("@dept", SqlDbType.Char);
cmd.Parameters.Add("@cSta", SqlDbType.Int);
cmd.Parameters.Add("@cMCd", SqlDbType.Char);
cmd.Parameters.Add("@cDat", SqlDbType.Date);
cmd.Parameters.Add("@fSta", SqlDbType.Int);
cmd.Parameters.Add("@fMCd", SqlDbType.Char);
cmd.Parameters.Add("@fDat", SqlDbType.Date);
cmd.Parameters.Add("@sSta", SqlDbType.Int);
cmd.Parameters.Add("@sMCd", SqlDbType.Char);
cmd.Parameters.Add("@sDat", SqlDbType.Date);
cmd.Parameters.Add("@oSta", SqlDbType.Int);
cmd.Parameters.Add("@oMCd", SqlDbType.Char);
cmd.Parameters.Add("@oDat", SqlDbType.Date);
cmd.Parameters.Add("@pSta", SqlDbType.Int);
cmd.Parameters.Add("@pMCd", SqlDbType.Char);
cmd.Parameters.Add("@pDat", SqlDbType.Date);
cmd.Parameters.Add("@xSta", SqlDbType.Int);
cmd.Parameters.Add("@xMCd", SqlDbType.Char);
cmd.Parameters.Add("@xDat", SqlDbType.Date);
cmd.Parameters["@tenID"].Value = plnd.TaskEntryID;
cmd.Parameters["@verN"].Value = plnd.VersionNo;
cmd.Parameters["@sales"].Value = plnd.Sales;
cmd.Parameters["@bget"].Value = plnd.Budgets;
cmd.Parameters["@disc"].Value = string.IsNullOrEmpty(plnd.Discussion) ? "" : plnd.Discussion;
cmd.Parameters["@oCod"].Value = plnd.OfficeCode;
cmd.Parameters["@dept"].Value = plnd.Department;
cmd.Parameters["@cSta"].Value = plnd.CreateStat;
cmd.Parameters["@cMCd"].Value = string.IsNullOrEmpty(plnd.CreateMCd) ? "" : plnd.CreateMCd;
cmd.Parameters["@cDat"].Value = plnd.CreateDate;
cmd.Parameters["@fSta"].Value = plnd.ConfirmStat;
cmd.Parameters["@fMCd"].Value = string.IsNullOrEmpty(plnd.ConfirmMCd) ? "" : plnd.ConfirmMCd;
cmd.Parameters["@fDat"].Value = plnd.ConfirmDate;
cmd.Parameters["@sSta"].Value = plnd.ScreeningStat;
cmd.Parameters["@sMCd"].Value = string.IsNullOrEmpty(plnd.ScreeningMCd) ? "" : plnd.ScreeningMCd;
cmd.Parameters["@sDat"].Value = plnd.ScreeningDate;
cmd.Parameters["@oSta"].Value = plnd.ApOfficerStat;
cmd.Parameters["@oMCd"].Value = string.IsNullOrEmpty(plnd.ApOfficerMCd) ? "" : plnd.ApOfficerMCd;
cmd.Parameters["@oDat"].Value = plnd.ApOfficerDate;
cmd.Parameters["@pSta"].Value = plnd.ApPresidentStat;
cmd.Parameters["@pMCd"].Value = string.IsNullOrEmpty(plnd.ApPresidentMCd) ? "" : plnd.ApPresidentMCd;
cmd.Parameters["@pDat"].Value = plnd.ApPresidentDate;
cmd.Parameters["@xSta"].Value = plnd.ProxyStat;
cmd.Parameters["@xMCd"].Value = string.IsNullOrEmpty(plnd.ProxyMCd) ? "" : plnd.ProxyMCd;
cmd.Parameters["@xDat"].Value = plnd.ProxyDate;
return(cmd);
}
public bool Planning_Update(PlanningData plnd)
{
if (plnd == null)
{
return(false);
}
////string sqlStr = "UPDATE D_Planning SET TaskEntryID = @tenID, VersionNo = @verN,"
//string sqlStr = "UPDATE D_Planning SET VersionNo = @verN,"
// + " Sales = @sales, Budgets = @bget, Discussion = @disc, OfficeCode = @oCod, Department = @dept"
// + " WHERE PlanningID = @plnID";
string sqlStr = "UPDATE D_Planning SET VersionNo = @verN,"
+ " Sales = @sales, Budgets = @bget, Discussion = @disc,"
+ " OfficeCode = @oCod, Department = @dept,"
+ " CreateStat = @cSta, CreateMCd = @cMCd, CreateDate = @cDat,"
+ " ConfirmStat = @fSta, ConfirmMCd = @fMCd, ConfirmDate = @fDat,"
+ " ScreeningStat = @sSta, ScreeningMCd = @sMCd, ScreeningDate = @sDat,"
+ " ApOfficerStat = @oSta, ApOfficerMCd = @oMCd, ApOfficerDate = @oDat,"
+ " ApPresidentStat = @pSta, ApPresidentMCd = @pMCd, ApPresidentDate = @pDat,"
+ " ProxyStat = @xSta, ProxyMCd = @xMCd, ProxyDate = @xDat"
+ " WHERE PlanningID = @plnID";
using (TransactionScope tran = new TransactionScope())
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
try
{
conn.Open();
SqlCommand cmd = new SqlCommand(sqlStr, conn);
cmd = planning_cmd(cmd, plnd);
cmd.Parameters.Add("@plnID", SqlDbType.Int);
cmd.Parameters["@plnID"].Value = plnd.PlanningID;
if (TryExecute(conn, cmd) < 0)
{
return(false);
}
}
catch (SqlException sqle)
{
MessageBox.Show("SQLエラー errorno " + Convert.ToString(sqle.Number) + " " + sqle.Message);
conn.Close();
return(false);
}
conn.Close();
tran.Complete();
}
return(true);
}
//--------------------------------------------------------//
// Method
//--------------------------------------------------------//
public new object Clone()
{
PlanningData cloneData = new PlanningData();
cloneData.PlanningID = this.PlanningID;
cloneData.TaskEntryID = this.TaskEntryID;
cloneData.VersionNo = this.VersionNo;
cloneData.Sales = this.Sales;
cloneData.Budgets = this.Budgets;
cloneData.Discussion = this.Discussion;
cloneData.OfficeCode = this.OfficeCode;
cloneData.Department = this.Department;
cloneData.CreateStat = this.CreateStat;
cloneData.CreateMCd = this.CreateMCd;
cloneData.CreateDate = this.CreateDate;
cloneData.ConfirmStat = this.ConfirmStat;
cloneData.ConfirmMCd = this.ConfirmMCd;
cloneData.ConfirmDate = this.ConfirmDate;
cloneData.ScreeningStat = this.ScreeningStat;
cloneData.ScreeningMCd = this.ScreeningMCd;
cloneData.ScreeningDate = this.ScreeningDate;
cloneData.ApOfficerStat = this.ApOfficerStat;
cloneData.ApOfficerMCd = this.ApOfficerMCd;
cloneData.ApOfficerDate = this.ApOfficerDate;
cloneData.ApPresidentStat = this.ApPresidentStat;
cloneData.ApPresidentMCd = this.ApPresidentMCd;
cloneData.ApPresidentDate = this.ApPresidentDate;
cloneData.ProxyStat = this.ProxyStat;
cloneData.ProxyMCd = this.ProxyMCd;
cloneData.ProxyDate = this.ProxyDate;
cloneData.Publisher = this.Publisher;
cloneData.MaxVersion = this.MaxVersion;
cloneData.EstimateID = this.EstimateID;
cloneData.EstimateVer = this.EstimateVer;
cloneData.Direct = this.Direct;
cloneData.OutS = this.OutS;
cloneData.Matel = this.Matel;
cloneData.Sum = this.Sum;
cloneData.Other = this.Other;
cloneData.AdmCost = this.AdmCost;
return(cloneData);
}
public PlanningData LatestPlanningData(int taskEntryID)
{
if (taskEntryID == 0)
{
return(null);
}
string wParam = "WHERE TaskEntryID = " + taskEntryID + " ORDER BY VersionNo DESC";
DataTable dt = SelectAllData_Core("D_Planning", wParam);
if (dt == null)
{
return(null);
}
if (dt.Rows.Count < 1)
{
return(null);
}
PlanningData pld = new PlanningData(dt.Rows[0]);
return(pld);
}