public bool D_EDI_Insert(D_EDI_Entity de, DataTable dt)
{
string sp = "D_EDI_Insert";
command = new SqlCommand(sp, GetConnection());
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = 0;
AddParam(command, "@VendorCD", SqlDbType.VarChar, de.VendorCD);
AddParam(command, "@ImportFile", SqlDbType.VarChar, de.ImportFile);
AddParam(command, "@OrderDetailsSu", SqlDbType.Int, de.OrderDetailsSu.ToString());
AddParam(command, "@ImportDetailsSu", SqlDbType.Int, de.ImportDetailsSu.ToString());
AddParam(command, "@ErrorSu", SqlDbType.Int, de.ErrorSu.ToString());
AddParamForDataTable(command, "@Table", SqlDbType.Structured, dt);
AddParam(command, "@Operator", SqlDbType.VarChar, de.UpdateOperator);
AddParam(command, "@PC", SqlDbType.VarChar, "");
//OUTパラメータの追加
string outPutParam = "@OutEDIImportNO";
command.Parameters.Add(outPutParam, SqlDbType.VarChar, 11);
command.Parameters[outPutParam].Direction = ParameterDirection.Output;
UseTransaction = true;
bool ret = InsertUpdateDeleteData(sp, ref outPutParam);
if (ret)
{
de.EDIImportNO = outPutParam;
}
return(ret);
}
public bool D_EDI_Insert(D_EDI_Entity de, DataTable dt)
{
D_Edi_DL dl = new D_Edi_DL();
return(dl.D_EDI_Insert(de, dt));
}
private void SetData(D_EDI_Entity de, DataTable import, DataTable dt)
{
int rowNo = 1;
foreach (DataRow row in import.Rows)
{
int errKbn = Convert.ToInt16(row[33]);
string errText = GetErrText(errKbn);
string arrivalPlanDate = ediAPI_bl.FormatDate(row[14].ToString());
bool ret = ediAPI_bl.CheckDate(arrivalPlanDate);
if (!ret)
{
arrivalPlanDate = "";
}
dt.Rows.Add(rowNo //EDIImportRows", typeof(int)); //取込行番号
, row[25].ToString().Substring(0, 11) //OrderNO", typeof(string)); //発注番号
, ediAPI_bl.Z_Set(row[25].ToString().Substring(12, 3)) //OrderRows", typeof(int)); //発注明細連番
, arrivalPlanDate == "" ? null : arrivalPlanDate //ArrivalPlanDate", typeof(DateTime)); //入荷予定日
, null //ArrivalPlanMonth", typeof(int)); //入荷予定月
, null //ArrivalPlanCD", typeof(string)); // (4) //入荷予定状況CD
, ediAPI_bl.Z_Set(row[29]) //ArrivalPlanSu", typeof(int)); //入荷予定数
, row[32].ToString() == "" ? null : row[32].ToString() //VendorComment", typeof(string)); // (30) //仕入先コメント
, errKbn //ErrorKBN", typeof(int)); //エラー区分
, errText == "" ? null : errText //ErrorText", typeof(string)); //エラー内容
, row[28].ToString() //EDIOrderNO", typeof(string)); //EDI発注番号
, ediAPI_bl.Z_Set(row[31]) //EDIOrderRows", typeof(int)); //行番号
, row[0].ToString() == "" ? null : row[0].ToString() //CSVRecordKBN", typeof(string)); // (1) //レコード区分
, row[1].ToString() == "" ? null : row[1].ToString() //CSVDataKBN", typeof(string)); //データ区分
, row[2].ToString() == "" ? null : row[2].ToString() //CSVCapitalCD", typeof(string)); // (13) //発注者会社部署CD
, row[3].ToString() == "" ? null : row[3].ToString() //CSVCapitalName", typeof(string)); //発注者企業部署名
, row[4].ToString() == "" ? null : row[4].ToString() //CSVOrderCD", typeof(string)); //受注者会社部署CD
, row[5].ToString() == "" ? null : row[5].ToString() //CSVOrderName", typeof(string)); // (20) //受注者企業部署名
, row[6].ToString() == "" ? null : row[6].ToString() //CSVSalesCD", typeof(string)); //販売店会社部署CD
, row[7].ToString() == "" ? null : row[7].ToString() //CSVSalesName", typeof(string)); // (20) //販売店会社部署名
, row[8].ToString() == "" ? null : row[8].ToString() //CSVDestinationCD", typeof(string)); //出荷先会社部署CD
, row[9].ToString() == "" ? null : row[9].ToString() //CSVDestinationName", typeof(string)); //出荷先会社部署名
, row[10].ToString() == "" ? null : row[10].ToString() //CSVOrderNO", typeof(string)); //発注NO
, row[11].ToString() == "" ? null : row[11].ToString() //CSVOrderRows", typeof(string)); // (5) //発注NO行
, row[12].ToString() == "" ? null : row[12].ToString() //CSVOrderLines", typeof(string)); // (5) //発注NO列
, row[13].ToString() == "" ? null : row[13].ToString() //CSVOrderDate", typeof(string)); // (15) //発注日
, row[14].ToString() == "" ? null : row[14].ToString() //CSVArriveDate", typeof(string)); // (15) //納品日
, row[15].ToString() == "" ? null : row[15].ToString() //CSVOrderKBN", typeof(string)); //発注区分
, row[16].ToString() == "" ? null : row[16].ToString() //CSVMakerItemKBN", typeof(string)); //発注者商品分類
, row[17].ToString() == "" ? null : row[17].ToString() //CSVMakerItem", typeof(string)); // (50) //発注者商品CD
, row[18].ToString() == "" ? null : row[18].ToString() //CSVSKUCD", typeof(string)); //受注者品番
, row[19].ToString() == "" ? null : row[19].ToString() //CSVSizeName", typeof(string)); //メーカー規格1
, row[20].ToString() == "" ? null : row[20].ToString() //CSVColorName", typeof(string)); // (20) //メーカー規格2
, row[21].ToString() == "" ? null : row[21].ToString() //CSVTaniCD", typeof(string)); //単位
, row[22].ToString() == "" ? null : row[22].ToString() //CSVOrderUnitPrice", typeof(string)); //取引単価
, row[23].ToString() == "" ? null : row[23].ToString() //CSVOrderPriceWithoutTax", typeof(string)); //標準上代
, row[24].ToString() == "" ? null : row[24].ToString() //CSVBrandName", typeof(string)); //ブランド略名
, row[25].ToString() == "" ? null : row[25].ToString() //CSVSKUName", typeof(string)); //商品略名
, row[26].ToString() == "" ? null : row[26].ToString() //CSVJanCD", typeof(string)); //JANCD
, row[27].ToString() == "" ? null : row[27].ToString() //CSVOrderSu", typeof(string)); //発注数
, row[28].ToString() == "" ? null : row[28].ToString() //CSVOrderGroupNO", typeof(string)); //予備１（発注グループ番号）
, row[29].ToString() == "" ? null : row[29].ToString() //CSVAnswerSu", typeof(string)); //予備２（引当数）
, row[30].ToString() == "" ? null : row[30].ToString() //CSVNextDate", typeof(string)); //予備３（次回予定日）
, row[31].ToString() == "" ? null : row[31].ToString() //CSVOrderGroupRows", typeof(string)); //予備４（発注グループ行）
, row[32].ToString() == "" ? null : row[32].ToString() //CSVErrorMessage", typeof(string)); //予備５（エラーメッセージ）
);
rowNo++;
}
}
public DataTable CSVToTable(string filePath, D_EDI_Entity dee)
{
DataTable csvData = new DataTable();
int count = 1;
string firstColHeader = "2"; //Todo:何を設定するかは後で
try
{
using (TextFieldParser csvReader = new TextFieldParser(filePath, Encoding.GetEncoding(932), true))
{
csvReader.SetDelimiters(new string[] { "," });
csvReader.HasFieldsEnclosedInQuotes = true;
//read column names
string[] colFields = csvReader.ReadFields();
char c = 'A';
int errNo = 0;
//CSVファイルの項目数チェック
if (colFields.Length != COL_COUNT)
{
errNo = 1;
while (colFields.Count() > COL_COUNT)
{
colFields = colFields.Take(colFields.Count() - 1).ToArray();
}
while (colFields.Count() < COL_COUNT)
{
Array.Resize(ref colFields, colFields.Length + 1);
colFields[colFields.Length - 1] = null;
}
}
ErrCheck(colFields, ref errNo);
//最終項目にエラー番号をセット
Array.Resize(ref colFields, colFields.Length + 1);
colFields[colFields.Length - 1] = errNo.ToString();
//１行目の一番左の項目が”B”でなければ１行目は無視する
if (!colFields[0].Equals("B"))
{
firstColHeader = "1";
}
else
{
dee.OrderDetailsSu++;
}
foreach (string column in colFields)
{
if (firstColHeader.Equals("1")) //first row is column name
{
if (!csvData.Columns.Contains(column))
{
DataColumn datacolumn = new DataColumn(column);
datacolumn.AllowDBNull = true;
csvData.Columns.Add(datacolumn);
}
else
{
DataColumn datacolumn = new DataColumn(column + "_" + count++);
datacolumn.AllowDBNull = true;
csvData.Columns.Add(datacolumn);
}
}
else//2
{
DataColumn datacolumn = new DataColumn(c++.ToString());
datacolumn.AllowDBNull = true;
csvData.Columns.Add(datacolumn);
}
}
if (firstColHeader.Equals("2")) // first row is data
{
csvData.Rows.Add(colFields); //add first row as data row
if (errNo == 0)
{
dee.ImportDetailsSu++;
}
else
{
dee.ErrorSu++;
}
}
while (!csvReader.EndOfData)
{
string[] fieldData = csvReader.ReadFields();
dee.OrderDetailsSu = dee.OrderDetailsSu + 1;
//CSVファイルの項目数チェック
if (fieldData.Length != COL_COUNT)
{
errNo = 1;
}
while (fieldData.Count() > COL_COUNT)
{
fieldData = fieldData.Take(fieldData.Count() - 1).ToArray();
}
while (fieldData.Count() < COL_COUNT)
{
Array.Resize(ref fieldData, fieldData.Length + 1);
fieldData[fieldData.Length - 1] = null;
}
//Making empty value as null
for (int i = 0; i < fieldData.Length; i++)
{
if (fieldData[i] == "")
{
fieldData[i] = null;
}
}
ErrCheck(fieldData, ref errNo);
//最終項目にエラー番号をセット
Array.Resize(ref fieldData, fieldData.Length + 1);
fieldData[fieldData.Length - 1] = errNo.ToString();
csvData.Rows.Add(fieldData);
if (errNo == 0)
{
dee.ImportDetailsSu++;
}
else
{
dee.ErrorSu++;
}
}
}
}
catch (Exception ex)
{
}
return(csvData);
}