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); }