public MDHeaderIndexUsed GetHeaderIndexUsedByhiu_id(string hiu_id)
{
MDHeaderIndexUsed oMDHeaderIndex = new MDHeaderIndexUsed();
DataTable dTable = new DataTable();
SqlCommand sql = new SqlCommand();
sql.CommandText = "SELECT hiu.hiu_id,hiu.mc_code,mc.mc_name,hiu.header_id,hm.header_detail";
sql.CommandText += " ,field_index,ColumnName,result_index,datetime_index,partnumber_index,model_index";
sql.CommandText += " FROM HeaderIndexUsed hiu";
sql.CommandText += " LEFT JOIN HeaderMaster hm ON hiu.header_id = hm.header_id";
sql.CommandText += " LEFT JOIN Machine mc ON hiu.mc_code = mc.mc_code";
sql.CommandText += " WHERE hiu_id = @hiu_id";
sql.Parameters.Add(new SqlParameter("@hiu_id", hiu_id));
dTable = oConn.Query(sql);
if (dTable.Rows.Count > 0)
{
foreach (DataRow row in dTable.Rows)
{
MDHeaderIndexUsed.CMDHeaderIndexUsed oMD = new MDHeaderIndexUsed.CMDHeaderIndexUsed();
oMD.hiu_id = row["hiu_id"] != DBNull.Value ? Convert.ToInt32(row["hiu_id"]) : 0;
oMD.mc_code = row["mc_code"].ToString();
oMD.mc_name = row["mc_name"].ToString();
oMD.header_id = row["header_id"].ToString();
oMD.header_detail = row["header_detail"].ToString();
oMD.field_index = row["field_index"].ToString();
oMD.ColumnName = row["ColumnName"].ToString();
oMD.result_index = row["result_index"].ToString();
oMD.datetime_index = row["datetime_index"].ToString();
oMD.partnumber_index = row["partnumber_index"].ToString();
oMD.model_index = row["model_index"].ToString();
oMDHeaderIndex.ListOfHeaderIndexUsed.Add(oMD);
}
}
return(oMDHeaderIndex);
}
public MDHeaderIndexUsed GetDataWithHeaderInx(string mc_code, string Model, DateTime DateStart, DateTime DateEnd)
{
MDHeaderIndexUsed oMDHeaderInx = new MDHeaderIndexUsed();
DataTable dTable = new DataTable();
StringBuilder sbd = new StringBuilder();
sbd.AppendLine("SELECT data_partnumber,header_detail,data_detail,field_index,ColumnName");
sbd.AppendLine(", mc_name, datetime_index, model_index, result_index,d.mc_code,model_code,Result");
sbd.AppendLine("FROM data d");
sbd.AppendLine("LEFT JOIN MachineHeaderIndex mhi ON d.mc_code = mhi.mc_code");
sbd.AppendLine("LEFT JOIN HeaderMaster hm ON mhi.header_id = hm.header_id");
sbd.AppendLine("LEFT JOIN Machine mc ON d.mc_code = mc.mc_code");
sbd.AppendLine("LEFT JOIN HeaderIndexUsed hiu ON d.mc_code = hiu.mc_code AND hm.header_id = hiu.header_id");
sbd.AppendLine("LEFT JOIN Model md ON d.model_id=md.model_id");
sbd.AppendLine("WHERE d.mc_code='" + mc_code + "'");
sbd.AppendLine("AND md.model_code='" + Model + "'");
sbd.AppendLine("AND data_mfgdate >= '" + DateStart + "'");
sbd.AppendLine("AND data_mfgdate <= '" + DateEnd + "'");
dTable = oConn.Query(sbd.ToString());
if (dTable.Rows.Count > 0)
{
foreach (DataRow row in dTable.Rows)
{
MDHeaderIndexUsed.CMDHeaderIndexUsed oMD = new MDHeaderIndexUsed.CMDHeaderIndexUsed();
oMD.ColumnName = row["ColumnName"].ToString();
oMD.header_detail = row["header_detail"].ToString();
oMD.data_detail = row["data_detail"].ToString();
oMD.field_index = row["field_index"].ToString();
oMD.mc_name = row["mc_name"].ToString();
oMD.mc_code = row["mc_code"].ToString();
oMD.result_index = row["result_index"].ToString();
oMD.model_index = row["model_index"].ToString();
oMD.datetime_index = row["datetime_index"].ToString();
oMD.Result = row["Result"].ToString();
oMDHeaderInx.ListOfHeaderIndexUsed.Add(oMD);
}
}
return(oMDHeaderInx);
}
public DataTable GetDataTable(string mc_code, string Model, DateTime DateStart, DateTime DateEnd)
{
DataTable dTable = new DataTable();
dTable.TableName = "ExportData";
MDHeaderIndexUsed oMDExport = new MDHeaderIndexUsed();
oMDExport = oHeaderInx.GetDataWithHeaderInx(mc_code, Model, DateStart, DateEnd);
if (oMDExport.ListOfHeaderIndexUsed.Count > 0)
{
string[] splColumn = oMDExport.ListOfHeaderIndexUsed[0].ColumnName.Split(',');
if (splColumn.Length > 0)
{
foreach (string ColumnName in splColumn)
{
dTable.Columns.Add(ColumnName, typeof(string));
}
//dTable.Columns.Add("Result");
}
for (int i = 0; i < oMDExport.ListOfHeaderIndexUsed.Count; i++)
{
bool CheckIndex = false;
int countDetail = 0;
string[] splDetail = oMDExport.ListOfHeaderIndexUsed[i].data_detail.Split(',');
countDetail = splDetail.Length;
DataRow dRow;
dRow = dTable.NewRow();
string[] splIndex = oMDExport.ListOfHeaderIndexUsed[i].field_index.Split(',');
int CountColumn = 0;
for (int j = 0; j < countDetail; j++)
{
CheckIndex = false;
int inxResult = 0;
if (splIndex.Length > 0)
{
foreach (string index in splIndex)
{
inxResult++;
if (j == Convert.ToInt32(index))
{
CheckIndex = true;
if (index == oMDExport.ListOfHeaderIndexUsed[i].result_index)
{
}
break;
}
}
}
if (CheckIndex)
{
//dRow["Result"] = oMDExport.ListOfHeaderIndexUsed[i].Result;
dRow[CountColumn] = splDetail[j];
CountColumn++;
}
}
dTable.Rows.Add(dRow);
}
}
else
{
//DataRow dRow;
//dRow = dTable.NewRow();
//dRow["CanEdit"] = false;
//dRow["CanDel"] = false;
//dTable.Rows.Add(dRow);
}
return(dTable);
}
