public object Clone()
{
CostReportData cloneData = new CostReportData();
cloneData.CostReportID = this.CostReportID;
cloneData.SlipNo = this.SlipNo;
cloneData.TaskCode = this.TaskCode;
cloneData.OfficeCode = this.OfficeCode;
cloneData.Department = this.Department;
cloneData.ReportDate = this.ReportDate;
cloneData.ItemCode = this.ItemCode;
cloneData.Item = this.Item;
cloneData.UnitPrice = this.UnitPrice;
cloneData.Quantity = this.Quantity;
cloneData.Cost = this.Cost;
cloneData.Unit = this.Unit;
cloneData.CustoCode = this.CustoCode;
cloneData.SubCoCode = this.SubCoCode;
cloneData.Subject = this.Subject;
cloneData.MemberCode = this.MemberCode;
cloneData.LeaderMCode = this.LeaderMCode;
cloneData.SalesMCode = this.SalesMCode;
cloneData.AccountCode = this.AccountCode;
cloneData.Note = this.Note;
cloneData.CoTaskCode = this.CoTaskCode;
cloneData.Publisher = this.Publisher;
return(cloneData);
}
private SqlCommand parametersValueA(SqlCommand cmd, CostReportData crd)
{
cmd.Parameters["@slip"].Value = crd.SlipNo; // 0
cmd.Parameters["@tCod"].Value = crd.TaskCode; // 1
cmd.Parameters["@oCod"].Value = crd.OfficeCode; // 2
cmd.Parameters["@dept"].Value = crd.Department; // 3
cmd.Parameters["@qty"].Value = crd.Quantity; // 8
cmd.Parameters["@cost"].Value = crd.Cost; // 9
cmd.Parameters["@lCod"].Value = crd.LeaderMCode; // 15
return(cmd);
}
public CostReportData[] SelectCostReportItemCode(string officeCode, string taskCode, int fYear)
{
//string sqlStr = " DISTINCT LEFT(ItemCode,4) AS iCod, Item, MemberCode FROM D_CostReport WHERE OfficeCode = '" + officeCode + "' AND TaskCode = '" + taskCode + "' AND (ItemCode LIKE 'A%' OR ItemCode LIKE 'B%')"
// + " AND ( ReportDate BETWEEN '" + DHandling.FisicalYearStartDate( fYear ) + "' AND '" + DHandling.FisicalYearEndDate( fYear ) + "') ORDER BY LEFT(ItemCode,4)";
//string sqlStr = " ItemCode, Item, MemberCode FROM D_CostReport WHERE OfficeCode = '" + officeCode + "' AND TaskCode = '" + taskCode + "' AND (ItemCode LIKE 'A%' OR ItemCode LIKE 'B%')"
// + " AND ( ReportDate BETWEEN '" + DHandling.FisicalYearStartDate( fYear ) + "' AND '" + DHandling.FisicalYearEndDate( fYear ) + "') ORDER BY SUBSTRING LEFT(ItemCode,4)";
string sqlStr = " ItemCode, Item, MemberCode FROM D_CostReport WHERE OfficeCode = '" + officeCode + "' AND TaskCode = '" + taskCode + "' AND (ItemCode LIKE 'A%' OR ItemCode LIKE 'B%')"
+ " AND ( ReportDate BETWEEN '" + DHandling.FisicalYearStartDate(fYear) + "' AND '" + DHandling.FisicalYearEndDate(fYear) + "') ORDER BY ItemCode";
SqlHandling sh = new SqlHandling();
DataTable dt = sh.SelectFull_Core(sqlStr);
if (dt == null)
{
return(null);
}
string wkItemCode = "";
int rowIdx = 0;
DataRow dr;
for (int i = 0; i < dt.Rows.Count; i++)
{
dr = dt.Rows[i];
if (wkItemCode != (Convert.ToString(dr["ItemCode"])).TrimEnd())
{
rowIdx++;
wkItemCode = (Convert.ToString(dr["ItemCode"])).TrimEnd();
}
}
CostReportData[] crd = new CostReportData[rowIdx];
wkItemCode = "";
rowIdx = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
dr = dt.Rows[i];
if (wkItemCode != (Convert.ToString(dr["ItemCode"])).TrimEnd())
{
wkItemCode = (Convert.ToString(dr["ItemCode"])).TrimEnd();
crd[rowIdx] = new CostReportData();
crd[rowIdx].ItemCode = wkItemCode;
crd[rowIdx].Item = Convert.ToString(dr["Item"]);
rowIdx++;
}
}
return(crd);
}
private SqlCommand parametersValueB(SqlCommand cmd, CostReportData crd)
{
cmd.Parameters["@rDat"].Value = crd.ReportDate; // 4
cmd.Parameters["@iCod"].Value = crd.ItemCode; // 5
cmd.Parameters["@item"].Value = crd.Item; // 6
cmd.Parameters["@uPri"].Value = crd.UnitPrice; // 7
cmd.Parameters["@unit"].Value = crd.Unit; // 10
cmd.Parameters["@cust"].Value = crd.CustoCode; // 11
cmd.Parameters["@subc"].Value = crd.SubCoCode; // 12
cmd.Parameters["@subj"].Value = crd.Subject; // 13
cmd.Parameters["@mCod"].Value = crd.MemberCode; // 14
cmd.Parameters["@sCod"].Value = crd.SalesMCode; // 16
cmd.Parameters["@aCod"].Value = crd.AccountCode; // 17
cmd.Parameters["@note"].Value = crd.Note; // 18
cmd.Parameters["@cTCd"].Value = crd.CoTaskCode; // 19
//cmd.Parameters["@cTCd"].Value = String.IsNullOrEmpty(crd.CoTaskCode)?"":crd.CoTaskCode; // 19
return(cmd);
}
public CostReportData[] SelectCostReport(int slipNo)
{
SqlHandling sh = new SqlHandling("D_CostReport");
DataTable dt = sh.SelectAllData("WHERE SlipNo = " + slipNo);
if (dt == null || dt.Rows.Count < 1)
{
return(null);
}
CostReportData[] crd = new CostReportData[dt.Rows.Count];
for (int i = 0; i < dt.Rows.Count; i++)
{
crd[i] = new CostReportData(dt.Rows[i]);
}
return(crd);
}
public Decimal SumMonthlyQuantity(string officeCode, string itemCode, DateTime month)
{
string sqlStr = "SUM( Quantity ) AS SumQty FROM D_CostReport "
+ "WHERE OfficeCode = '" + officeCode + "' AND "
+ "ItemCode = '" + itemCode + "' AND "
+ "( ReportDate BETWEEN '" + month + "' AND '" + month.EndOfMonth() + "' )";
SqlHandling sh = new SqlHandling();
DataTable dt = sh.SelectFull_Core(sqlStr);
if (dt == null)
{
return(0);
}
CostReportData crd = new CostReportData();
DataRow dr = dt.Rows[0];
return((string.IsNullOrEmpty(Convert.ToString(dr["SumQty"]))) ? 0 : Convert.ToDecimal(dr["SumQty"]));
}
public CostReportData[] SelectCostReport(DateTime dateFr, DateTime dateTo, string taskCode, string officeCode, string department)
{
SqlHandling sh = new SqlHandling("D_CostReport");
DataTable dt = sh.SelectAllData("WHERE OfficeCode = '" + officeCode + "' AND Department = '" + department + "' AND TaskCode = '" + taskCode
+ "' AND ( ReportDate BETWEEN '" + dateFr + "' AND '" + dateTo + "') ORDER BY ReportDate");
if (dt == null || dt.Rows.Count < 1)
{
return(null);
}
CostReportData[] crd = new CostReportData[dt.Rows.Count];
for (int i = 0; i < dt.Rows.Count; i++)
{
crd[i] = new CostReportData(dt.Rows[i]);
}
return(crd);
}
public bool StoreOsWkReportAndDetail(OsWkDetailData[] wdtl)
{
crd = new CostReportData();
using (TransactionScope tran = new TransactionScope())
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
try
{
conn.Open();
SqlCommand cmd = new SqlCommand(insSql + seliDSql, conn);
cmd = addParaRep(cmd);
cmd = addValueRep(cmd);
if ((OsWkReportID = TryExScalar(conn, cmd)) < 0)
{
return(false);
}
cmd = new SqlCommand(OsWkDetailData.sqlInsDtl, conn);
cmd = OsWkDetailData.addCmdPara(cmd);
for (int i = 0; i < wdtl.Length; i++)
{
wdtl[i].OsWkReportID = OsWkReportID;
cmd = OsWkDetailData.addValue(cmd, wdtl[i]);
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 Decimal SumMonthlyCost(string taskCode, string officeCode, DateTime month)
{
string sqlStr = "SUM( Cost ) AS SumCost FROM D_CostReport "
+ "WHERE TaskCode = '" + taskCode + "' AND "
+ "OfficeCode = '" + officeCode + "' AND "
+ "( LEFT(ItemCode, 1 ) = 'A' OR LEFT( ItemCode, 1 ) = 'B' ) AND "
+ "( ReportDate BETWEEN '" + month + "' AND '" + month.EndOfMonth() + "' )";
SqlHandling sh = new SqlHandling();
DataTable dt = sh.SelectFull_Core(sqlStr);
if (dt == null)
{
return(0);
}
CostReportData crd = new CostReportData();
DataRow dr = dt.Rows[0];
return((string.IsNullOrEmpty(Convert.ToString(dr["SumCost"]))) ? 0 : Convert.ToDecimal(dr["SumCost"]));
}
private SqlCommand parametersValue(SqlCommand cmd, CostReportData crd)
{
cmd = parametersValueA(cmd, crd);
cmd = parametersValueB(cmd, crd);
return(cmd);
}
public int CreateCostReportDataByCSVData(string fileName, string officeCode)
{
int procCount = 0;
int repoDate;
string[] codeArray = new string[2];
int[] getaArray = new int[] { 0, 300000, 500000, 700000 };
int geta = getaArray[Conv.OfficeCodeIndex(officeCode)];
StringUtility sutil = new StringUtility();
MembersData md = new MembersData();
CostReportData crd = new CostReportData();
try
{
using (var streamReader = new StreamReader(fileName, System.Text.Encoding.Default))
{
while (!streamReader.EndOfStream)
{
var line = streamReader.ReadLine();
var valArray = line.Split(',');
if (!DHandling.IsNumeric(Convert.ToString(valArray[0])))
{
continue;
}
if (Convert.ToInt32(valArray[4]) > 900000)
{
continue;
}
crd.SlipNo = Convert.ToInt32(valArray[4]) + geta;
repoDate = (Convert.ToInt32(valArray[1]) == 0) ? Convert.ToInt32(valArray[2]) + 19880000 : Convert.ToInt32(valArray[2]) + 20000000;
crd.ReportDate = DateTime.ParseExact(Convert.ToString(repoDate) + "000000", "yyyyMMddHHmmss", null);
crd.TaskCode = Convert.ToString(valArray[5]);
crd.ItemCode = Convert.ToString(valArray[12]);
crd.Item = Convert.ToString(valArray[14]);
crd.Quantity = Convert.ToDecimal(valArray[18]);
crd.Unit = Convert.ToString(valArray[19]);
crd.UnitPrice = Convert.ToDecimal(valArray[20]);
crd.Cost = Convert.ToDecimal(valArray[21]);
crd.Note = Convert.ToString(valArray[27]);
TaskData td = new TaskData();
td = td.SelectTaskData(crd.TaskCode);
if (td != null)
{
crd.CustoCode = td.PartnerCode;
crd.SalesMCode = td.SalesMCode;
}
crd.Subject = sutil.SubstringByte(crd.ItemCode, 0, 1);
crd.SubCoCode = (crd.Subject == "F") ? crd.ItemCode : "";
crd.MemberCode = (Convert.ToInt32(valArray[9]) < 100) ? "0" + Convert.ToString(valArray[9]) : Convert.ToString(valArray[9]);
crd.LeaderMCode = (Convert.ToInt32(valArray[9]) < 100) ? "0" + Convert.ToString(valArray[9]) : Convert.ToString(valArray[9]);
// 事業所および部門
crd.OfficeCode = officeCode;
crd.Department = (crd.OfficeCode == "H") ? Conv.tdHList[Conv.DepartmentCodeIndex(sutil.SubstringByte(crd.TaskCode, 0, 1))]
: Conv.tdBList[Conv.DepartmentCodeIndex(sutil.SubstringByte(crd.TaskCode, 0, 1))];
if (crd.MemberCode != "" && crd.MemberCode == holdMCode)
{
crd.Department = holdDept;
}
else
{
codeArray = md.SelectMembersOffice(crd.MemberCode);
if (codeArray != null)
{
if (codeArray[0] == crd.OfficeCode)
{
crd.Department = codeArray[1];
}
}
holdMCode = crd.MemberCode;
holdDept = crd.Department;
}
crd.AccountCode = "SYO";
crd.CoTaskCode = "";
if (crd.ExistenceSlipNo())
{
continue;
}
// 項目移送
if (!crd.InsertCostReport())
{
return(0);
}
procCount++;
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
procCount = -1;
}
return(procCount);
}