public VolumeData[] SelectVolumeData(string officeCode, string department, string taskCode, int year, int preYear)
{
string sqlDepartment = "";
if (!String.IsNullOrEmpty(department))
{
sqlDepartment = " AND Department =" + "'" + department + "'";
}
string strSql = " NULL AS TaskCode, NULL AS YearMonth, NULL AS Publisher, ClaimDate, PaidDate, NULL AS Comment, NULL AS TaskStat, NULL AS CarryOverPlanned, NULL AS OfficeCode, NULL AS Department, NULL AS Note, "
+ "Volume AS MonthlyVolume, VolUncomp, VolClaimRem, VolClaim, VolPaid, Claim AS MonthlyClaim, Cost AS MonthlyCost, BalanceClaim, BalanceIncom, Deposit1, Deposit2 FROM D_YearVolume "
+ "WHERE TaskCode = " + "'" + taskCode + "' "
+ "AND OfficeCode =" + "'" + officeCode + "' "
+ sqlDepartment
+ " AND YearMonth = " + "'" + preYear + "'";
SqlHandling sh = new SqlHandling("D_YearVolume");
DataTable dt = sh.SelectFullDescription(strSql);
if (dt == null)
{
return(null);
}
VolumeData[] vd = new VolumeData[dt.Rows.Count];
for (int i = 0; i < dt.Rows.Count; i++)
{
vd[i] = new VolumeData(dt.Rows[i]);
}
return(vd);
}
public bool InsertVolume(VolumeData vd)
{
using (TransactionScope tran = new TransactionScope())
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
try
{
conn.Open();
SqlCommand cmd = new SqlCommand(insSql, conn);
cmd = parametersSqlDbType(cmd);
cmd = parametersValue(cmd, vd);
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 object Clone()
{
VolumeData cloneData = new VolumeData();
cloneData.VolumeID = this.VolumeID;
cloneData.TaskCode = this.TaskCode;
cloneData.OfficeCode = this.OfficeCode;
cloneData.Department = this.Department;
cloneData.YearMonth = this.YearMonth;
cloneData.MonthlyVolume = this.MonthlyVolume;
cloneData.VolUncomp = this.VolUncomp;
cloneData.VolClaimRem = this.VolClaimRem;
cloneData.VolClaim = this.VolClaim;
cloneData.MonthlyClaim = this.MonthlyClaim;
cloneData.VolPaid = this.VolPaid;
cloneData.BalanceClaim = this.BalanceClaim;
cloneData.BalanceIncom = this.BalanceIncom;
cloneData.MonthlyCost = this.MonthlyCost;
cloneData.ClaimDate = this.ClaimDate;
cloneData.PaidDate = this.PaidDate;
cloneData.CarryOverPlanned = this.CarryOverPlanned;
cloneData.Comment = this.Comment;
cloneData.TaskStat = this.TaskStat;
cloneData.Note = this.Note;
return(cloneData);
}
private VolumeData loadVolume(int volumeID)
{
SqlHandling sh = new SqlHandling("D_Volume");
DataTable dt = sh.SelectAllData("WHERE VolumeID = " + volumeID);
if (dt == null || dt.Rows.Count < 1)
{
return(null);
}
VolumeData vd = new VolumeData(dt.Rows[0]);
return(vd);
}
//public VolumeData[] SelectSummaryVolume(string officeCode, string department)
//{
// VolumeData[] vold;
// SqlHandling sh = new SqlHandling();
// DataTable dt = sh.SelectFullDescription(sumPara + " OfficeCode = '" + officeCode + "' AND Department = '" + department + "'");
// if (dt == null)
// {
// vold = new VolumeData[1];
// }
// else
// {
// vold = new VolumeData[dt.Rows.Count];
// for (int i = 0; i < dt.Rows.Count; i++)
// {
// vold[i] = (VolumeData)(setVolumeData(dt.Rows[i])).Clone();
// vold[i].OfficeCode = officeCode;
// vold[i].Department = department;
// }
// }
// return vold;
//}
public VolumeData setVolumeData(DataRow dr)
{
VolumeData vol = new VolumeData();
vol.MonthlyVolume = (Convert.ToString(dr["sMV"]) == "") ? 0M : Convert.ToDecimal(dr["sMV"]);
vol.VolUncomp = (Convert.ToString(dr["sVU"]) == "") ? 0M : Convert.ToDecimal(dr["sVU)"]);
vol.VolClaimRem = (Convert.ToString(dr["sVCR"]) == "") ? 0M : Convert.ToDecimal(dr["sVCR"]);
vol.VolClaim = (Convert.ToString(dr["sVC"]) == "") ? 0M : Convert.ToDecimal(dr["sVC"]);
vol.MonthlyClaim = (Convert.ToString(dr["sMC"]) == "") ? 0M : Convert.ToDecimal(dr["sMC"]);
vol.VolPaid = (Convert.ToString(dr["sVP"]) == "") ? 0M : Convert.ToDecimal(dr["sVP"]);
vol.BalanceClaim = (Convert.ToString(dr["sBC"]) == "") ? 0M : Convert.ToDecimal(dr["sBC"]);
vol.BalanceIncom = (Convert.ToString(dr["sBI"]) == "") ? 0M : Convert.ToDecimal(dr["sBI"]);
vol.MonthlyCost = (Convert.ToString(dr["sMCO"]) == "") ? 0M : Convert.ToDecimal(dr["sMCO"]);
vol.CarryOverPlanned = (Convert.ToString(dr["sCOP"]) == "") ? 0M : Convert.ToDecimal(dr["sCOP"]);
return(vol);
}
public VolumeData[] SelectVolumeDate(string strSql)
{
SqlHandling sh = new SqlHandling("D_Volume");
DataTable dt = sh.SelectFullDescription(strSql);
if (dt == null || dt.Rows.Count < 1)
{
return(null);
}
VolumeData[] vd = new VolumeData[dt.Rows.Count];
for (int i = 0; i < dt.Rows.Count; i++)
{
vd[i] = new VolumeData(dt.Rows[i]);
}
return(vd);
}
public VolumeData SelectSummaryVolume(string officeCode, string department, string ymPara)
{
VolumeData vold = new VolumeData();
SqlHandling sh = new SqlHandling();
DataTable dt = sh.SelectFullDescription(sumPara + " OfficeCode = '" + officeCode + "' AND Department = '" + department + "' AND YearMonth " + ymPara);
//DataTable dt = sh.SelectFullDescription( sumPara + " OfficeCode = '" + officeCode + "' AND Department = '" + department + "' AND YearMonth " + ymPara + " AND TaskStat != 3" ); // 休止以外
if (dt.Rows.Count == 0)
{
return(null);
}
DataRow dr = dt.Rows[0];
vold = setVolumeData(dr);
vold.OfficeCode = officeCode;
vold.Department = department;
return(vold);
}
public VolumeData SelectSummaryVolume(string officeCode, string department, int yymm)
{
VolumeData vold = new VolumeData();
SqlHandling sh = new SqlHandling();
DataTable dt = sh.SelectFullDescription(sumPara + " OfficeCode = '" + officeCode + "' AND Department = '" + department + "' AND YearMonth = " + yymm);
//if (dt == null) return vold;
if (dt.Rows.Count == 0)
{
return(null);
}
DataRow dr = dt.Rows[0];
vold = setVolumeData(dr);
vold.OfficeCode = officeCode;
vold.Department = department;
vold.YearMonth = yymm;
return(vold);
}
public bool UpdateYearVolume(VolumeData vd)
{
using (TransactionScope tran = new TransactionScope())
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
try
{
conn.Open();
SqlCommand cmd;
if (vd.Department != "")
{
cmd = new SqlCommand(updSqlYear, conn);
}
else
{
cmd = new SqlCommand(updSqlYear1, conn);
}
cmd = parametersSqlYearDbType(cmd);
cmd = parametersYearValue(cmd, vd);
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);
}
public VolumeData[] SelectVolumeDate(string strTaskCode, int intYearMonth, string strOfficeCode, string strDepartment)
{
SqlHandling sh = new SqlHandling("D_Volume");
string strSqlDepartment = "";
if (strDepartment != "")
{
strSqlDepartment = " AND Department = '" + strDepartment + "'";
}
DataTable dt = sh.SelectAllData("WHERE TaskCode = '" + strTaskCode + "' AND OfficeCode = '" + strOfficeCode + "'" + strSqlDepartment + " AND YearMonth Like '" + intYearMonth + "%" + "'");
if (dt == null || dt.Rows.Count < 1)
{
return(null);
}
VolumeData[] vd = new VolumeData[dt.Rows.Count];
for (int i = 0; i < dt.Rows.Count; i++)
{
vd[i] = new VolumeData(dt.Rows[i]);
}
return(vd);
}
public VolumeData[] SelectVolumeData(string taskCode, int yearMonth, string officeCode, string department)
{
SqlHandling sh = new SqlHandling("D_Volume");
string sqlDepartment = "";
if (department != "")
{
sqlDepartment = " AND Department = '" + department + "'";
}
DataTable dt = sh.SelectAllData("WHERE TaskCode = '" + taskCode + "' AND OfficeCode = '" + officeCode + "'" + sqlDepartment + " AND YearMonth = " + yearMonth);
if (dt == null || dt.Rows.Count < 1)
{
return(null);
}
VolumeData[] vd = new VolumeData[dt.Rows.Count];
for (int i = 0; i < dt.Rows.Count; i++)
{
vd[i] = new VolumeData(dt.Rows[i]);
}
return(vd);
}
private SqlCommand parametersValue(SqlCommand cmd, VolumeData vd)
{
cmd.Parameters["@TaskCode"].Value = vd.TaskCode;
cmd.Parameters["@OfficeCode"].Value = vd.OfficeCode;
cmd.Parameters["@Department"].Value = DBNull.Value;
if (vd.Department != "")
{
cmd.Parameters["@Department"].Value = vd.Department;
}
cmd.Parameters["@YearMonth"].Value = vd.YearMonth;
cmd.Parameters["@MonthlyVolume"].Value = DBNull.Value;
cmd.Parameters["@MonthlyVolume"].Value = DBNull.Value;
if (vd.MonthlyVolume != null)
{
cmd.Parameters["@MonthlyVolume"].Value = vd.MonthlyVolume;
}
cmd.Parameters["@VolUncomp"].Value = DBNull.Value;
if (vd.VolUncomp != null)
{
cmd.Parameters["@VolUncomp"].Value = vd.VolUncomp;
}
cmd.Parameters["@VolClaimRem"].Value = DBNull.Value;
if (vd.VolClaimRem != null)
{
cmd.Parameters["@VolClaimRem"].Value = vd.VolClaimRem;
}
cmd.Parameters["@VolClaim"].Value = DBNull.Value;
if (vd.VolClaim != null)
{
cmd.Parameters["@VolClaim"].Value = vd.VolClaim;
}
cmd.Parameters["@MonthlyClaim"].Value = DBNull.Value;
if (vd.MonthlyClaim != null)
{
cmd.Parameters["@MonthlyClaim"].Value = vd.MonthlyClaim;
}
cmd.Parameters["@VolPaid"].Value = DBNull.Value;
if (vd.VolPaid != null)
{
cmd.Parameters["@VolPaid"].Value = vd.VolPaid;
}
cmd.Parameters["@BalanceClaim"].Value = DBNull.Value;
if (vd.BalanceClaim != null)
{
cmd.Parameters["@BalanceClaim"].Value = vd.BalanceClaim;
}
cmd.Parameters["@BalanceIncom"].Value = DBNull.Value;
if (vd.BalanceIncom != null)
{
cmd.Parameters["@BalanceIncom"].Value = vd.BalanceIncom;
}
cmd.Parameters["@MonthlyCost"].Value = DBNull.Value;
if (vd.MonthlyCost != null)
{
cmd.Parameters["@MonthlyCost"].Value = vd.MonthlyCost;
}
cmd.Parameters["@ClaimDate"].Value = DBNull.Value;
if (vd.ClaimDate != null)
{
cmd.Parameters["@ClaimDate"].Value = vd.ClaimDate;
}
cmd.Parameters["@PaidDate"].Value = DBNull.Value;
if (vd.PaidDate != null)
{
cmd.Parameters["@PaidDate"].Value = vd.PaidDate;
}
cmd.Parameters["@CarryOverPlanned"].Value = DBNull.Value;
if (vd.CarryOverPlanned != null)
{
cmd.Parameters["@CarryOverPlanned"].Value = vd.CarryOverPlanned;
}
cmd.Parameters["@Comment"].Value = vd.Comment;
cmd.Parameters["@TaskStat"].Value = vd.TaskStat;
cmd.Parameters["@Note"].Value = vd.Note;
return(cmd);
}
public bool ClearPartWorkReport(int volumeID, int crID)
{
VolumeData vd = loadVolume(volumeID);
return(UpdateVolume(vd));
}
private SqlCommand parametersYearValue(SqlCommand cmd, VolumeData vd)
{
cmd.Parameters["@TaskCode"].Value = vd.TaskCode;
cmd.Parameters["@OfficeCode"].Value = vd.OfficeCode;
cmd.Parameters["@Department"].Value = DBNull.Value;
if (vd.Department != "")
{
cmd.Parameters["@Department"].Value = vd.Department;
}
cmd.Parameters["@YearMonth"].Value = vd.YearMonth;
cmd.Parameters["@Volume"].Value = DBNull.Value;
if (vd.MonthlyVolume != null)
{
cmd.Parameters["@Volume"].Value = vd.MonthlyVolume;
}
cmd.Parameters["@VolUncomp"].Value = DBNull.Value;
if (vd.VolUncomp != null)
{
cmd.Parameters["@VolUncomp"].Value = vd.VolUncomp;
}
cmd.Parameters["@VolClaimRem"].Value = DBNull.Value;
if (vd.VolClaimRem != null)
{
cmd.Parameters["@VolClaimRem"].Value = vd.VolClaimRem;
}
cmd.Parameters["@VolClaim"].Value = DBNull.Value;
if (vd.VolClaim != null)
{
cmd.Parameters["@VolClaim"].Value = vd.VolClaim;
}
cmd.Parameters["@Claim"].Value = DBNull.Value;
if (vd.MonthlyClaim != null)
{
cmd.Parameters["@Claim"].Value = vd.MonthlyClaim;
}
cmd.Parameters["@VolPaid"].Value = DBNull.Value;
if (vd.VolPaid != null)
{
cmd.Parameters["@VolPaid"].Value = vd.VolPaid;
}
cmd.Parameters["@BalanceClaim"].Value = DBNull.Value;
if (vd.BalanceClaim != null)
{
cmd.Parameters["@BalanceClaim"].Value = vd.BalanceClaim;
}
cmd.Parameters["@BalanceIncom"].Value = DBNull.Value;
if (vd.BalanceIncom != null)
{
cmd.Parameters["@BalanceIncom"].Value = vd.BalanceIncom;
}
cmd.Parameters["@Deposit1"].Value = DBNull.Value;
if (vd.Deposit1 != null)
{
cmd.Parameters["@Deposit1"].Value = vd.Deposit1;
}
cmd.Parameters["@Cost"].Value = DBNull.Value;
if (vd.MonthlyCost != null)
{
cmd.Parameters["@Cost"].Value = vd.MonthlyCost;
}
cmd.Parameters["@ClaimDate"].Value = DBNull.Value;
if (vd.ClaimDate != null)
{
cmd.Parameters["@ClaimDate"].Value = vd.ClaimDate;
}
cmd.Parameters["@PaidDate"].Value = DBNull.Value;
if (vd.PaidDate != null)
{
cmd.Parameters["@PaidDate"].Value = vd.PaidDate;
}
cmd.Parameters["@Deposit2"].Value = DBNull.Value;
if (vd.Deposit2 != null)
{
cmd.Parameters["@Deposit2"].Value = vd.Deposit2;
}
return(cmd);
}