//--------------------------------------------------------//
// Method
//--------------------------------------------------------//
public string NumberingOrder(string officeCode, string department)
{
string orderNo = department;
if (officeCode != "H")
{
orderNo = "A";
}
orderNo += util.TruncateByteRight(Convert.ToString(DHandling.FiscalYear(DateTime.Today)), 2);
orderNo += officeCode;
SqlHandling sh = new SqlHandling("M_Office");
DataTable dt = sh.SelectAllData("WHERE OfficeCode = '" + officeCode + "'");
if (dt == null)
{
return(null);
}
DataRow dr;
dr = dt.Rows[0];
int orderSeqNo = Convert.ToInt32(dr["OrderSeqNo"]);
orderSeqNo++;
orderNo += orderSeqNo.ToString("000");
if (!office_Update(officeCode, orderSeqNo))
{
return(null);
}
return(orderNo);
}
static private SqlCommand planningContParaValue(SqlCommand cmd, DataGridViewRow dgvr)
{
cmd.Parameters["@iCod"].Value = Convert.ToString(dgvr.Cells[1].Value);
cmd.Parameters["@item"].Value = Convert.ToString(dgvr.Cells[2].Value);
cmd.Parameters["@iDtl"].Value = Convert.ToString(dgvr.Cells[3].Value);
cmd.Parameters["@qty"].Value = Convert.ToDecimal(dgvr.Cells[4].Value);
cmd.Parameters["@unit"].Value = Convert.ToString(dgvr.Cells[5].Value);
// Wakamatsu 20170329
//cmd.Parameters["@cost0"].Value = DHandling.ToRegDecimal( Convert.ToString( dgvr.Cells[6].Value ) );
//cmd.Parameters["@cost1"].Value = DHandling.ToRegDecimal( Convert.ToString( dgvr.Cells[8].Value ) );
//cmd.Parameters["@cost2"].Value = DHandling.ToRegDecimal( Convert.ToString( dgvr.Cells[10].Value ) );
Calculation calc = new Calculation();
if (calc.ExtractCalcWord(Convert.ToString(dgvr.Cells["Item"].Value)) == Sign.Discount)
{
cmd.Parameters["@cost0"].Value = DHandling.ToRegDecimal(Convert.ToString(dgvr.Cells[7].Value));
cmd.Parameters["@cost1"].Value = DHandling.ToRegDecimal(Convert.ToString(dgvr.Cells[9].Value));
cmd.Parameters["@cost2"].Value = DHandling.ToRegDecimal(Convert.ToString(dgvr.Cells[11].Value));
}
else
{
cmd.Parameters["@cost0"].Value = DHandling.ToRegDecimal(Convert.ToString(dgvr.Cells[6].Value));
cmd.Parameters["@cost1"].Value = DHandling.ToRegDecimal(Convert.ToString(dgvr.Cells[8].Value));
cmd.Parameters["@cost2"].Value = DHandling.ToRegDecimal(Convert.ToString(dgvr.Cells[10].Value));
}
// Wakamatsu 20170329
return(cmd);
}
static private SqlCommand estContParaValue(SqlCommand cmd, DataGridViewRow dgvr)
{
cmd.Parameters["@iCod"].Value = Convert.ToString(dgvr.Cells["ItemCode"].Value);
cmd.Parameters["@item"].Value = Convert.ToString(dgvr.Cells["Item"].Value);
cmd.Parameters["@iDtl"].Value = Convert.ToString(dgvr.Cells["ItemDetail"].Value);
cmd.Parameters["@qty"].Value = Convert.ToDecimal(dgvr.Cells["Quantity"].Value);
cmd.Parameters["@unit"].Value = Convert.ToString(dgvr.Cells["Unit"].Value);
// Wakamatsu 20170324
//cmd.Parameters["@cost"].Value = DHandling.ToRegDecimal(Convert.ToString(dgvr.Cells["Cost"].Value));
Calculation calc = new Calculation();
if (calc.ExtractCalcWord(Convert.ToString(dgvr.Cells["Item"].Value)) == Sign.Discount)
{
// Wakamatsu 20170327
//cmd.Parameters["@cost"].Value = DHandling.ToRegDecimal(Convert.ToString(dgvr.Cells["Amount"].Value));
cmd.Parameters["@cost"].Value = SignConvert(Convert.ToString(dgvr.Cells["Amount"].Value));
}
// Wakamatsu 20170327
else
{
cmd.Parameters["@cost"].Value = DHandling.ToRegDecimal(Convert.ToString(dgvr.Cells["Cost"].Value));
}
// Wakamatsu 20170324
cmd.Parameters["@note"].Value = Convert.ToString(dgvr.Cells["Note"].Value);
return(cmd);
}
public TaskCodeNameData[] SelectTaskCodeNameFromOsWkReport(string pCode, DateTime dateFr)
{
DateTime dateTo = DHandling.EndOfMonth(dateFr);
string selParam = "DISTINCT WR.TaskCode AS TaskCode, TI.TaskName AS TaskName, TI.LeaderMCode AS LeaderMCode FROM "
+ "D_TaskInd TI INNER JOIN D_OsWkReport WR ON TI.TaskCode = WR.TaskCode "
+ "WHERE (WR.ReportDate BETWEEN '" + dateFr + "' AND '" + dateTo + "') AND WR.PartnerCode = '" + pCode + "' ORDER BY WR.TaskCode";
SqlHandling sh = new SqlHandling();
DataTable dt = sh.SelectFullDescription(selParam);
if (dt == null || dt.Rows.Count < 1)
{
return(null);
}
TaskCodeNameData[] tcd = new TaskCodeNameData[dt.Rows.Count];
DataRow dr;
for (int i = 0; i < dt.Rows.Count; i++)
{
dr = dt.Rows[i];
tcd[i] = new TaskCodeNameData();
tcd[i].TaskCode = Convert.ToString(dr["TaskCode"]);
tcd[i].TaskName = Convert.ToString(dr["TaskName"]);
tcd[i].LeaderMCode = Convert.ToString(dr["LeaderMCode"]);
}
return(tcd);
}
public string CreateTaskBaseCode(string officeCode)
{
int seqNo = 0;
string taskBaseCode = "";
string selectSql = "SELECT OrderSeqNo FROM M_Office WHERE OfficeCode = @oCod";
string updateSql = "UPDATE M_Office SET OrderSeqNo = @oSeq WHERE OfficeCode = @oCod";
using (TransactionScope tran = new TransactionScope())
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
try
{
conn.Open();
SqlCommand cmd = new SqlCommand(selectSql, conn);
cmd.Parameters.Add("@oCod", SqlDbType.Char);
SqlCommand cmd1 = new SqlCommand(updateSql, conn);
cmd1.Parameters.Add("@oCod", SqlDbType.Char);
cmd1.Parameters.Add("@oSeq", SqlDbType.Int);
cmd.Parameters["@oCod"].Value = officeCode;
SqlDataReader dr = TryExReader(conn, cmd);
if (!dr.HasRows)
{
return(null);
}
while (dr.Read())
{
seqNo = Convert.ToInt32(dr["OrderSeqNo"]);
}
dr.Close();
cmd1.Parameters["@oCod"].Value = officeCode;
seqNo++;
cmd1.Parameters["@oSeq"].Value = seqNo;
if (TryExecute(conn, cmd1) < 0)
{
return(null);
}
}
catch (SqlException sqle)
{
MessageBox.Show("SQLエラー errorno " + Convert.ToString(sqle.Number) + " " + sqle.Message);
conn.Close();
return(null);
}
conn.Close();
tran.Complete();
}
DateTime nowdt = DateTime.Now;
taskBaseCode = (DHandling.FiscalYear(nowdt) - 2000).ToString() + officeCode + seqNo.ToString("000");
return(taskBaseCode);
}
static private SqlCommand outsourceContParaValue(SqlCommand cmd, DataGridViewRow dgvr)
{
cmd.Parameters["@iCod"].Value = Convert.ToString(dgvr.Cells["ItemCode"].Value);
cmd.Parameters["@item"].Value = Convert.ToString(dgvr.Cells["Item"].Value);
cmd.Parameters["@iDtl"].Value = Convert.ToString(dgvr.Cells["ItemDetail"].Value);
cmd.Parameters["@qty"].Value = Convert.ToDecimal(dgvr.Cells["Quantity"].Value);
cmd.Parameters["@unit"].Value = Convert.ToString(dgvr.Cells["Unit"].Value);
cmd.Parameters["@cost"].Value = DHandling.ToRegDecimal(Convert.ToString(dgvr.Cells["Cost"].Value));
cmd.Parameters["@note"].Value = DHandling.ToRegDecimal(Convert.ToString(dgvr.Cells["Note"].Value));
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);
}
public DataTable SelectOsWkReportPartnerCode(DateTime dateFr, string officeCode, string department)
{
DateTime dateTo = DHandling.EndOfMonth(dateFr);
SqlHandling sh = new SqlHandling();
DataTable dt = sh.SelectFull_Core("DISTINCT PartnerCode FROM D_OsWkReport WHERE OfficeCode = '" + officeCode + "' AND Department = '" + department
+ "' AND ( ReportDate BETWEEN '" + dateFr + "' AND '" + dateTo + "') ORDER BY PartnerCode");
if (dt == null || dt.Rows.Count < 1)
{
return(null);
}
return(dt);
}
// 予算
public bool PlanningCont_StoreDgv(DataTable dt, DataGridView dgv)
{
DataRow dr;
decimal qty;
for (int i = 0; i < dt.Rows.Count; i++)
{
dr = dt.Rows[i];
storeItemsToDgv(dgv.Rows[i], dr);
qty = Convert.ToDecimal(dr["Quantity"]);
if (Convert.ToString(dr["Unit"]) != "")
{
dgv.Rows[i].Cells["Quantity"].Value = qty;
dgv.Rows[i].Cells["Unit"].Value = Convert.ToString(dr["Unit"]);
dgv.Rows[i].Cells["Cost0"].Value = DHandling.DecimaltoStr(Convert.ToDecimal(dr["Cost0"]), "#,0");
dgv.Rows[i].Cells["Amount0"].Value = DHandling.DecimaltoStr(qty * Convert.ToDecimal(dr["Cost0"]), "#,0");
dgv.Rows[i].Cells["Cost1"].Value = DHandling.DecimaltoStr(Convert.ToDecimal(dr["Cost1"]), "#,0");
dgv.Rows[i].Cells["Amount1"].Value = DHandling.DecimaltoStr(qty * Convert.ToDecimal(dr["Cost1"]), "#,0");
dgv.Rows[i].Cells["Cost2"].Value = DHandling.DecimaltoStr(Convert.ToDecimal(dr["Cost2"]), "#,0");
dgv.Rows[i].Cells["Amount2"].Value = DHandling.DecimaltoStr(qty * Convert.ToDecimal(dr["Cost2"]), "#,0");
}
}
return(true);
}
//---------------------------------------------------------------------
// SubRoutine
//---------------------------------------------------------------------
private static string decFormat(decimal decNum)
{
return(DHandling.DecimaltoStr(decNum, "#,0"));
}
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);
}
private string editTextLine()
{
// 2018.01 asakawa 数値がマイナスの場合の出力形式の修正のために一部変更
string textLine = "";
// 伝区
textLine += "0";
// 年号
textLine += "1";
// 売上年月日
string date = ReportDate.ToShortDateString();
textLine += util.TruncateByteRight(DHandling.RemoveNoNum(date), 6); // yyyymmdd →yymmdd
// 請求年月日
textLine += util.TruncateByteRight(DHandling.RemoveNoNum(date), 6); // yyyymmdd →yymmdd
// 伝票番号 6桁、0Padding
textLine += util.PaddingInBytes(Convert.ToString(SlipNo), "Number", 6);
// 得意先コード（業務No)
textLine += TaskCode;
// 得意先名
char[] strChar = TaskName.ToCharArray();
for (int i = 0; i < strChar.Length; i++)
{
if (strChar[i] == ' ')
{
strChar[i] = ' ';
}
}
TaskName = new string( strChar );
//string taskData = util.FormFixedByteLengthLeft("(" + CostType + ")" + TaskName, 68);
string taskData = util.FormFixedByteLengthLeft(TaskName, 68);
textLine += util.TruncateByteLeft(taskData, 40);
// 先方担当者名
textLine += util.TruncateByteRight(taskData, 28);
// 部門コード
int dIdx = Array.IndexOf(sdKeyArray, (OfficeCode + util.SubstringByte(TaskCode, 0, 1)));
if (dIdx < 0)
{
dIdx = Array.IndexOf(sdKeyArray, (OfficeCode + " "));
}
textLine += sDeptArray[dIdx];
// 担当者コード
//string pCode = Conv.ResizeMemberCode(MemberCode.TrimEnd(),2);
textLine += Conv.ResizeMemberCode(LeaderMCode.TrimEnd(), 2);
// 摘要コード
textLine += "00";
// 摘要名
textLine += util.PaddingInBytes(" ", "Char", 30);
// 商品コード
textLine += util.SubstringByte(ItemCode, 0, 4);
// マスター区分
textLine += "0";
// 品名
if (ItemCode.TrimEnd() == "K999")
{
char checkChar = ' ';
if (Item != "")
{
checkChar = Item[0];
}
string kItem = (checkChar == '●') ? Item : kMark + Item;
textLine += util.FormFixedByteLengthLeft(kItem, 36);
}
else
{
textLine += util.FormFixedByteLengthLeft(Item, 36);
}
//textLine += util.PaddingInBytes(Item, "Char", 36);
//textLine += util.FormFixedByteLengthLeft(Item, 36);
// 区
textLine += "0";
// 入数
textLine += "0000";
// 箱数
textLine += "00000";
// 数量
if (Quantity == 0)
{
Quantity = 1; // 0では商魂汎用データ作成時にエラーになる
}
// 2018.01 asakawa マイナス値の場合の出力形式の修正のために変更
// textLine += util.PaddingInBytes( Convert.ToString( Quantity ), "Number", 9 );
if (Quantity < 0)
{
textLine += "-";
decimal qq = Quantity * (-1);
textLine += util.PaddingInBytes(Convert.ToString(qq), "Number", 8);
}
else
{
textLine += util.PaddingInBytes(Convert.ToString(Quantity), "Number", 9);
}
// 単位
textLine += util.PaddingInBytes(Unit, "Char", 4);
// 単価
// 2018.01 asakawa マイナス値の場合の出力形式の修正のために変更
// textLine += util.PaddingInBytes( UnitPrice.ToString( "0" ), "Number", 10 );
if (UnitPrice < 0)
{
textLine += "-";
decimal pp = UnitPrice * (-1);
// textLine += util.PaddingInBytes(Convert.ToString(pp), "Number", 9); // 小数点以下も出力される
textLine += util.PaddingInBytes(pp.ToString("0"), "Number", 9);
}
else
{
textLine += util.PaddingInBytes(UnitPrice.ToString("0"), "Number", 10);
}
// 売上金額
int sales = Convert.ToInt32(Quantity * UnitPrice);
// 2018.01 asakawa マイナス値の場合の出力形式の修正のために変更
// textLine += util.PaddingInBytes( Convert.ToString( sales ), "Number", 10 );
if (sales < 0)
{
textLine += "-";
textLine += util.PaddingInBytes(Convert.ToString(sales * (-1)), "Number", 9);
}
else
{
textLine += util.PaddingInBytes(Convert.ToString(sales), "Number", 10);
}
// 原単価
textLine += util.PaddingInBytes("0", "Number", 9);
// 原価額
textLine += util.PaddingInBytes("0", "Number", 10);
// 税区分
textLine += "0";
// 税込区分
textLine += "0";
// 備考区分
textLine += "1"; //備考区分が0:備考は数字、1:文字1
// 備考
textLine += util.PaddingInBytes(" ", "Char", 9);
// 同時入荷区分
textLine += "0";
return(textLine);
}
public TaskData SelectTaskData(string taskCode)
{
SqlHandling sh = new SqlHandling("D_Task");
DataTable dt = sh.SelectAllData("WHERE OldVerMark = 0 AND TaskBaseCode = '" + DHandling.NumberOfCharacters(taskCode, 1) + "'");
if (dt == null || dt.Rows.Count < 1)
{
return(null);
}
TaskData td = new TaskData(dt.Rows[0]);
return(td);
}
public TaskCodeNameData[] SelectTaskCodeNameFromCostReport(DateTime dateFr, DateTime dateTo, string scCode, string officeCode)
{
string selParam = "DISTINCT TaskCode FROM D_CostReport WHERE ( ReportDate BETWEEN '" + dateFr + "' AND '" + dateTo + "') "
+ "AND OfficeCode = '" + officeCode + "' AND ";
selParam += scCode[0] == 'F' ? "SubCoCode = " : "ItemCode = ";
selParam += "'" + scCode + "' ORDER BY TaskCode";
SqlHandling sh = new SqlHandling();
DataTable dt = sh.SelectFullDescription(selParam);
if (dt == null || dt.Rows.Count < 1)
{
return(null);
}
string[] taskCdArray = new string[dt.Rows.Count];
DataRow dr;
for (int i = 0; i < dt.Rows.Count; i++)
{
dr = dt.Rows[i];
taskCdArray[i] = Convert.ToString(dr["TaskCode"]);
}
StringUtility utl = new StringUtility();
TaskIndData tid = new TaskIndData();
TaskCodeNameData[] tcd = new TaskCodeNameData[taskCdArray.Length];
for (int i = 0; i < taskCdArray.Length; i++)
{
tcd[i] = new TaskCodeNameData();
tcd[i].TaskCode = taskCdArray[i];
tid = tid.SelectTaskIndData(utl.SubstringByte(tcd[i].TaskCode, 2, 3) == "999"
? utl.SubstringByte(tcd[i].TaskCode, 0, 1) + Convert.ToString(DHandling.FisicalYear() - 2000) + utl.SubstringByte(tcd[i].TaskCode, 1, 1) + "999"
: tcd[i].TaskCode);
tcd[i].TaskName = tid.TaskName;
tcd[i].LeaderMCode = tid.LeaderMCode;
}
return(tcd);
}