public JsonResult GetCYUMNDBF()
{
//string strcon = @"Provider = Microsoft.Jet.OLEDB.4.0; Data Source = \\10.121.21.16\Rainbow; Extended Properties = dBASE IV;";
string strcon = Models.MasterModels.ConStringDBF;
string sqlINS = "SELECT CYUNO,CYUDT,ZUBAN,SNAME,KISYU,TANI,TANKA,CYUSU,NOUKI,TOKCD,NOUCD,SFLG FROM CYUMNDF.DBF WHERE CYUDT > '20190901' AND CYUNO > '180000'";// SFLG IS NULL";
DataTable dtResult = new DataTable();
using (OleDbConnection conn = new OleDbConnection(strcon))
{
OleDbCommand cmd = new OleDbCommand(sqlINS, conn);
conn.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
adapter.Fill(dtResult);
conn.Close();
}
List <Models.DBFCYUMNModels> listCYUMN = new List <Models.DBFCYUMNModels>();
foreach (DataRow row in dtResult.Rows)
{
Models.DBFCYUMNModels cyumn = new Models.DBFCYUMNModels
{
Cyuno = row["CYUNO"].ToString(),
Cyudt = row["CYUDT"].ToString(),
Zuban = row["ZUBAN"].ToString(),
Sname = row["SNAME"].ToString(),
Kisyu = row["KISYU"].ToString(),
Tani = row["TANI"].ToString(),
Tanka = row["TANKA"].ToString(),
Cyusu = row["CYUSU"].ToString(),
Nouki = row["NOUKI"].ToString(),
Tokcd = row["TOKCD"].ToString(),
Noucd = row["NOUCD"].ToString(),
Sflg = row["SFLG"].ToString()
};
listCYUMN.Add(cyumn);
}
Models.DBFCYUMNModels[] result = listCYUMN.ToArray <Models.DBFCYUMNModels>();
Session["listCyumn"] = listCYUMN;
return(Json(result, JsonRequestBehavior.AllowGet));
}
public JsonResult TESTBYDUC(string testV)
{
string path = Models.MasterModels.dirServer + "CYUMNDF.csv";
//string path = @"C:\Rainbow\CYUMNDF.csv";
string pathOnly = Path.GetDirectoryName(path);
string fileName = Path.GetFileName(path);
string strsql = "select * from CYUMNDF.csv where ZUBAN='GA6-6713-001'";
switch (testV)
{
case "0":
string cnstr = @"Driver={Microsoft Text Driver (*.txt; *.csv)}; DBQ=" + pathOnly;
OdbcConnection cn = new OdbcConnection(cnstr);
cn.Open();
OdbcCommand cmd = new OdbcCommand("select * from CYUMNDF.csv where ZUBAN='GA6-6713-001'", cn);
OdbcDataReader reader = cmd.ExecuteReader();
List <Models.DBFCYUMNModels> listCYUMN = new List <Models.DBFCYUMNModels>();
while (reader.Read())
{
Models.DBFCYUMNModels cyumn = new Models.DBFCYUMNModels
{
Cyuno = reader["CYUNO"].ToString(),
Cyudt = reader["CYUDT"].ToString(),
Zuban = reader["ZUBAN"].ToString(),
Sname = reader["SNAME"].ToString(),
Kisyu = reader["KISYU"].ToString(),
Tani = reader["TANI"].ToString(),
Tanka = reader["TANKA"].ToString(),
Cyusu = reader["CYUSU"].ToString(),
Nouki = reader["NOUKI"].ToString(),
Tokcd = reader["TOKCD"].ToString(),
Noucd = reader["NOUCD"].ToString(),
Sflg = reader["SFLG"].ToString()
};
listCYUMN.Add(cyumn);
}
reader.Close();
cn.Close();
Models.DBFCYUMNModels[] result = listCYUMN.ToArray <Models.DBFCYUMNModels>();
Session["listCyumn"] = listCYUMN;
return(Json(result, JsonRequestBehavior.AllowGet));
cn.Close();
break;
case "1":
string sql = @"SELECT * FROM [" + fileName + "] WHERE SFLG='' OR SFLG IS NULL";
using (OleDbConnection con = new OleDbConnection(
@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathOnly +
";Extended Properties=\"Text;HDR=Yes\""))
using (OleDbCommand command = new OleDbCommand(sql, con))
using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
{
DataTable dtResult = new DataTable();
dtResult.Locale = CultureInfo.CurrentCulture;
adapter.Fill(dtResult);
listCYUMN = new List <Models.DBFCYUMNModels>();
//List<DBFCYUMNModels> listCYUMN = new List<DBFCYUMNModels>();
foreach (DataRow row in dtResult.Rows)
{
Models.DBFCYUMNModels cyumn = new Models.DBFCYUMNModels
{
Cyuno = row["CYUNO"].ToString(),
Cyudt = row["CYUDT"].ToString(),
Zuban = row["ZUBAN"].ToString(),
Sname = row["SNAME"].ToString(),
Kisyu = row["KISYU"].ToString(),
Tani = row["TANI"].ToString(),
Tanka = row["TANKA"].ToString(),
Cyusu = row["CYUSU"].ToString(),
Nouki = row["NOUKI"].ToString(),
Tokcd = row["TOKCD"].ToString(),
Noucd = row["NOUCD"].ToString(),
Sflg = row["SFLG"].ToString()
};
listCYUMN.Add(cyumn);
}
}
result = listCYUMN.ToArray <Models.DBFCYUMNModels>();
Session["listCyumn"] = listCYUMN;
return(Json(result, JsonRequestBehavior.AllowGet));
break;
case "2":
// 読み込みたいCSVファイルのパスを指定して開く
using (var reader3 = new StreamReader(path, Encoding.GetEncoding("shift_jis")))
//Console.WriteLine(reader3.ReadToEnd(),Encoding.GetEncoding("UTF-8"));//日本語化ける
//Console.WriteLine(reader3.ReadToEnd(),Encoding.GetEncoding("Shift-JIS"));//日本語化ける
//Console.WriteLine(reader3.ReadToEnd(),Encoding.GetEncoding("Shift_JIS"));//日本語化ける
//Console.WriteLine(reader3.ReadToEnd(),Encoding.GetEncoding("ASCII"));//日本語化ける
//Console.WriteLine(reader3.ReadToEnd(),Encoding.GetEncoding("CP932"));//サポート外
{
listCYUMN = new List <Models.DBFCYUMNModels>();
while (!reader3.EndOfStream)
{
string line = reader3.ReadLine();
if (!String.IsNullOrWhiteSpace(line))
{
string[] values = line.Split(',');
var slfgV = values[values.Length - 1].Replace("\"", "").Trim();
if (values.Length == 18)
{
if (slfgV == "" || slfgV == null)
{
Models.DBFCYUMNModels cyumn = new Models.DBFCYUMNModels
{
Cyuno = values[0].ToString().Replace("\"", ""),
Cyudt = values[2].ToString().Replace("\"", ""),
Zuban = values[4].ToString().Substring(1, values[4].Length - 2).Replace("\"\"", "\""),
Sname = values[5].ToString().Replace("\"", ""),
Kisyu = values[6].ToString().Replace("\"", ""),
Tani = values[7].ToString().Replace("\"", ""),
Tanka = values[8].ToString().Replace("\"", ""),
Cyusu = values[9].ToString().Replace("\"", ""),
Nouki = values[10].ToString().Replace("\"", ""),
Tokcd = values[15].ToString().Replace("\"", ""),
Noucd = values[16].ToString().Replace("\"", ""),
Sflg = values[17].ToString().Replace("\"", "")
};
listCYUMN.Add(cyumn);
}
}
else
{
int i = values.Length - 18;
if (slfgV == "" || slfgV == null)
{
string zubanV = "";
for (int j = 0; j <= i; j++)
{
zubanV += values[4 + j].ToString() + ",";
}
Models.DBFCYUMNModels cyumn = new Models.DBFCYUMNModels
{
Cyuno = values[0].ToString().Replace("\"", ""),
Cyudt = values[2].ToString().Replace("\"", ""),
Zuban = zubanV.Substring(1, zubanV.Length - 3).Replace("\"\"", "\""),
Sname = values[5 + i].ToString().Replace("\"", ""),
Kisyu = values[6 + 1].ToString().Replace("\"", ""),
Tani = values[7 + i].ToString().Replace("\"", ""),
Tanka = values[8 + i].ToString().Replace("\"", ""),
Cyusu = values[9 + i].ToString().Replace("\"", ""),
Nouki = values[10 + i].ToString().Replace("\"", ""),
Tokcd = values[15 + i].ToString().Replace("\"", ""),
Noucd = values[16 + i].ToString().Replace("\"", ""),
Sflg = values[17 + i].ToString().Replace("\"", "")
};
listCYUMN.Add(cyumn);
}
}
}
}
result = listCYUMN.ToArray <Models.DBFCYUMNModels>();
return(Json(result, JsonRequestBehavior.AllowGet));
}
break;
case "3":
DbConnection connection = new OleDbConnection();
connection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" // プロバイダ設定
//= "Provider=Microsoft.Jet.OLEDB.4.0;" // Jetでやる場合
+ "Data Source=" + pathOnly + "\\; " // ソースファイル指定
+ "Extended Properties=\"Text;HDR=YES;FMT=Delimited\"";
connection.Open();
DbCommand cmd1;
cmd1 = connection.CreateCommand();
cmd1.CommandText = strsql;
DbDataReader reader2 = cmd1.ExecuteReader();
listCYUMN = new List <Models.DBFCYUMNModels>();
while (reader2.Read())
{
Models.DBFCYUMNModels cyumn = new Models.DBFCYUMNModels
{
Cyuno = reader2["CYUNO"].ToString(),
Cyudt = reader2["CYUDT"].ToString(),
Zuban = reader2["ZUBAN"].ToString(),
Sname = reader2["SNAME"].ToString(),
Kisyu = reader2["KISYU"].ToString(),
Tani = reader2["TANI"].ToString(),
Tanka = reader2["TANKA"].ToString(),
Cyusu = reader2["CYUSU"].ToString(),
Nouki = reader2["NOUKI"].ToString(),
Tokcd = reader2["TOKCD"].ToString(),
Noucd = reader2["NOUCD"].ToString(),
Sflg = reader2["SFLG"].ToString()
};
listCYUMN.Add(cyumn);
}
result = listCYUMN.ToArray <Models.DBFCYUMNModels>();
Session["listCyumn"] = listCYUMN;
cmd1.Dispose();
connection.Dispose();
return(Json(result, JsonRequestBehavior.AllowGet));
break;
default:
return(new JsonResult());
break;
}
return(new JsonResult());
/*
* //string header = isFirstRowHeader ? "Yes" : "No";
* string path = Models.MasterModels.dirServer + "CYUMNDF.csv";
* string pathOnly = Path.GetDirectoryName(path);
* string fileName = Path.GetFileName(path);
*
* // string result = "";
* string cnstr = @"Driver={Microsoft Text Driver (*.txt; *.csv)}; DBQ=C:\Rainbow";
* OdbcConnection cn = new OdbcConnection(cnstr);
*
* cn.Open();
*
* OdbcCommand cmd = new OdbcCommand("select * from CYUMNDF.csv where ZUBAN='GA6-6713-001'", cn);
*
* OdbcDataReader row = cmd.ExecuteReader();
*
* List<Models.DBFCYUMNModels> listCYUMN = new List<Models.DBFCYUMNModels>();
*
* while (row.Read())
* {
* Models.DBFCYUMNModels cyumn = new Models.DBFCYUMNModels
* {
* Cyuno = row["CYUNO"].ToString(),
* Cyudt = row["CYUDT"].ToString(),
* Zuban = row["ZUBAN"].ToString(),
* Sname = row["SNAME"].ToString(),
* Kisyu = row["KISYU"].ToString(),
* Tani = row["TANI"].ToString(),
* Tanka = row["TANKA"].ToString(),
* Cyusu = row["CYUSU"].ToString(),
* Nouki = row["NOUKI"].ToString(),
* Tokcd = row["TOKCD"].ToString(),
* Noucd = row["NOUCD"].ToString(),
* Sflg = row["SFLG"].ToString()
* };
*
* listCYUMN.Add(cyumn);
* }
*
* row.Close();
* cn.Close();
*
* Models.DBFCYUMNModels[] result = listCYUMN.ToArray<Models.DBFCYUMNModels>();
*
* Session["listCyumn"] = listCYUMN;
*
* return Json(result, JsonRequestBehavior.AllowGet);
*
*
* /*
* using (OleDbConnection connection = new OleDbConnection(@"Driver={Microsoft Text Driver (*.txt; *.csv)}; DBQ="+pathOnly))
*
* using (OleDbCommand command = new OleDbCommand(sql, connection))
* using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
* {
* DataTable dtResult = new DataTable();
* dtResult.Locale = CultureInfo.CurrentCulture;
* adapter.Fill(dtResult);
*
* List<Models.DBFCYUMNModels> listCYUMN = new List<Models.DBFCYUMNModels>();
* foreach (DataRow row in dtResult.Rows)
* {
* Models.DBFCYUMNModels cyumn = new Models.DBFCYUMNModels
* {
* Cyuno = row["CYUNO"].ToString(),
* Cyudt = row["CYUDT"].ToString(),
* Zuban = row["ZUBAN"].ToString(),
* Sname = row["SNAME"].ToString(),
* Kisyu = row["KISYU"].ToString(),
* Tani = row["TANI"].ToString(),
* Tanka = row["TANKA"].ToString(),
* Cyusu = row["CYUSU"].ToString(),
* Nouki = row["NOUKI"].ToString(),
* Tokcd = row["TOKCD"].ToString(),
* Noucd = row["NOUCD"].ToString(),
* Sflg = row["SFLG"].ToString()
* };
*
* listCYUMN.Add(cyumn);
* }
*
* Models.DBFCYUMNModels[] result = listCYUMN.ToArray<Models.DBFCYUMNModels>();
*
* Session["listCyumn"] = listCYUMN;
*
* return Json(result, JsonRequestBehavior.AllowGet);
* }
*
* return new JsonResult();
*
* /*
* string result = "";
* string cnstr = @"Driver={Microsoft Text Driver (*.txt; *.csv)}; DBQ=C:\Rainbow";
* OdbcConnection cn = new OdbcConnection(cnstr);
*
* cn.Open();
*
* OdbcCommand cmd = new OdbcCommand("select * from CYUMNDF.csv where ZUBAN='GA6-6713-001'", cn);
*
* OdbcDataReader r = cmd.ExecuteReader();
*
*
* while (r.Read())
* {
* result += "<span>";
* result += r["CYUNO"] + ", ";
* result += r["SOUHU"] + ", ";
* result += r["CYUDT"] + ", ";
* result += r["BCODE"] + ", ";
* result += r["ZUBAN"] + ", ";
* result += r["SNAME"] + ", ";
* result += r["KISYU"] + ", ";
* result += r["TANI"] + ", ";
* result += r["TANKA"] + ", ";
* result += r["CYUSU"] + ", ";
* result += r["NOUKI"] + ", ";
* result += r["HKBN"] + ", ";
* result += r["SEIZO"] + ", ";
* result += r["HINSYU"] + ", ";
* result += r["NKBN"] + ", ";
* result += r["TOKCD"] + ", ";
* result += r["NOUCD"] + ", ";
* result += r["SFLG"] + "\n";
* result += "</span>";
* }
*
* r.Close();
* cn.Close();
*
* return result;
*
* /*
* string result = "";
* //string filePath = @"\\10.121.21.16\Rainbow";
* string filePath = @"C:\Rainbow";
*
* string query = string.Empty;
*
* query = "SELECT * FROM " + fileName + " WHERE SFLG=''";
*
* // 32-bit
* // OdbcConnection conn = new OdbcConnection("Driver=Microsoft Text Driver (*.txt, *.csv);Dbq=" + filePath + ";Extensions=csv;");
* // 64-bit
* OdbcConnection conn = new OdbcConnection("Driver=Microsoft Access Text Driver (*.txt, *.csv);Dbq=" + filePath + ";Extensions=csv;");
*
* conn.Open();
* OdbcCommand cmd = new OdbcCommand(query, conn);
* OdbcDataAdapter adapter = new OdbcDataAdapter(cmd);
* DataSet mydata = new DataSet("CSVData");
* adapter.Fill(mydata);
* conn.Close();
* conn.Dispose();
*
* var a = mydata.Tables[0].Rows.Count;
*
* return result;
*/
}
public Models.JyuucyuuModels convertCyu(Models.DBFCYUMNModels cyumn)
{
//Models.JyuucyuuModels result = new Models.JyuucyuuModels();
//
Models.JyuucyuuModels jyucyu = new Models.JyuucyuuModels();
jyucyu.Jyuucyuno = "";
jyucyu.Tokuicode = "00" + cyumn.Tokcd;
jyucyu.Nounyucode = "00" + cyumn.Noucd;
string temp = this.findName(jyucyu.Tokuicode, jyucyu.Nounyucode);
jyucyu.Tokuiname = temp.Split(new string[] { " - " }, StringSplitOptions.None)[0];
jyucyu.Nonyuname = temp.Split(new string[] { " - " }, StringSplitOptions.None)[1];
jyucyu.Seihinname = cyumn.Sname;
jyucyu.Seihinzuban = cyumn.Zuban;
jyucyu.Seihinkisyu = cyumn.Kisyu;
jyucyu.Quantity = cyumn.Cyusu;
jyucyu.Tanka = cyumn.Tanka;
//find by SQL
DataTable dtResult = cyumn.findSehinCode();
string result = "";
var rowC = dtResult.Rows.Count;
string rtani;
double rtanka, ctanka;
if (rowC < 1)
{
result = "NG: Can not find this sehin";
}
if (rowC == 1)
{
jyucyu.Seihinname = dtResult.Rows[0]["NAME"].ToString().Trim();
jyucyu.Seihinkisyu = dtResult.Rows[0]["KISYU"].ToString().Trim();
rtani = dtResult.Rows[0]["TANI"].ToString().Trim();
rtanka = double.Parse(dtResult.Rows[0]["TANKA"].ToString());
if (cyumn.Sname == null)
{
cyumn.Sname = "";
}
if (cyumn.Kisyu == null)
{
cyumn.Kisyu = "";
}
if (cyumn.Tani == null)
{
cyumn.Tani = "";
}
if (cyumn.Tanka == null)
{
ctanka = 0;
}
else
{
ctanka = double.Parse(cyumn.Tanka);
}
if (jyucyu.Seihinname != cyumn.Sname)
{
result += "NG: Name is not correct, ";
}
if (jyucyu.Seihinkisyu != cyumn.Kisyu.ToString())
{
result += "NG: Kisyu is not correct, ";
}
if (rtani != cyumn.Tani.ToString())
{
result += "NG: Tani is not correct, ";
}
if (rtanka != ctanka)
{
result += "NG: Tanka is not correct, ";
}
if (result != "")
{
result = result.Substring(0, result.Length - 2);
}
jyucyu.Tanka = rtanka.ToString();
jyucyu.Seihincode = dtResult.Rows[0]["ZAICD"].ToString().Trim();
}
else
{
result = "NG: Have many sehin Code";
}
jyucyu.Nouki = cyumn.Nouki;
jyucyu.Nounyu = new DateTime(Int32.Parse(cyumn.Nouki.Substring(0, 4)), Int32.Parse(cyumn.Nouki.Substring(4, 2)), Int32.Parse(cyumn.Nouki.Substring(6, 2))).AddDays(-2).ToString("yyyyMMdd");//early nouki 2 days
jyucyu.Bumoncode = "999";
jyucyu.Bumonname = "データCONV";
jyucyu.Tantoucode = "209"; //by Login User
jyucyu.Tantouname = "和久井清美"; //by Login User
jyucyu.Cyumonno1 = cyumn.Cyuno;
jyucyu.Cyumonno2 = "";
jyucyu.Seizou = "";
jyucyu.Bikou = "";
jyucyu.Status = result; //"Checking";
return(jyucyu);
}
public JsonResult convertCyuToJyu(Models.DBFCYUMNModels cyumn)
{
Models.JyuucyuuModels jyucyu = new Models.JyuucyuuModels();
jyucyu.Jyuucyuno = "";
jyucyu.Tokuicode = "00001";
jyucyu.Tokuiname = "(株)玉吉製作所大田原工場";
jyucyu.Nounyucode = "00001";
jyucyu.Nonyuname = "大田原工場";
jyucyu.Seihinname = cyumn.Sname;
jyucyu.Seihinzuban = cyumn.Zuban;
jyucyu.Seihinkisyu = cyumn.Kisyu;
jyucyu.Quantity = cyumn.Cyusu;
jyucyu.Tanka = cyumn.Tanka;
//find by SQL
DataTable dtResult = cyumn.findSehinCode();
string result = "";
var rowC = dtResult.Rows.Count;
string rtani;
double rtanka, ctanka;
if (rowC < 1)
{
result = "Error : Can not find this sehin";
}
if (rowC == 1)
{
jyucyu.Seihinname = dtResult.Rows[0]["NAME"].ToString().Trim();
jyucyu.Seihinkisyu = dtResult.Rows[0]["KISYU"].ToString().Trim();
rtani = dtResult.Rows[0]["TANI"].ToString().Trim();
rtanka = double.Parse(dtResult.Rows[0]["TANKA"].ToString());
if (cyumn.Sname == null)
{
cyumn.Sname = "";
}
if (cyumn.Kisyu == null)
{
cyumn.Kisyu = "";
}
if (cyumn.Tani == null)
{
cyumn.Tani = "";
}
if (cyumn.Tanka == null)
{
ctanka = 0;
}
else
{
ctanka = double.Parse(cyumn.Tanka);
}
if (jyucyu.Seihinname != cyumn.Sname)
{
result += "Name is not correct, ";
}
if (jyucyu.Seihinkisyu != cyumn.Kisyu.ToString())
{
result += "Kisyu is not correct, ";
}
if (rtani != cyumn.Tani.ToString())
{
result += "Tani is not correct, ";
}
if (rtanka != ctanka)
{
result += "Tanka is not correct, ";
}
if (result != "")
{
result = "Error: " + result.Substring(0, result.Length - 2);
}
jyucyu.Tanka = rtanka.ToString();
jyucyu.Seihincode = dtResult.Rows[0]["ZAICD"].ToString().Trim();
}
else
{
result = "Error : Have many sehin Code";
}
jyucyu.Nouki = cyumn.Nouki;
jyucyu.Nounyu = "";//early nouki 2 days
jyucyu.Bumoncode = "999";
jyucyu.Bumonname = "データCONV";
jyucyu.Tantoucode = "209"; //by Login User
jyucyu.Tantouname = "和久井清美"; //by Login User
jyucyu.Cyumonno1 = cyumn.Cyuno;
jyucyu.Cyumonno2 = "";
jyucyu.Seizou = "";
jyucyu.Bikou = "";
jyucyu.Status = result; //"Checking";
return(Json(jyucyu, JsonRequestBehavior.AllowGet));
}
