using System; using System.Data; using System.IO; using System.Windows.Forms; using NPOI.SS.UserModel; using NPOI.XSSF.UserModel; // 对于.xlsx文件 using NPOI.HSSF.UserModel; // 对于.xls文件 using System.Collections.Generic; using System.Runtime.InteropServices; using System.Drawing; namespace WFormReadData_SMR { public partial class ReadCoordinateExcelFrom : Form { DataTable dataTable = new DataTable(); private json objJsonResult = new json(); public DBHelper oCN = new DBHelper(); public ReadCoordinateExcelFrom() { InitializeComponent(); } private void ReadCoordinateExcelFrom_Load(object sender, EventArgs e) { this.cmbJYSelect.SelectedIndex = 0; this.cmbJYSelect.DropDownStyle = ComboBoxStyle.DropDownList; //批次码只读 this.txtPcm.ReadOnly = true; //路径只读 this.txtLj.ReadOnly = true; this.txtPcm.ReadOnly = true; //制单编码只读 this.txtHMakerNumber.ReadOnly = true; //制单人提示 this.txtHMaker.ForeColor = Color.LightGray; this.txtHMaker.Text = "请输入用户编码!"; SelectHProList(); } private void btnLj_Click(object sender, EventArgs e) { string txtLzk = this.txtLzk.Text; //string txtLzk = "GXLX00002866"; if (txtLzk == "") { MessageBox.Show("流转卡没有扫描!"); } else { //路径赋值给文本 using (FolderBrowserDialog folderBrowserDialog = new FolderBrowserDialog()) { DialogResult result = folderBrowserDialog.ShowDialog(); if (result == DialogResult.OK) { //保存按钮灰掉 this.btnSave.Enabled = true; string selectedFolderPath = folderBrowserDialog.SelectedPath; // 使用 selectedFolderPath 进行后续操作，例如显示在文本框中 this.txtLj.Text = selectedFolderPath; //GetCSV_Read(selectedFolderPath); GetExcel_Read(selectedFolderPath); } } } } //根据路径找到路径下面的所有文件进行数据读取 CSV public void GetCSV_Read(string FilePath) { DataSet ds = new DataSet(); if (string.IsNullOrEmpty(FilePath) || !Directory.Exists(FilePath)) { MessageBox.Show("文件夹内容为空!"); } //FilePath = FilePath + "\\" + this.txtLzk.Text; string[] files = Directory.GetFiles(FilePath, "*.csv", SearchOption.TopDirectoryOnly); int num = 0; for (int i = 0; i < files.Length; i++) { bool flag= files[i].Contains(this.txtLzk.Text); if (flag) { //根据文件路径找到对应的文件 flag = File.Exists(files[i]); if (flag) { //获取Excel对应的数据 objJsonResult = Xt_CSVReadText(files[i], i); if (objJsonResult.count == 1) { DataTable dt = objJsonResult.dataTable; ds.Tables.Add(dt); } else { MessageBox.Show("错误:" + objJsonResult.Message); break; } } else { MessageBox.Show("路径:" + files[i] + ",文件不存在!"); break; } } else { num++; if (num == files.Length) { MessageBox.Show("选择文件夹不存在流转卡:" + this.txtLzk.Text + "!"); break; } } } //显示数据 CMR-L-066 轮廓仪 ShowData(ds, "CMR-L-066"); } //根据文件路径找到对应文件 并获取对应的数据 public json Xt_CSVReadText(string FilePath, int num) { try { DataTable dt = new DataTable(num.ToString()); dt.Columns.Add("Characteristic"); dt.Columns.Add("Actual"); DataTable dt2 = new DataTable(); List records = new List(); using (StreamReader sr = new StreamReader(FilePath)) { string[] headers = sr.ReadLine()?.Split(','); if (headers != null) { foreach (var header in headers) { dt2.Columns.Add(header.Trim()); } } string line; while ((line = sr.ReadLine()) != null) { var values = line.Split(','); DataRow row = dt2.NewRow(); for (int i = 0; i < values.Length; i++) { // 这里可能需要添加类型转换和错误处理 row[i] = values[i].Trim(); } dt2.Rows.Add(row); } } for (int i = 0; i < dt2.Rows.Count; i++) { int nums = 0; for (int j = 0; j < dt2.Columns.Count; j++) { if (dt2.Columns[j].ColumnName.Contains("VALUE")) { DataRow dr = dt.NewRow(); if (nums == 1) { dr["Characteristic"] = "FAI 3-16-3-" + (i + 1); dr["Actual"] = dt2.Rows[0][dt2.Columns[j]].ToString(); } else { dr["Characteristic"] = "FAI 3-16-1-" + (i + 1); dr["Actual"] = dt2.Rows[0][dt2.Columns[j]].ToString(); } dt.Rows.Add(dr); nums++; if (nums == 2) { break; } } } } objJsonResult.count = 1; objJsonResult.Message = ""; objJsonResult.dataTable = dt; return objJsonResult; } catch (Exception e) { objJsonResult.count = 0; objJsonResult.Message = e.Message; objJsonResult.dataTable = null; return objJsonResult; } } //根据路径找到路径下面的所有文件进行数据读取 EXCEL public void GetExcel_Read(string FilePath) { DataSet ds = new DataSet(); if (string.IsNullOrEmpty(FilePath) || !Directory.Exists(FilePath)) { MessageBox.Show("文件夹内容为空!"); } string[] files = Directory.GetFiles(FilePath, "*.xls*", SearchOption.TopDirectoryOnly); int num = 0; for (int i = 0; i < files.Length; i++) { bool flag = files[i].Contains(this.txtLzk.Text); if (flag) { //根据文件路径找到对应的文件 flag = File.Exists(files[i]); if (flag) { //获取Excel对应的数据 objJsonResult = Xt_ExcelReadText(files[i], i); if (objJsonResult.count == 1) { DataTable dt = objJsonResult.dataTable; ds.Tables.Add(dt); } else { MessageBox.Show("错误:" + objJsonResult.Message); break; } } else { MessageBox.Show("路径:" + files[i] + ",文件不存在!"); break; } } else { num++; if (num == files.Length) { MessageBox.Show("选择文件夹不存在流转卡:" + this.txtLzk.Text + "!"); break; } } } //显示数据 CMR-L-067 三坐标 ShowData(ds, "CMR-L-067"); } //根据文件路径找到对应文件 并获取对应的数据 public json Xt_ExcelReadText(string FilePath,int num) { try { DataTable dt = new DataTable(num.ToString()); IWorkbook workbook; using (FileStream file = new FileStream(FilePath, FileMode.Open, FileAccess.Read)) { // 创建一个WorkbookFactory对象来读取文件 workbook = WorkbookFactory.Create(file); ISheet sheet = workbook.GetSheetAt(0); // 替换为实际的工作表名称 if (sheet != null) { // 迭代工作表中的行 for (int rowNum = 12; rowNum <= sheet.LastRowNum; rowNum++) { DataRow dr = dt.NewRow(); IRow row = sheet.GetRow(rowNum); if (row != null) // 空行可能为null { // 迭代每一行的单元格 for (int colNum = 0; colNum < row.LastCellNum; colNum++) { ICell cell = row.GetCell(colNum); if (cell != null) // 单元格可能为null { string cellValue = cell.ToString(); if (rowNum == 12) { dt.Columns.Add(cellValue); } else { dr[dt.Columns[colNum]] = cellValue; } } } if (rowNum != 12) { dt.Rows.Add(dr); } } } } } objJsonResult.count = 1; objJsonResult.Message = ""; objJsonResult.dataTable = dt; return objJsonResult; } catch (Exception e) { objJsonResult.count = 0; objJsonResult.Message = e.Message; objJsonResult.dataTable = null; return objJsonResult; } } //把数据显示在页面上 public void ShowData(DataSet ds,string HNumber) { //清空网格内容 ListData.Items.Clear(); //清空表格内容 dataTable.Columns.Clear(); dataTable.Rows.Clear(); dataTable.Columns.Add("HQCSchemeID"); dataTable.Columns.Add("CharacteristicID"); dataTable.Columns.Add("HQCStd"); dataTable.Columns.Add("Characteristic"); dataTable.Columns.Add("Actual"); dataTable.Columns.Add("HUpLimit"); dataTable.Columns.Add("HDownLimit"); dataTable.Columns.Add("HInspectInstruMentID"); for (int i = 0; i < ds.Tables.Count; i++) { //修改是在哪个设备上进行的数据采集 b.HInspectInstruMentID=1 三坐标1 轮廓仪2 DataSet dataSet = oCN.RunProcReturn(@"select b.HInspectInstruMentID,a.HInterID HQCSchemeID,b.HQCCheckItemID, ch.HNumber HQCCheckItemNumber,m.HNumber,HUpLimit,HDownLimit,HTargetVal from Sc_ProcessExchangeBillMain pr with(nolock) inner join Gy_QCCheckProjectMain a with(nolock) on pr.HMaterID=a.HMaterID inner join Gy_QCCheckProjectSub b with(nolock) on a.HInterID=b.HInterID inner join Gy_InspectInstruMent men with(nolock) on b.HInspectInstruMentID=men.HItemID and men.HNumber='"+ HNumber + @"' left join Gy_Material m with(nolock) on a.HMaterID=m.HItemID left join Gy_QCCheckItem ch with(nolock) on b.HQCCheckItemID=ch.HItemID where pr.HBillNo='" + this.txtLzk.Text + "'", "Gy_QCCheckProjectMain"); if (dataSet.Tables[0].Rows.Count == 0) { MessageBox.Show("当前流转卡对应的物料没有对应的检验方案!"); } else { for (int j = 0; j < dataSet.Tables[0].Rows.Count; j++) { int num = 0; DataRow dr = dataTable.NewRow(); for (int k = 0; k < ds.Tables[i].Rows.Count; k++) { if (dataSet.Tables[0].Rows[j]["HQCCheckItemNumber"].ToString() == ds.Tables[i].Rows[k]["Characteristic"].ToString()) { dr["HQCSchemeID"] = dataSet.Tables[0].Rows[j]["HQCSchemeID"].ToString(); dr["CharacteristicID"] = dataSet.Tables[0].Rows[j]["HQCCheckItemID"].ToString(); dr["HQCStd"] = dataSet.Tables[0].Rows[j]["HTargetVal"].ToString(); dr["Characteristic"] = ds.Tables[i].Rows[k]["Characteristic"].ToString(); dr["Actual"] = ds.Tables[i].Rows[k]["Actual"].ToString(); dr["HUpLimit"] = dataSet.Tables[0].Rows[j]["HUpLimit"].ToString(); dr["HDownLimit"] = dataSet.Tables[0].Rows[j]["HDownLimit"].ToString(); dr["HInspectInstruMentID"] = dataSet.Tables[0].Rows[j]["HInspectInstruMentID"].ToString(); ListData.Items.Add("检验项目:" + ds.Tables[i].Rows[k]["Characteristic"].ToString() + ",实测值:" + ds.Tables[i].Rows[k]["Actual"].ToString() + ",上公差:" + dataSet.Tables[0].Rows[j]["HUpLimit"].ToString() + ",下公差:" + dataSet.Tables[0].Rows[j]["HDownLimit"].ToString()); break; } else { num++; } } if (num == ds.Tables[i].Rows.Count) { MessageBox.Show("检验项目:" + dataSet.Tables[0].Rows[j]["HQCCheckItemNumber"].ToString() + ",不存在文件里!"); break; } if (dr.ItemArray.Length > 0) { dataTable.Rows.Add(dr); } } } } } //保存数据 private void btnSave_Click(object sender, EventArgs e) { string txtLzk = this.txtLzk.Text; string txtLj = this.txtLj.Text; if (txtLzk == "") { MessageBox.Show("流转卡没有扫描!"); } else if (txtLj == "") { MessageBox.Show("请选择路径!"); } else { //保存按钮灰掉 this.btnSave.Enabled = false; string SelectTpye = this.cmbJYSelect.Text; switch (SelectTpye) { case "首件检验单": FistInspectionForm_Save(); break; case "巡检检验单": InspectionForm_Save(); break; case "工序检验单": FinalInspectionForm_Save(); break; default: MessageBox.Show("检验类型错误!"); break; } } } //首件检验单保存 public void FistInspectionForm_Save() { try { string HProcID = this.cmbPro.SelectedValue.ToString(); string sExeReturnInfo = ""; string BillType = "7505"; long HInterID = oCN.CreateBillID_Prod(BillType, ref sExeReturnInfo); string HBillNo = oCN.CreateBillCode(BillType, ref sExeReturnInfo, true); DataSet ds = oCN.RunProcReturn(@"select staus.HSourceID,a.HICMOInterID,a.HICMOEntryID,a.HICMOBillNo,a.HPlanQty,a.HInterID HProcExchInterID, staus.HSourceEntryID HProcExchEntryID,a.HBillNo HProcExchBillNo,a.HQty,a.HMaterID from Sc_ProcessExchangeBillMain a with(nolock) inner join Sc_ICMOBillStatus_Tmp staus with(nolock) on a.HInterID=staus.HSourceInterID where a.HBillNo='" + this.txtLzk.Text + "' and staus.HProcID="+ HProcID, "Sc_ProcessExchangeBillMain"); if (ds.Tables[0].Rows.Count == 0) { MessageBox.Show("当前工单没有开工数据!"); } else { string HSourceID = ds.Tables[0].Rows[0]["HSourceID"].ToString(); string HICMOInterID = ds.Tables[0].Rows[0]["HICMOInterID"].ToString(); string HICMOEntryID = ds.Tables[0].Rows[0]["HICMOEntryID"].ToString(); string HICMOBillNo = ds.Tables[0].Rows[0]["HICMOBillNo"].ToString(); string HPlanQty = ds.Tables[0].Rows[0]["HPlanQty"].ToString(); string HProcExchInterID = ds.Tables[0].Rows[0]["HProcExchInterID"].ToString(); string HProcExchEntryID = ds.Tables[0].Rows[0]["HProcExchEntryID"].ToString(); string HProcExchBillNo = ds.Tables[0].Rows[0]["HProcExchBillNo"].ToString(); string HQty = ds.Tables[0].Rows[0]["HQty"].ToString(); string HMaterID = ds.Tables[0].Rows[0]["HMaterID"].ToString(); string HQCSchemeID = dataTable.Rows[0]["HQCSchemeID"].ToString(); int HLastResults = 1; oCN.BeginTran(); for (int i = 0; i < dataTable.Rows.Count; i++) { double HUpLimits = Double.Parse(dataTable.Rows[i]["HUpLimit"].ToString()) + Double.Parse(dataTable.Rows[i]["HQCStd"].ToString()); double HDownLimits = Double.Parse(dataTable.Rows[i]["HDownLimit"].ToString()) + Double.Parse(dataTable.Rows[i]["HQCStd"].ToString()); int HLastResult = (Double.Parse(dataTable.Rows[i]["Actual"].ToString()) > HUpLimits || HDownLimits < Double.Parse(dataTable.Rows[i]["HDownLimit"].ToString())) ? 0 : 1; if (HLastResult == 1 && HLastResults != 0) { HLastResults = 1; } else { HLastResults = 0; } oCN.RunProc("Insert into QC_FirstPieceCheckBillSub " + " (HInterID,HBillNo_bak,HEntryID,HCloseMan" + ",HEntryCloseDate,HCloseType,HRemark,HSourceInterID" + ",HSourceEntryID,HSourceBillNo,HSourceBillType,HRelationQty,HRelationMoney" + ",HQCCheckItemID,HQCStd,HUnit,HQCNote,HResult" + ",HMax,HMin,HAvg,HSampleSchemeID,HSampleQty,HSampleDamageQty,HAcceptQty" + ",HRejectQty,HSampleUnRightQty,HStatus,HUnitID,HInspectVal,HTargetVal" + ",HUpLimit,HDownLimit,HUpOffSet,HDownOffSet,HKeyInspect,HAnalysisMethod,HInspectInstruMentID,HInspectResult" + ") values(" + HInterID + ",'" + HBillNo + "'," + (i + 1) + ",''" + ",getdate(),0,''," + HProcExchInterID + "," + HProcExchEntryID + ",'" + HProcExchBillNo + "','',0,0" + "," + dataTable.Rows[i]["CharacteristicID"].ToString() + ",'" + dataTable.Rows[i]["HQCStd"].ToString() + "','','"+ dataTable.Rows[i]["HQCStd"].ToString() + "','" + HLastResult + "'" + ",'" + dataTable.Rows[i]["HUpLimit"].ToString() + "','" + dataTable.Rows[i]["HDownLimit"].ToString() + "',''" + ",0,0,0,0,0,0,'',0,'" + dataTable.Rows[i]["Actual"].ToString() + "','" + dataTable.Rows[i]["HQCStd"].ToString() + "','" + dataTable.Rows[i]["HUpLimit"].ToString() + "','" + dataTable.Rows[i]["HDownLimit"].ToString() + "','','',0,'2'," + dataTable.Rows[i]["HInspectInstruMentID"].ToString() + ",'" + HLastResult + "'" + ") "); } //主表 oCN.RunProc("Insert Into QC_FirstPieceCheckBillMain " + "(HBillType,HBillSubType,HInterID,HBillNo,HBillStatus,HDate,HMaker,HMakeDate" + ",HYear,HPeriod,HRemark" + ",HSourceID,HICMOInterID,HICMOBillNo,HICMOQty,HProcExchInterID,HProcExchEntryID" + ",HProcExchBillNo,HProcExchQty,HMaterID,HFirstCheckEmp,HLastResult" + ",HMainSourceInterID,HMainSourceEntryID,HMainSourceBillNo,HMainSourceBillType,HICMOEntryID,HQCSchemeID,HShiftsID,HErrTreatment" + ",HTakeSampleCheckBillID,HTakeSampleCheckBillNo,HProcID" + ",HBatchNo) " + " values('" + BillType + "','" + BillType + "'," + HInterID + ",'" + HBillNo + "',1,getdate(),'" + this.txtHMaker.Text + "',getdate()" + "," + DateTime.Now.Year + "," + DateTime.Now.Month + ",''" + "," + HSourceID + "," + HICMOInterID + ",'" + HICMOBillNo + "'," + HPlanQty + "," + HProcExchInterID + "," + HProcExchEntryID + ",'" + HProcExchBillNo + "'," + HQty + "," + HMaterID + ",'"+this.labHMakerID.Text + "', " + HLastResults + "," + HProcExchInterID + "," + HProcExchEntryID + ",'" + HProcExchBillNo + "',''," + HICMOEntryID + "," + HQCSchemeID + ",0,''" + "," + this.HTakeSampleCheckBillID.Text + ",'" + this.HTakeSampleCheckBillNo.Text + "'," + HProcID + "" + ",'" + this.txtPcm.Text + "') "); oCN.Commit(); MessageBox.Show("保存成功!"); } } catch (Exception e) { oCN.RollBack(); //保存按钮灰掉 this.btnSave.Enabled = true; MessageBox.Show("保存失败!" + e.Message); } } //巡检检验单保存 public void InspectionForm_Save() { try { string HProcID = this.cmbPro.SelectedValue.ToString(); string sExeReturnInfo = ""; string BillType = "7520"; long HInterID = oCN.CreateBillID_Prod(BillType, ref sExeReturnInfo); string HBillNo = oCN.CreateBillCode(BillType, ref sExeReturnInfo, true); DataSet ds = oCN.RunProcReturn(@"select staus.HSourceID,a.HICMOInterID,a.HICMOEntryID,a.HICMOBillNo,a.HPlanQty,a.HInterID HProcExchInterID, staus.HSourceEntryID HProcExchEntryID,a.HBillNo HProcExchBillNo,a.HQty,a.HMaterID from Sc_ProcessExchangeBillMain a with(nolock) inner join Sc_ICMOBillStatus_Tmp staus with(nolock) on a.HInterID=staus.HSourceInterID where a.HBillNo='" + this.txtLzk.Text + "' and staus.HProcID=" + HProcID, "Sc_ProcessExchangeBillMain"); if (ds.Tables[0].Rows.Count == 0) { MessageBox.Show("当前工单没有开工数据!"); } else { string HSourceID = ds.Tables[0].Rows[0]["HSourceID"].ToString(); string HICMOInterID = ds.Tables[0].Rows[0]["HICMOInterID"].ToString(); string HICMOEntryID = ds.Tables[0].Rows[0]["HICMOEntryID"].ToString(); string HICMOBillNo = ds.Tables[0].Rows[0]["HICMOBillNo"].ToString(); string HPlanQty = ds.Tables[0].Rows[0]["HPlanQty"].ToString(); string HProcExchInterID = ds.Tables[0].Rows[0]["HProcExchInterID"].ToString(); string HProcExchEntryID = ds.Tables[0].Rows[0]["HProcExchEntryID"].ToString(); string HProcExchBillNo = ds.Tables[0].Rows[0]["HProcExchBillNo"].ToString(); string HQty = ds.Tables[0].Rows[0]["HQty"].ToString(); string HMaterID = ds.Tables[0].Rows[0]["HMaterID"].ToString(); string HQCSchemeID = dataTable.Rows[0]["HQCSchemeID"].ToString(); int HLastResults = 1; oCN.BeginTran(); for (int i = 0; i < dataTable.Rows.Count; i++) { double HUpLimits = Double.Parse(dataTable.Rows[i]["HUpLimit"].ToString()) + Double.Parse(dataTable.Rows[i]["HQCStd"].ToString()); double HDownLimits = Double.Parse(dataTable.Rows[i]["HDownLimit"].ToString()) + Double.Parse(dataTable.Rows[i]["HQCStd"].ToString()); int HLastResult = (Double.Parse(dataTable.Rows[i]["Actual"].ToString()) > HUpLimits || HDownLimits < Double.Parse(dataTable.Rows[i]["HDownLimit"].ToString())) ? 0 : 1; if (HLastResult == 1 && HLastResults != 0) { HLastResults = 1; } else { HLastResults = 0; } oCN.RunProc("Insert into QC_PatrolProcCheckOtherBillSub " + " (HInterID,HBillNo_bak,HEntryID,HCloseMan" + ",HEntryCloseDate,HCloseType,HRemark,HSourceInterID" + ",HSourceEntryID,HSourceBillNo,HSourceBillType,HRelationQty,HRelationMoney" + ",HQCCheckItemID,HQCStd,HUnit,HQCNote,HResult" + ",HMax,HMin,HAvg,HSampleSchemeID,HSampleQty,HSampleDamageQty,HAcceptQty" + ",HRejectQty,HSampleUnRightQty,HStatus,HUnitID,HInspectVal,HTargetVal" + ",HUpLimit,HDownLimit,HUpOffSet,HDownOffSet,HKeyInspect,HAnalysisMethod,HInspectInstruMentID,HInspectResult" + ") values(" + HInterID + ",'" + HBillNo + "'," + (i + 1) + ",''" + ",getdate(),0,''," + HProcExchInterID + "," + HProcExchEntryID + ",'" + HProcExchBillNo + "','',0,0," + dataTable.Rows[i]["CharacteristicID"].ToString() + ",'" + dataTable.Rows[i]["HQCStd"].ToString() + "','','"+ dataTable.Rows[i]["HQCStd"].ToString() + "','" + HLastResult + "'" + ",'" + dataTable.Rows[i]["HUpLimit"].ToString() + "','" + dataTable.Rows[i]["HDownLimit"].ToString() + "',''" + ",0,0,0,0,0,0,'',0,'" + dataTable.Rows[i]["Actual"].ToString() + "','" + dataTable.Rows[i]["HQCStd"].ToString() + "','" + dataTable.Rows[i]["HUpLimit"].ToString() + "','" + dataTable.Rows[i]["HDownLimit"].ToString() + "','','',0,'2'," + dataTable.Rows[i]["HInspectInstruMentID"].ToString() + ",'" + HLastResult + "'" + ") "); } //主表 oCN.RunProc("Insert Into QC_PatrolProcCheckOtherBillMain " + "(HBillType,HBillSubType,HInterID,HBillNo,HBillStatus,HDate,HMaker,HMakeDate" + ",HYear,HPeriod,HRemark" + ",HSourceID,HICMOInterID,HICMOBillNo,HICMOQty,HProcExchInterID,HProcExchEntryID" + ",HProcExchBillNo,HProcExchQty,HMaterID,HFirstCheckEmp,HLastResult" + ",HMainSourceInterID,HMainSourceEntryID,HMainSourceBillNo,HMainSourceBillType,HICMOEntryID,HQCSchemeID,HShiftsID,HErrTreatment" + ",HTakeSampleCheckBillID,HTakeSampleCheckBillNo,HProcID,HBatchNo) " + " values('" + BillType + "','" + BillType + "'," + HInterID + ",'" + HBillNo + "',1,getdate(),'"+this.txtHMaker.Text + "',getdate()" + "," + DateTime.Now.Year + "," + DateTime.Now.Month + ",''" + "," + HSourceID + "," + HICMOInterID + ",'" + HICMOBillNo + "'," + HPlanQty + "," + HProcExchInterID + "," + HProcExchEntryID + ",'" + HProcExchBillNo + "'," + HQty + "," + HMaterID + ",'" + this.labHMakerID.Text + "', " + HLastResults + "," + HProcExchInterID + "," + HProcExchEntryID + ",'" + HProcExchBillNo + "',''," + HICMOEntryID + "," + HQCSchemeID + ",0,''" + "," + this.HTakeSampleCheckBillID.Text + ",'" + this.HTakeSampleCheckBillNo.Text + "',"+ HProcID + ",'" + this.txtPcm.Text + "') "); oCN.Commit(); MessageBox.Show("保存成功!"); } } catch (Exception e) { oCN.RollBack(); MessageBox.Show("保存失败!" + e.Message); } } //末检检验单 public void FinalInspectionForm_Save() { try { string HProcID = this.cmbPro.SelectedValue.ToString(); string sExeReturnInfo = ""; string BillType = "7507"; long HInterID = oCN.CreateBillID_Prod(BillType, ref sExeReturnInfo); string HBillNo = oCN.CreateBillCode(BillType, ref sExeReturnInfo, true); DataSet ds = oCN.RunProcReturn(@"select staus.HSourceID,a.HICMOInterID,a.HICMOEntryID,a.HICMOBillNo,a.HPlanQty,a.HInterID HProcExchInterID, staus.HSourceEntryID HProcExchEntryID,a.HBillNo HProcExchBillNo,a.HQty,a.HMaterID,staus.HProcID from Sc_ProcessExchangeBillMain a with(nolock) inner join Sc_ICMOBillStatus_Tmp staus with(nolock) on a.HInterID=staus.HSourceInterID where a.HBillNo='" + this.txtLzk.Text + "' and staus.HProcID=" + HProcID, "Sc_ProcessExchangeBillMain"); if (ds.Tables[0].Rows.Count == 0) { MessageBox.Show("当前工单没有开工数据!"); } else { string HSourceID = ds.Tables[0].Rows[0]["HSourceID"].ToString(); string HICMOInterID = ds.Tables[0].Rows[0]["HICMOInterID"].ToString(); string HICMOEntryID = ds.Tables[0].Rows[0]["HICMOEntryID"].ToString(); string HICMOBillNo = ds.Tables[0].Rows[0]["HICMOBillNo"].ToString(); string HPlanQty = ds.Tables[0].Rows[0]["HPlanQty"].ToString(); string HProcExchInterID = ds.Tables[0].Rows[0]["HProcExchInterID"].ToString(); string HProcExchEntryID = ds.Tables[0].Rows[0]["HProcExchEntryID"].ToString(); string HProcExchBillNo = ds.Tables[0].Rows[0]["HProcExchBillNo"].ToString(); string HQty = ds.Tables[0].Rows[0]["HQty"].ToString(); string HMaterID = ds.Tables[0].Rows[0]["HMaterID"].ToString(); HProcID = ds.Tables[0].Rows[0]["HProcID"].ToString(); string HQCSchemeID = dataTable.Rows[0]["HQCSchemeID"].ToString(); int HLastResults = 1; oCN.BeginTran(); for (int i = 0; i < dataTable.Rows.Count; i++) { double HUpLimits = Double.Parse(dataTable.Rows[i]["HUpLimit"].ToString()) + Double.Parse(dataTable.Rows[i]["HQCStd"].ToString()); double HDownLimits = Double.Parse(dataTable.Rows[i]["HDownLimit"].ToString()) + Double.Parse(dataTable.Rows[i]["HQCStd"].ToString()); int HLastResult = (Double.Parse(dataTable.Rows[i]["Actual"].ToString()) > HUpLimits || HDownLimits < Double.Parse(dataTable.Rows[i]["HDownLimit"].ToString())) ? 0 : 1; if (HLastResult == 1 && HLastResults != 0) { HLastResults = 1; } else { HLastResults = 0; } oCN.RunProc("Insert into QC_ProcessCheckBillSub " + " (HInterID,HBillNo_bak,HEntryID,HCloseMan" + ",HEntryCloseDate,HCloseType,HRemark,HSourceInterID" + ",HSourceEntryID,HSourceBillNo,HSourceBillType,HRelationQty,HRelationMoney" + ",HQCCheckClassID,HQCCheckItemID,HQCStd,HQCRelValue,HResult" + ",HProcCheckEmp,HProcCheckTime,HSampleSchemeID,HSampleQty,HSampleDamageQty,HAcceptQty" + ",HRejectQty,HSampleUnRightQty,HStatus,HUnitID,HInspectVal,HTargetVal" + ",HUpLimit,HDownLimit,HUpOffSet,HDownOffSet,HKeyInspect,HAnalysisMethod,HInspectInstruMentID,HInspectResult" + ",HQCNote) values(" + HInterID + ",'" + HBillNo + "'," + (i + 1) + ",''" + ",getdate(),0,''," + HProcExchInterID + "," + HProcExchEntryID + ",'" + HProcExchBillNo + "','',0,0,0," + dataTable.Rows[i]["CharacteristicID"].ToString() + ",'" + dataTable.Rows[i]["HQCStd"].ToString() + "','','" + HLastResult + "'" + ",0,'',0,0,0,0,0,0,'',0,'" + dataTable.Rows[i]["Actual"].ToString() + "','" + dataTable.Rows[i]["HQCStd"].ToString() + "','" + dataTable.Rows[i]["HUpLimit"].ToString() + "','" + dataTable.Rows[i]["HDownLimit"].ToString() + "','','',0,'2'," + dataTable.Rows[i]["HInspectInstruMentID"].ToString() + ",'" + HLastResult + "'" + ",'"+ dataTable.Rows[i]["HQCStd"].ToString() + "') "); } //主表 oCN.RunProc("Insert Into QC_ProcessCheckBillMain " + "(HBillType,HBillSubType,HInterID,HBillNo,HBillStatus,HDate,HMaker,HMakeDate" + ",HYear,HPeriod,HRemark" + ",HICMOInterID,HICMOBillNo,HProcExchInterID,HProcExchEntryID,HProcExchBillNo" + ",HMaterID,HProcID,HSourceID,HEmpID,HInStockQty" + ",HCheckQty,HRightQty,HBadQty,HFirstCheckEmp,HLastResult" + ",HBarCode,HLBatchNo,HCusID,HSortBillNo,HContrctBatchNo" + ",HProdAreaID,HProdTypeID,HProdStoveNo,HRecipeID,HDiameter1" + ",HDiameter2,HRoutingInterID,HDrawingDireID,HPackTypeID" + ",HMainSourceInterID,HMainSourceEntryID,HMainSourceBillNo,HMainSourceBillType,HICMOEntryID,HQCSchemeID,HICMOQty" + ",HProcExchQty,HShiftsID,HErrTreatment" + ",HTakeSampleCheckBillID,HTakeSampleCheckBillNo" + ",HBatchNo) " + " values('" + BillType + "','" + BillType + "'," + HInterID + ",'" + HBillNo + "',1,getdate(),'" + this.txtHMaker.Text + "',getdate()" + "," + DateTime.Now.Year + "," + DateTime.Now.Month + ",''" + "," + HICMOInterID + ",'" + HICMOBillNo + "'," + HProcExchInterID + "," + HProcExchEntryID + ",'" + HProcExchBillNo + "'" + "," + HMaterID + "," + HProcID + "," + HSourceID + ",0,0,0,0,0," + this.labHMakerID.Text + ",'" + HLastResults + "'" + ",'','',0,'',''" + ",0,0,'',0,0,0,0,'',0," + HProcExchInterID + "," + HProcExchEntryID + ",'" + HProcExchBillNo + "',''," + HICMOEntryID + "," + HQCSchemeID + "," + HPlanQty + "," + HQty + ",0,''" + "," + this.HTakeSampleCheckBillID.Text + ",'" + this.HTakeSampleCheckBillNo.Text + "'" + ",'" + this.txtPcm.Text + "') "); oCN.Commit(); MessageBox.Show("保存成功!"); } } catch (Exception e) { oCN.RollBack(); MessageBox.Show("保存失败!" + e.Message); } } //流转卡回车 private void txtLzk_KeyDown(object sender, KeyEventArgs e) { if (e.KeyCode == Keys.Enter) { if (this.txtHMakerNumber.Text == "") { MessageBox.Show("请输入制单人信息!"); } else { SelectHBardCode(this.txtLzk.Text); } } } //查询条形码 public void SelectHBardCode(string HBillNo) { try { string cmbPro = this.cmbPro.SelectedValue.ToString(); DataSet ds = oCN.RunProcReturn("select * from h_v_QC_TakeSampleCheckBillList where 单据号='" + HBillNo + "' and HProcID='" + cmbPro + "'", "h_v_QC_TakeSampleCheckBillList"); if (ds.Tables[0].Rows.Count == 0) { MessageBox.Show("当前工序对应的取样单据,查无数据!"); } else { string HProjectNum = ds.Tables[0].Rows[0]["HProjectNum"].ToString(); this.txtPcm.Text = HProjectNum.Split('-')[0]; this.txtLzk.Text = ds.Tables[0].Rows[0]["工序流转卡号"].ToString(); this.HTakeSampleCheckBillID.Text = ds.Tables[0].Rows[0]["hmainid"].ToString(); this.HTakeSampleCheckBillNo.Text = ds.Tables[0].Rows[0]["单据号"].ToString(); this.txtLzk.ReadOnly = true; this.label2.Text = "流转卡:"; } //DataSet ds = oCN.RunProcReturn("select * from h_v_Sc_ProcessExchangeBillQuerySub where 单据号='" + HBillNo + "' and HProcID='"+ cmbPro + "'", "h_v_Sc_ProcessExchangeBillQuerySub"); //if (ds.Tables[0].Rows.Count == 0) //{ //} //else //{ // string HProjectNum = ds.Tables[0].Rows[0]["HProjectNum"].ToString(); // this.txtPcm.Text = HProjectNum.Split('-')[0]; // this.HTakeSampleCheckBillID.Text = "0"; // this.HTakeSampleCheckBillNo.Text = ""; // this.txtLzk.ReadOnly = true; //} } catch (Exception e) { MessageBox.Show(this, e.Message, "提示"); } } //查询工序 public void SelectHProList() { try { DataSet ds = oCN.RunProcReturn("select * from Gy_Process ", "Sc_ProcessExchangeBillMain"); if (ds.Tables[0].Rows.Count == 0) { MessageBox.Show("工序查无数据!"); } else { this.cmbPro.DataSource = ds.Tables[0]; this.cmbPro.DisplayMember = "HName"; this.cmbPro.ValueMember = "HItemID"; this.cmbPro.SelectedIndex = 0; this.cmbPro.DropDownStyle = ComboBoxStyle.DropDownList; } } catch (Exception e) { MessageBox.Show(this, e.Message, "提示"); } } private void ReadCoordinateExcelFrom_Activated(object sender, EventArgs e) { //光标选中制单人 this.txtHMaker.Focus(); } private void ReadCoordinateExcelFrom_FormClosing(object sender, FormClosingEventArgs e) { if (MessageBox.Show("确定要关闭吗？", "确认", MessageBoxButtons.YesNo) == DialogResult.No) { e.Cancel = true; } } private void txtHMaker_Leave(object sender, EventArgs e) { if (string.IsNullOrWhiteSpace(txtHMaker.Text)) { this.txtHMaker.ForeColor = Color.LightGray; txtHMaker.Text = "请输入用户编码!"; } } private void txtHMaker_Enter(object sender, EventArgs e) { if (txtHMaker.Text.Trim() == "请输入用户编码!") { txtHMaker.Text = ""; this.txtHMaker.ForeColor = Color.Black; } } private void txtHMaker_KeyDown(object sender, KeyEventArgs e) { if (e.KeyCode == Keys.Enter) { SelectHMaker(this.txtHMaker.Text); } } //查询职员信息 public void SelectHMaker(string txtHMaker) { try { DataSet ds = oCN.RunProcReturn("select HItemID,HName,HNumber from Gy_Employee where HNumber='" + txtHMaker + "' or HName ='" + txtHMaker + "'", "Gy_Employee"); if (ds.Tables[0].Rows.Count > 0) { string HName = ds.Tables[0].Rows[0]["HName"].ToString(); string HMakerID = ds.Tables[0].Rows[0]["HItemID"].ToString(); string HNumber = ds.Tables[0].Rows[0]["HNumber"].ToString(); this.txtHMaker.Text = HName; this.txtHMakerNumber.Text = HNumber; this.labHMakerID.Text = HMakerID; this.txtHMaker.ReadOnly = true; } else { MessageBox.Show(this, txtHMaker + ",查无数据,可能是输入编码有误!", "提示"); } } catch (Exception e) { MessageBox.Show(this, e.Message, "提示"); } } } }