private void toolStripButtonEDIT_Click(object sender, EventArgs e) { int i; int iTemp; if (!countAmount(-1)) { MessageBox.Show("please check data", "information", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return; } System.Data.SqlClient.SqlTransaction sqlta; sqlConn.Open(); sqlta = sqlConn.BeginTransaction(); sqlComm.Transaction = sqlta; try { foreach (cIme s in alIme) { sqlComm.CommandText = "SELECT ID, [Product ID], [Product Code], [Indentor ID], [Indentor Code], [Start Number], [End Number], [Acquire ID], Date, Year, [Num of Week], Numbers FROM orders WHERE ([Product Code] = N'" + s.sPCode + "') AND ([Indentor Code] = N'" + s.sICode + "') AND (Year = " + s.iYear.ToString() + ") AND ([Num of Week] = N'" + s.iWeek.ToString() + "')"; sqldr = sqlComm.ExecuteReader(); if (sqldr.HasRows) { sqldr.Read(); iTemp = int.Parse(sqldr.GetValue(0).ToString()); sqldr.Close(); sqlComm.CommandText = "UPDATE orders SET [Start Number] = " + s.iStart.ToString() + ", [End Number] = " + s.iEnd.ToString() + ", [Acquire ID] = " + s.iAcquire.ToString() + ", Numbers = " + s.iCount.ToString() + ", [Order Start Number] =" + s.iOrderStart.ToString() + ", [Order End Number] =" + s.iOrderEnd.ToString() + " WHERE (ID = " + iTemp.ToString() + ")"; } else { sqldr.Close(); DateTime dt = ClassIm.GetDate(s.iYear, s.iWeek); sqlComm.CommandText = "INSERT INTO orders ([Product ID], [Product Code], [Indentor ID], [Indentor Code], [Start Number], [End Number], [Acquire ID], Date, Year, [Num of Week], Numbers,[Order Start Number],[Order End Number],backlog) VALUES (" + s.iPCode.ToString() + ", N'" + s.sPCode + "', " + s.iICode.ToString() + ", N'" + s.sICode + "', " + s.iStart.ToString() + ", " + s.iEnd.ToString() + ", " + s.iAcquire.ToString() + ", CONVERT(DATETIME, '" + dt.ToShortDateString() + " 00:00:00', 102), " + s.iYear.ToString() + ", N'" + s.iWeek.ToString() + "', " + s.iCount.ToString() + "," + s.iOrderStart.ToString() + "," + s.iOrderEnd.ToString() + ",0)"; } sqlComm.ExecuteNonQuery(); } sqlta.Commit(); MessageBox.Show("ORDER finished"); } catch (Exception ex) { MessageBox.Show("error:" + ex.Message.ToString(), "error", MessageBoxButtons.OK, MessageBoxIcon.Error); sqlta.Rollback(); return; } finally { sqlConn.Close(); } }
private void dataGridViewP_CellValidating(object sender, DataGridViewCellValidatingEventArgs e) { if (dataGridViewP.Rows[e.RowIndex].IsNewRow) { return; } int intOut = 0; ClassIm.ClearDataGridViewErrorText(dataGridViewP); switch (e.ColumnIndex) { case 10: if (e.FormattedValue.ToString() == "") { break; } if (int.TryParse(e.FormattedValue.ToString(), out intOut)) { if (intOut < 0) { dataGridViewP.Rows[e.RowIndex].Cells[10].ErrorText = "data error"; e.Cancel = true; } } else { dataGridViewP.Rows[e.RowIndex].Cells[10].ErrorText = "data error"; e.Cancel = true; } break; default: break; } //dataGridViewP.EndEdit(); }
private void MDIiManage_Load(object sender, EventArgs e) { //日期显示 toolStripStatusLabelDate.Text = "Today is " + System.DateTime.Now.ToShortDateString() + ", Week " + ClassIm.GetWeek(System.DateTime.Now).ToString(); string dFileName = ""; dFileName = Directory.GetCurrentDirectory() + "\\appcon.xml"; sqlComm.Connection = sqlConn; sqlDA.SelectCommand = sqlComm; if (File.Exists(dFileName)) //存在文件 { dSet.ReadXml(dFileName); strConn = "workstation id=CY;packet size=4096;user id=" + dSet.Tables["DataBaseInfo"].Rows[0][1].ToString() + ";password="******"DataBaseInfo"].Rows[0][2].ToString() + ";data source=\"" + dSet.Tables["DataBaseInfo"].Rows[0][0].ToString() + "\";;initial catalog=" + dSet.Tables["DataBaseInfo"].Rows[0][3].ToString(); strDataBaseAddr = dSet.Tables["DataBaseInfo"].Rows[0][0].ToString(); strDataBaseUser = dSet.Tables["DataBaseInfo"].Rows[0][1].ToString(); strDataBasePass = dSet.Tables["DataBaseInfo"].Rows[0][2].ToString(); strDataBaseName = dSet.Tables["DataBaseInfo"].Rows[0][3].ToString(); if (strDataBaseAddr == "0.0.0.0") { strConn = ""; //option string dFileNameOption = Directory.GetCurrentDirectory() + "\\options.xml"; if (File.Exists(dFileNameOption)) //存在文件 { dSetOption.ReadXml(dFileNameOption); iNumofWeek = int.Parse(dSetOption.Tables["parameters"].Rows[0][0].ToString()); iNumofPass = int.Parse(dSetOption.Tables["parameters"].Rows[0][1].ToString()); //iRange = int.Parse(dSetOption.Tables["parameters"].Rows[0][2].ToString()); } return; } sqlConn.ConnectionString = strConn; try { sqlConn.Open(); sqlDA.SelectCommand = sqlComm; sqlComm.CommandText = "SELECT ID, [num of week], [num of pass], range, [string TAG], [SAP Control Number], [SAP order], [Sales plan], [CW IMEI], [Master data] FROM parameters"; sqldr = sqlComm.ExecuteReader(); if (sqldr.HasRows) { sqldr.Read(); iNumofWeek = int.Parse(sqldr.GetValue(1).ToString()); iNumofPass = int.Parse(sqldr.GetValue(2).ToString()); iRange = int.Parse(sqldr.GetValue(3).ToString()); sTAG = sqldr.GetValue(4).ToString();; iSAPControlNumber = int.Parse(sqldr.GetValue(5).ToString());; sSAPorder = sqldr.GetValue(6).ToString(); sSalesplan = sqldr.GetValue(7).ToString(); sCWIMEI = sqldr.GetValue(8).ToString(); sMaster = sqldr.GetValue(9).ToString(); sqldr.Close(); } } catch (System.Data.SqlClient.SqlException err) { bool isCreateDatabase = true; if (MessageBox.Show("connect database fail, create it?" + err.Message.ToString(), "Infomation", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) == DialogResult.No) { isCreateDatabase = false; } strConn = ""; formDatabaseSet frmDatabaseSet = new formDatabaseSet(); if (isCreateDatabase) { frmDatabaseSet.intMode = 1; } frmDatabaseSet.ShowDialog(this); if (frmDatabaseSet.strConn != "") { strConn = frmDatabaseSet.strConn; //初始化窗口 sqlConn.ConnectionString = strConn; sqlComm.CommandText = "SELECT ID, [num of week], [num of pass], range, [string TAG], [SAP Control Number], [SAP order], [Sales plan], [CW IMEI], [Master data] FROM parameters"; sqlConn.Open(); sqldr = sqlComm.ExecuteReader(); if (sqldr.HasRows) { sqldr.Read(); iNumofWeek = int.Parse(sqldr.GetValue(1).ToString()); iNumofPass = int.Parse(sqldr.GetValue(2).ToString()); iRange = int.Parse(sqldr.GetValue(3).ToString()); sTAG = sqldr.GetValue(4).ToString();; iSAPControlNumber = int.Parse(sqldr.GetValue(5).ToString());; sSAPorder = sqldr.GetValue(6).ToString(); sSalesplan = sqldr.GetValue(7).ToString(); sCWIMEI = sqldr.GetValue(8).ToString(); sMaster = sqldr.GetValue(9).ToString(); sqldr.Close(); } sqlConn.Close(); } else { //this.Close(); return; } } finally { sqlConn.Close(); } } else //不存在文件 { /* * formDatabaseSet frmDatabaseSet = new formDatabaseSet(); * frmDatabaseSet.ShowDialog(this); * if (frmDatabaseSet.strConn != "") * { * strConn = frmDatabaseSet.strConn; * //初始化窗口 * sqlConn.ConnectionString = strConn; * * sqlComm.CommandText = "SELECT ID, [num of week], [num of pass], range, [string TAG], [SAP Control Number], [SAP order], [Sales plan], [CW IMEI], [Master data] FROM parameters"; * sqlConn.Open(); * sqldr = sqlComm.ExecuteReader(); * if (sqldr.HasRows) * { * sqldr.Read(); * iNumofWeek = int.Parse(sqldr.GetValue(1).ToString()); * iNumofPass = int.Parse(sqldr.GetValue(2).ToString()); * iRange = int.Parse(sqldr.GetValue(3).ToString()); * * sTAG = sqldr.GetValue(4).ToString(); ; * iSAPControlNumber = int.Parse(sqldr.GetValue(5).ToString()); ; * sSAPorder = sqldr.GetValue(6).ToString(); * sSalesplan = sqldr.GetValue(7).ToString(); * sCWIMEI = sqldr.GetValue(8).ToString(); * sMaster = sqldr.GetValue(9).ToString(); * sqldr.Close(); * * } * sqlConn.Close(); * } * else * { * //this.Close(); * return; * } */ strConn = ""; return; } }
private bool countAmount(int iRows) { bool bCheck = true; bool bFirst = true; int i, j, k; int iStartYear = 0, iStartWeek = 0; int iEndYear = 0, iEndWeek = 0; int iStartNo = 0, iEndNo = 0; int iRStart = 0, iRend = 0; int iAcquireNO = 0; int iTemp = 0; bool bTrue = true; int iSurplus = 0; manageArrayList(); //重置用户输入 this.dataGridViewP.CellValidating -= dataGridViewP_CellValidating; ClassIm.ClearDataGridViewErrorText(dataGridViewP); if (iRows < 0) { iRStart = 0; iRend = dataGridViewP.Rows.Count; } else { iRStart = iRows; iRend = iRows + 1; } for (i = iRStart; i < iRend; i++) { if (dataGridViewP.Rows[i].IsNewRow) { continue; } iSurplus = 0; for (j = ICOLUMNS; j < dataGridViewP.ColumnCount; j++) { if (dataGridViewP.Rows[i].Cells[j].Value.ToString() == "") { dataGridViewP.Rows[i].Cells[j].ErrorText = "please input number"; bCheck = false; } } if (!bCheck) { continue; } //整理 string[] sT = dataGridViewP.Rows[i].Cells[11].Value.ToString().Split('-'); iStartYear = int.Parse(sT[0]); iStartWeek = int.Parse(sT[1]); iStartNo = int.Parse(dataGridViewP.Rows[i].Cells[10].Value.ToString()); if (iStartNo != 0) { iStartNo++; //起始号 } //本周计算,backlog iEndWeek = iStartWeek; iEndYear = iStartYear; var alQuery1 = from cIme cime in alIme where (cime.sPCode == dataGridViewP.Rows[i].Cells[1].Value.ToString()) && (cime.sICode == dataGridViewP.Rows[i].Cells[5].Value.ToString()) && (cime.iYear == iEndYear) && (cime.iWeek == iEndWeek) select cime; foreach (cIme s in alQuery1) { iAcquireNO = 0; bTrue = true; while (bTrue) { //找到开始号区间 var q3 = from dt1 in dSet.Tables["acquire"].AsEnumerable() //查询最后 where (dt1.Field <string>("Product Code") == s.sPCode) && (dt1.Field <string>("Indentor Code") == s.sICode) && (dt1.Field <int>("Start Number") <= iStartNo) && (dt1.Field <int>("End Number") >= iStartNo) //条件 orderby dt1.Field <int>("End Number") select dt1; if (q3.Count() < 1) //没有区间 { s.iAcquire = 0; break; } bFirst = true; s.iAcquire = 0; foreach (var item in q3) { if (!bFirst) //第二次循环,以此次申请的第一位数为起始数 { iStartNo = item.Field <int>("Start Number"); } iEndNo = iStartNo + (int)Math.Ceiling((double)s.iBacklog * (double)s.iNum * ((double)s.dFailureRate / 100.0 + 1.0)) - 1; if (iEndNo <= item.Field <int>("End Number")) //找到区间 { s.iAcquire = item.Field <int>("ID"); s.iAcStart = item.Field <int>("Start Number"); s.iAcEnd = item.Field <int>("End Number"); s.iStart = iStartNo; s.iEnd = iEndNo; iStartNo = iEndNo + 1; s.iSurpLus = getSurplus(s.sPCode, s.sICode, iEndNo); bTrue = false; break; } else //区间不够,跳到下一区间 { //得到下一个申请区间 bFirst = false; } break; } } //while //得到预计计数 if (s.iAcquire == 0) { s.iOrderStart = iStartNo; s.iOrderEnd = iStartNo + (int)Math.Ceiling((double)s.iBacklog * (double)s.iNum * ((double)s.dFailureRate / 100.0 + 1.0)) - 1; iSurplus = -1 * (int)Math.Ceiling((double)s.iBacklog * (double)s.iNum * ((double)s.dFailureRate / 100.0 + 1.0)); s.iSurpLus = iSurplus; iStartNo = s.iOrderEnd + 1; } break; }//s //计算以后周 k = 1; while (true) { //从制造下一周开始 iEndWeek = int.Parse(ClassIm.GetWeek(iStartYear, iStartWeek, k)); iEndYear = int.Parse(ClassIm.sYear); k++; var alQuery = from cIme cime in alIme where (cime.sPCode == dataGridViewP.Rows[i].Cells[1].Value.ToString()) && (cime.sICode == dataGridViewP.Rows[i].Cells[5].Value.ToString()) && (cime.iYear == iEndYear) && (cime.iWeek == iEndWeek) select cime; if (alQuery.Count() < 1) //没有记录 { break; } foreach (cIme s in alQuery) { iAcquireNO = 0; bTrue = true; while (bTrue) { //找到开始号区间 var q3 = from dt1 in dSet.Tables["acquire"].AsEnumerable() //查询最后 where (dt1.Field <string>("Product Code") == s.sPCode) && (dt1.Field <string>("Indentor Code") == s.sICode) && (dt1.Field <int>("Start Number") <= iStartNo) && (dt1.Field <int>("End Number") >= iStartNo) //条件 orderby dt1.Field <int>("End Number") select dt1; if (q3.Count() < 1) //没有区间 { s.iAcquire = 0; break; } bFirst = true; s.iAcquire = 0; foreach (var item in q3) { if (!bFirst) //第二次循环,以此次申请的第一位数为起始数 { iStartNo = item.Field <int>("Start Number"); } iEndNo = iStartNo + (int)Math.Ceiling((double)s.iCount * (double)s.iNum * ((double)s.dFailureRate / 100.0 + 1.0)) - 1; if (iEndNo <= item.Field <int>("End Number")) //找到区间 { s.iAcquire = item.Field <int>("ID"); s.iAcStart = item.Field <int>("Start Number"); s.iAcEnd = item.Field <int>("End Number"); s.iStart = iStartNo; s.iEnd = iEndNo; iStartNo = iEndNo + 1; s.iSurpLus = getSurplus(s.sPCode, s.sICode, iEndNo); bTrue = false; break; } else //区间不够,跳到下一区间 { bFirst = false; } } break; } //while //得到预计计数 if (s.iAcquire == 0) { s.iOrderStart = iStartNo; s.iOrderEnd = iStartNo + (int)Math.Ceiling((double)s.iCount * (double)s.iNum * ((double)s.dFailureRate / 100.0 + 1.0)) - 1; iSurplus += -1 * ((int)Math.Ceiling((double)s.iCount * (double)s.iNum * ((double)s.dFailureRate / 100.0 + 1.0))); s.iSurpLus = iSurplus; iStartNo = s.iOrderEnd + 1; } break; }//s } //if (!bCheck1) // dataGridViewP.Rows[i].DefaultCellStyle.BackColor = Color.Pink; //else // dataGridViewP.Rows[i].DefaultCellStyle.BackColor = Color.Gray; //dataGridViewP.Rows[i].DefaultCellStyle.BackColor = Color.Red; } //for //changeViewColor(); changeViewText(); this.dataGridViewP.CellValidating += dataGridViewP_CellValidating; //dataGridViewP.EndEdit(); return(bCheck); }
private void initDatatable(bool bPCode, bool bICode) { int i, j, k; int iTemp = 0, iTemp1 = 0; string sTemp = ""; bool bFirst = true; dataGridViewP.RowValidating -= dataGridViewP_RowValidating; dataGridViewP.CellValidating -= dataGridViewP_CellValidating; dtOrder.Columns.Clear(); dtOrder.Columns.Add("pid", System.Type.GetType("System.Decimal"));//0 dtOrder.Columns.Add("Product Code", System.Type.GetType("System.String")); dtOrder.Columns.Add("Numbers", System.Type.GetType("System.Decimal")); dtOrder.Columns.Add("Failure Rate", System.Type.GetType("System.Decimal")); dtOrder.Columns.Add("iid", System.Type.GetType("System.Decimal"));//4 dtOrder.Columns.Add("Indentor Code", System.Type.GetType("System.String")); dtOrder.Columns.Add("TAC ID", System.Type.GetType("System.Decimal")); dtOrder.Columns.Add("TAC Code", System.Type.GetType("System.String")); dtOrder.Columns.Add("TAC Init", System.Type.GetType("System.Decimal")); dtOrder.Columns.Add("Manufacture Number", System.Type.GetType("System.Decimal"));//9 dtOrder.Columns.Add("Total Number", System.Type.GetType("System.Decimal")); dtOrder.Columns.Add("Manufacture Date", System.Type.GetType("System.String")); dtOrder.Columns.Add("Acquire Number", System.Type.GetType("System.Decimal")); dtOrder.Columns.Add("Acquire Date", System.Type.GetType("System.String")); dtOrder.Columns.Add("Surplus", System.Type.GetType("System.Decimal")); //剩余 14 for (i = iNumofPass; i > 0; i--) { sTemp = ClassIm.GetWeek(dateTimePickerP.Value, -1 * i); sTemp = ClassIm.sYear + "-" + sTemp; dtOrder.Columns.Add(sTemp, System.Type.GetType("System.String")); } for (i = 0; i < iNumofWeek; i++) { sTemp = ClassIm.GetWeek(dateTimePickerP.Value, i); sTemp = ClassIm.sYear + "-" + sTemp; dtOrder.Columns.Add(sTemp, System.Type.GetType("System.String")); } sqlConn.Open(); bFirst = true; sqlComm.CommandText = "SELECT product.ID, product.[Product Name], product.[Product Code], indentor.ID AS iID, indentor.[Indentor Name],indentor.[Indentor Code], product.[Number of IMEI], product.[Failure Rate] FROM product CROSS JOIN indentor "; if (bPCode && textBoxPC.Text.Trim() != "") { sqlComm.CommandText += " WHERE (product.[Product Code] LIKE N'%" + textBoxPC.Text.Trim() + "%')"; bFirst = false; } if (bICode && textBoxIC.Text.Trim() != "") { if (bFirst) { sqlComm.CommandText += " WHERE (indentor.[Indentor Code] LIKE N'%" + textBoxIC.Text.Trim() + "%')"; } else { sqlComm.CommandText += " AND (indentor.[Indentor Code] LIKE N'%" + textBoxIC.Text.Trim() + "%')"; } } if (dSet.Tables.Contains("pi")) { dSet.Tables["pi"].Clear(); } sqlDA.Fill(dSet, "pi"); bFirst = true; sqlComm.CommandText = "SELECT ID, [TAC Code], [Product ID], [Product Code], [Indentor ID], [Indentor Code], [Init Number] FROM TAC"; if (bPCode && textBoxPC.Text.Trim() != "") { sqlComm.CommandText += " WHERE (TAC.[Product Code] LIKE N'%" + textBoxPC.Text.Trim() + "%')"; bFirst = false; } if (bICode && textBoxIC.Text.Trim() != "") { if (bFirst) { sqlComm.CommandText += " WHERE (TAC.[Indentor Code] LIKE N'%" + textBoxIC.Text.Trim() + "%')"; } else { sqlComm.CommandText += " AND (TAC.[Indentor Code] LIKE N'%" + textBoxIC.Text.Trim() + "%')"; } } sqlComm.CommandText += " ORDER BY [Init Number]"; if (dSet.Tables.Contains("TAC")) { dSet.Tables["TAC"].Clear(); } sqlDA.Fill(dSet, "TAC"); bFirst = true; sqlComm.CommandText = "SELECT ID, [Product ID], [Product Code], [Indentor ID], [Indentor Code], [End Number], Year, [Num of Week], [Start Number], Date, [TAC ID], [TAC Code], Status FROM acquire"; if (bPCode && textBoxPC.Text.Trim() != "") { sqlComm.CommandText += " WHERE ([Product Code] LIKE N'%" + textBoxPC.Text.Trim() + "%')"; bFirst = false; } if (bICode && textBoxIC.Text.Trim() != "") { if (bFirst) { sqlComm.CommandText += " WHERE ([Indentor Code] LIKE N'%" + textBoxIC.Text.Trim() + "%')"; } else { sqlComm.CommandText += " AND ([Indentor Code] LIKE N'%" + textBoxIC.Text.Trim() + "%')"; } } if (dSet.Tables.Contains("acquire")) { dSet.Tables["acquire"].Clear(); } sqlDA.Fill(dSet, "acquire"); bFirst = true; sqlComm.CommandText = "SELECT actual_1.[Product ID], actual_1.[Product Code], actual_1.[Indentor ID], actual_1.[Indentor Code], actual_1.[Start Number], actual_1.[End Number], actual_1.[Acquire ID], actual_1.Date, actual_1.Year, actual_1.[Num of Week], actual_1.[TAC ID], actual_1.[TAC Code], actual_1.[Total Number] FROM actual INNER JOIN actual AS actual_1 ON actual.[Product Code] = actual_1.[Product Code] AND actual.[Indentor Code] = actual_1.[Indentor Code] GROUP BY actual_1.[Product ID], actual_1.[Product Code], actual_1.[Indentor ID], actual_1.[Indentor Code], actual_1.[Start Number], actual_1.[End Number], actual_1.[Acquire ID], actual_1.Date, actual_1.Year, actual_1.[Num of Week], actual_1.[TAC ID], actual_1.[TAC Code], actual_1.[Total Number]"; if (bPCode && textBoxPC.Text.Trim() != "") { sqlComm.CommandText += " HAVING (actual_1.[Product Code] LIKE N'%" + textBoxPC.Text.Trim() + "%')"; bFirst = false; } if (bICode && textBoxIC.Text.Trim() != "") { if (bFirst) { sqlComm.CommandText += " HAVING (actual_1.[Indentor Code] LIKE N'%" + textBoxIC.Text.Trim() + "%')"; } else { sqlComm.CommandText += " AND (actual_1.[Indentor Code] LIKE N'%" + textBoxIC.Text.Trim() + "%')"; } } if (dSet.Tables.Contains("actual")) { dSet.Tables["actual"].Clear(); } sqlDA.Fill(dSet, "actual"); bFirst = true; sqlComm.CommandText = "SELECT ID, [Product ID], [Product Code], [Indentor ID], [Indentor Code], Year, [Num of Week], Numbers, backlog FROM orders "; if (bPCode && textBoxPC.Text.Trim() != "") { sqlComm.CommandText += " WHERE ([Product Code] LIKE N'%" + textBoxPC.Text.Trim() + "%')"; bFirst = false; } if (bICode && textBoxIC.Text.Trim() != "") { if (bFirst) { sqlComm.CommandText += " WHERE ([Indentor Code] LIKE N'%" + textBoxIC.Text.Trim() + "%')"; } else { sqlComm.CommandText += " AND ([Indentor Code] LIKE N'%" + textBoxIC.Text.Trim() + "%')"; } } if (dSet.Tables.Contains("orders")) { dSet.Tables["orders"].Clear(); } sqlDA.Fill(dSet, "orders"); sqlConn.Close(); object[] oTemp = new object[ICOLUMNS + iNumofWeek + iNumofPass]; dtOrder.Clear(); for (i = 0; i < dSet.Tables["pi"].Rows.Count; i++) { oTemp[0] = dSet.Tables["pi"].Rows[i][0]; oTemp[1] = dSet.Tables["pi"].Rows[i][2]; oTemp[2] = dSet.Tables["pi"].Rows[i][6]; oTemp[3] = dSet.Tables["pi"].Rows[i][7]; oTemp[4] = dSet.Tables["pi"].Rows[i][3]; oTemp[5] = dSet.Tables["pi"].Rows[i][5]; //TAC Manufacture oTemp[6] = 0; oTemp[7] = ""; oTemp[8] = 0; oTemp[9] = 0; oTemp[10] = 0; oTemp[11] = "2000-1"; var q2 = from dt1 in dSet.Tables["actual"].AsEnumerable() //查询最后 where (dt1.Field <string>("Product Code") == oTemp[1].ToString()) && (dt1.Field <string>("Indentor Code") == oTemp[5].ToString()) //条件 select dt1; foreach (var item in q2)//显示查询结果 { //TAC oTemp[6] = item.Field <int>("TAC ID"); oTemp[7] = item.Field <string>("TAC Code").ToString(); var qTAC = from dt2 in dSet.Tables["TAC"].AsEnumerable() //得到TAC初始值 where (dt2.Field <string>("Product Code") == oTemp[1].ToString()) && (dt2.Field <string>("Indentor Code") == oTemp[5].ToString()) && (dt2.Field <string>("TAC Code") == oTemp[7].ToString()) //条件 select dt2; foreach (var item1 in qTAC) //显示查询结果 { oTemp[8] = item1.Field <int>("Init Number"); break; } //Manufacture oTemp[9] = item.Field <int>("End Number"); oTemp[10] = item.Field <int>("Total Number"); oTemp[11] = item.Field <int>("Year").ToString() + "-" + item.Field <string>("Num of Week"); break; } //acquire oTemp[12] = 0; oTemp[13] = ""; var q3 = from dt1 in dSet.Tables["acquire"].AsEnumerable() //查询最后 where (dt1.Field <string>("Product Code") == oTemp[1].ToString()) && (dt1.Field <string>("Indentor Code") == oTemp[5].ToString()) //条件 orderby dt1.Field <int>("End Number") descending select dt1; foreach (var item in q3)//显示查询结果 { oTemp[12] = item.Field <int>("End Number"); oTemp[13] = item.Field <int>("Year").ToString() + "-" + item.Field <string>("Num of Week"); break; } //剩余号码 oTemp[14] = getSurplus(oTemp[1].ToString(), oTemp[5].ToString(), int.Parse(oTemp[9].ToString())); //ORDER numbers k = ICOLUMNS; for (j = iNumofPass; j > 0; j--) { oTemp[k] = 0; //得到日期 string[] sT = dtOrder.Columns[k].Caption.Split('-'); if (sT.Length == 2) { var q4 = from dt1 in dSet.Tables["orders"].AsEnumerable() //查询最后 where (dt1.Field <string>("Product Code") == oTemp[1].ToString()) && (dt1.Field <string>("Indentor Code") == oTemp[5].ToString()) && (dt1.Field <int>("Year") == int.Parse(sT[0])) && (dt1.Field <string>("Num of Week") == sT[1]) //条件 select dt1; foreach (var item in q4) //显示查询结果 { oTemp[k] = item.Field <int>("Numbers"); break; } } k++; } for (j = 0; j < iNumofWeek; j++) { oTemp[k] = 0; //得到日期 string[] sT = dtOrder.Columns[k].Caption.Split('-'); if (sT.Length == 2) { var q5 = from dt1 in dSet.Tables["orders"].AsEnumerable() //查询最后 where (dt1.Field <string>("Product Code") == oTemp[1].ToString()) && (dt1.Field <string>("Indentor Code") == oTemp[5].ToString()) && (dt1.Field <int>("Year") == int.Parse(sT[0])) && (dt1.Field <string>("Num of Week") == sT[1]) //条件 select dt1; foreach (var item in q5) //显示查询结果 { oTemp[k] = item.Field <int>("Numbers"); break; } } k++; } dtOrder.Rows.Add(oTemp); } dataGridViewP.DataSource = dtOrder; for (i = 0; i < dataGridViewP.Columns.Count; i++) { dataGridViewP.Columns[i].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells; dataGridViewP.Columns[i].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter; } for (i = 0; i < dataGridViewP.Columns.Count; i++) { dataGridViewP.Columns[i].ReadOnly = true; } dataGridViewP.Columns[0].Visible = false; dataGridViewP.Columns[4].Visible = false; dataGridViewP.Columns[6].Visible = false; dataGridViewP.Columns[8].Visible = false; dataGridViewP.Columns[9].Visible = false; //dataGridViewP.Columns[0].Visible = false; //dataGridViewP.Columns[0].Visible = false; setSTAUS(); //低于实际时间不可编辑 //for (i = 0; i < dataGridViewP.RowCount; i++) //{ // string[] sT = dataGridViewP.Rows[i].Cells[7].Value.ToString().Split('-'); // if(sT.Length==2) // { // iTemp = int.Parse(sT[0]) * 100 + int.Parse(sT[1]); // for (j = 10; j < dataGridViewP.ColumnCount; j++) //判断 // { // string[] sT1 = dataGridViewP.Columns[j].Name.Split('-'); // iTemp1 = int.Parse(sT1[0]) * 100 + int.Parse(sT1[1]); // if (iTemp1 >= iTemp) // dataGridViewP.Rows[i].Cells[j].ReadOnly = false; // else // dataGridViewP.Rows[i].Cells[j].ReadOnly = true; // } // } //} //make arraylist alIme.Clear(); for (i = 0; i < dataGridViewP.RowCount; i++) { string[] sT = dataGridViewP.Rows[i].Cells[11].Value.ToString().Split('-'); iTemp = int.Parse(sT[0]) * 100 + int.Parse(sT[1]); var q10 = from dt1 in dSet.Tables["orders"].AsEnumerable()//大于起始时间Order where (dt1.Field <string>("Product Code") == dataGridViewP.Rows[i].Cells[1].Value.ToString()) && (dt1.Field <string>("Indentor Code") == dataGridViewP.Rows[i].Cells[5].Value.ToString()) && (dt1.Field <int>("Year") * 100 + int.Parse(dt1.Field <string>("Num of Week")) >= iTemp) orderby(dt1.Field <int>("Year") * 100 + int.Parse(dt1.Field <string>("Num of Week"))) select dt1; foreach (var item in q10)//显示查询结果 { cIme cime = new cIme(int.Parse(dataGridViewP.Rows[i].Cells[0].Value.ToString()), dataGridViewP.Rows[i].Cells[1].Value.ToString(), int.Parse(dataGridViewP.Rows[i].Cells[4].Value.ToString()), dataGridViewP.Rows[i].Cells[5].Value.ToString(), int.Parse(dataGridViewP.Rows[i].Cells[2].Value.ToString())); cime.iYear = item.Field <int>("Year"); cime.iWeek = int.Parse(item.Field <string>("Num of Week")); cime.iCount = item.Field <int>("Numbers"); cime.iBacklog = item.Field <int>("backlog"); cime.dFailureRate = decimal.Parse(dataGridViewP.Rows[i].Cells[3].Value.ToString()); alIme.Add(cime); } //加入未有数据 for (j = ICOLUMNS; j < dataGridViewP.Columns.Count; j++) { //判断是有效日期 string[] sT1 = dataGridViewP.Columns[j].Name.Split('-'); iTemp1 = int.Parse(sT1[0]) * 100 + int.Parse(sT1[1]); if (iTemp1 <= iTemp) { continue; } var alQuery = from cIme cime in alIme where (cime.sPCode == dataGridViewP.Rows[i].Cells[1].Value.ToString()) && (cime.sICode == dataGridViewP.Rows[i].Cells[5].Value.ToString()) && (cime.iYear == int.Parse(sT1[0])) && (cime.iWeek == int.Parse(sT1[1])) select cime; //foreach (cIme s in alQuery) //{ //} if (alQuery.Count() < 1) //数据库没有记录,加入预订为0的数据 { cIme cime1 = new cIme(int.Parse(dataGridViewP.Rows[i].Cells[0].Value.ToString()), dataGridViewP.Rows[i].Cells[1].Value.ToString(), int.Parse(dataGridViewP.Rows[i].Cells[4].Value.ToString()), dataGridViewP.Rows[i].Cells[5].Value.ToString(), int.Parse(dataGridViewP.Rows[i].Cells[2].Value.ToString())); cime1.iYear = int.Parse(sT1[0]); cime1.iWeek = int.Parse(sT1[1]); cime1.iCount = 0; cime1.dFailureRate = decimal.Parse(dataGridViewP.Rows[i].Cells[3].Value.ToString()); alIme.Add(cime1); } } } //manageArrayList(); countAmount(-1); dataGridViewP.RowValidating += dataGridViewP_RowValidating; dataGridViewP.CellValidating += dataGridViewP_CellValidating; }
private void btnLoad_Click(object sender, EventArgs e) { int i, j, iTemp = 0, iTemp1 = 0, iTemp2 = 0, iTemp3 = 0; int iSheet; DataSet dsCSV = new DataSet(); int iI = 0, iP = 0; string sICode = "", sPCode = ""; int iTAC = 0; string sTAC = ""; string sNowWeek = ClassIm.GetWeek(dateTimePickerP.Value).ToString(); int iYear = 0, iWeek = 0; OpenFileDialog openFileDialogOutput = new OpenFileDialog(); openFileDialogOutput.Filter = "EXCEL files(*.xlsx)|*.xlsx|EXCEL files(*.xls)|*.xls";// openFileDialogOutput.FilterIndex = 0; openFileDialogOutput.RestoreDirectory = true; if (openFileDialogOutput.ShowDialog() != DialogResult.OK) { return; } Microsoft.Office.Interop.Excel.Application m_xlsApp = null; Workbook m_Workbook = null; Worksheet m_Worksheet = null; toolStripProgressBarALL.Value = 0; toolStripProgressBarExcel.Value = 0; textBoxLOG.Text = "Import Data:\r\n filename:" + openFileDialogOutput.FileName + "\r\n\r\n"; textBoxERROR.Text = "Error Note:\r\n filename:" + openFileDialogOutput.FileName + "\r\n\r\n"; sqlConn.Open(); try { object objOpt = System.Reflection.Missing.Value; m_xlsApp = new Microsoft.Office.Interop.Excel.Application(); m_Workbook = m_xlsApp.Workbooks.Open(openFileDialogOutput.FileName, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt); toolStripProgressBarALL.Maximum = m_Workbook.Worksheets.Count + 2; #region accquire //TAC Accquire First for (iSheet = 1; iSheet <= m_Workbook.Worksheets.Count; iSheet++) { m_Worksheet = (Microsoft.Office.Interop.Excel.Worksheet)m_Workbook.Worksheets.get_Item(iSheet); if (m_Worksheet.Name.IndexOf(sMaster) == -1) { continue; } toolStripProgressBarALL.Value++; textBoxLOG.Text += "\r\nRead TABLE '" + m_Worksheet.Name + "'\r\n"; if (m_Worksheet.UsedRange.Rows.Count < 1 && m_Worksheet.UsedRange.Columns.Count < 8) { continue; } toolStripProgressBarExcel.Maximum = m_Worksheet.UsedRange.Rows.Count + 1; toolStripProgressBarExcel.Value = 0; for (i = 1; i <= m_Worksheet.UsedRange.Rows.Count; i++) { toolStripProgressBarExcel.Value++; toolStripStatusLabelWarn.Text = "LINE:" + toolStripProgressBarExcel.Value.ToString(); //跳过空行 if (((Range)(m_Worksheet.Cells[i, 1])) == null) { continue; } //跳过描述行 if (((Range)(m_Worksheet.Cells[i, 1])).Text.ToString().IndexOf(sTAG) >= 0) { continue; } //TAC为空 if (((Range)(m_Worksheet.Cells[i, 6])).Text.ToString() == "") { continue; } //iICODE iI = 0; sICode = ""; sqlComm.CommandText = "SELECT ID, [Indentor Name], [Indentor Code] FROM indentor WHERE ([Indentor Code] = N'" + ((Range)(m_Worksheet.Cells[i, 1])).Text.ToString() + "')"; sqldr = sqlComm.ExecuteReader(); if (sqldr.HasRows) { sqldr.Read(); iI = int.Parse(sqldr.GetValue(0).ToString()); sICode = sqldr.GetValue(2).ToString(); sqldr.Close(); } else { textBoxERROR.Text += m_Worksheet.Name + " Line " + i.ToString() + ": NO Indentors Code\r\n"; sqldr.Close(); continue; } //iPCode iP = 0; sPCode = ""; sqlComm.CommandText = "SELECT ID, [Product Name], [Product Code], [Number of IMEI], Status, [Factory ID], [Factory Code], [Failure Rate] FROM product WHERE ([Product Code] = N'" + ClassIm.GetPCode(((Range)(m_Worksheet.Cells[i, 2])).Text.ToString()) + "')"; sqldr = sqlComm.ExecuteReader(); if (sqldr.HasRows) { sqldr.Read(); iP = int.Parse(sqldr.GetValue(0).ToString()); sPCode = sqldr.GetValue(2).ToString(); sqldr.Close(); } else { textBoxERROR.Text += m_Worksheet.Name + " Line " + i.ToString() + ": NO Product Code\r\n"; sqldr.Close(); continue; } //TAC iTAC = 0; sTAC = ""; sqlComm.CommandText = "SELECT ID, [TAC Code], [Product ID], [Product Code], [Indentor ID], [Indentor Code], [Init Number] FROM TAC WHERE ([Product Code] = N'" + sPCode + "') AND ([Indentor Code] = N'" + sICode + "')"; sqldr = sqlComm.ExecuteReader(); if (sqldr.HasRows) //有TAC { sqldr.Read(); iTAC = int.Parse(sqldr.GetValue(0).ToString()); sTAC = sqldr.GetValue(1).ToString(); sqlComm.CommandText = "UPDATE TAC SET [Init Number] = 0, [Product ID] = " + iP.ToString() + ", [Indentor ID] = " + iI.ToString() + ", [TAC Code] = N'" + ((Range)(m_Worksheet.Cells[i, 6])).Text.ToString() + "' WHERE ([Product Code] = N'" + sPCode + "') AND ([Indentor Code] = N'" + sICode + "')"; sqldr.Close(); sqlComm.ExecuteNonQuery(); } else { sqlComm.CommandText = "INSERT INTO TAC ([Init Number], [Product ID], [Indentor ID], [TAC Code], [Product Code], [Indentor Code]) VALUES (0, " + iP.ToString() + ", " + iI.ToString() + ", N'" + ((Range)(m_Worksheet.Cells[i, 6])).Text.ToString() + "', N'" + sPCode + "', N'" + sICode + "')"; sqldr.Close(); sqlComm.ExecuteNonQuery(); sqlComm.CommandText = "SELECT @@IDENTITY"; sqldr = sqlComm.ExecuteReader(); sqldr.Read(); iTAC = Convert.ToInt32(sqldr.GetValue(0).ToString()); sqldr.Close(); sTAC = ((Range)(m_Worksheet.Cells[i, 6])).Text.ToString(); } //accquire if (((Range)(m_Worksheet.Cells[i, 7])).Text.ToString() == "") { continue; } if (((Range)(m_Worksheet.Cells[i, 8])).Text.ToString() == "") { continue; } sqlComm.CommandText = "SELECT ID, [Product ID], [Product Code], [Indentor ID], [Indentor Code], [Start Number], [End Number], Date, Year, [Num of Week], [TAC ID], [TAC Code], Status FROM acquire WHERE ([Product Code] = N'" + sPCode + "') AND ([Indentor Code] = N'" + sICode + "')"; sqldr = sqlComm.ExecuteReader(); if (sqldr.HasRows) //有申请 { sqlComm.CommandText = "UPDATE acquire SET [Start Number] = " + ((Range)(m_Worksheet.Cells[i, 7])).Text.ToString() + ", [End Number] = " + ((Range)(m_Worksheet.Cells[i, 8])).Text.ToString() + ", Date = CONVERT(DATETIME, '" + dateTimePickerP.Value.ToShortDateString() + " 00:00:00', 102), Year = " + dateTimePickerP.Value.Year.ToString() + ", [Num of Week] = N'" + ClassIm.GetWeek(dateTimePickerP.Value).ToString() + "', [Product ID] = " + iP.ToString() + ", [Indentor ID] = " + iI.ToString() + ", [TAC ID] = " + iTAC.ToString() + ", [TAC Code] = N'" + sTAC + "' WHERE ([Product Code] = N'" + sPCode + "') AND ([Indentor Code] = N'" + sICode + "')";; } else { sqlComm.CommandText = "INSERT INTO acquire ([Start Number], [End Number], Date, Year, [Num of Week], [Product ID], [Indentor ID], [TAC ID], [TAC Code], [Product Code], [Indentor Code]) VALUES (" + ((Range)(m_Worksheet.Cells[i, 7])).Text.ToString() + ", " + ((Range)(m_Worksheet.Cells[i, 8])).Text.ToString() + ", CONVERT(DATETIME, '" + dateTimePickerP.Value.ToShortDateString() + " 00:00:00', 102), " + dateTimePickerP.Value.Year.ToString() + ", N'" + ClassIm.GetWeek(dateTimePickerP.Value).ToString() + "', " + iP.ToString() + ", " + iI.ToString() + ", " + iTAC.ToString() + ", N'" + sTAC + "', N'" + sPCode + "', N'" + sICode + "')"; } sqldr.Close(); sqlComm.ExecuteNonQuery(); } } #endregion //TAC sqlComm.CommandText = "SELECT ID, [TAC Code], [Product ID], [Product Code], [Indentor ID], [Indentor Code], [Init Number] FROM TAC"; if (dSet.Tables.Contains("TAC")) { dSet.Tables["TAC"].Clear(); } sqlDA.Fill(dSet, "TAC"); //manufacture & Order alSales.Clear(); alSAP.Clear(); #region Order for (iSheet = 1; iSheet <= m_Workbook.Worksheets.Count; iSheet++) { m_Worksheet = (Microsoft.Office.Interop.Excel.Worksheet)m_Workbook.Worksheets.get_Item(iSheet); //if (m_Worksheet.Name.IndexOf(sSAPorder) >= 0) // continue; //if (m_Worksheet.Name.IndexOf(sSalesplan) >= 0) // continue; //************************************************************************************** //manufacture if (m_Worksheet.Name.IndexOf(sCWIMEI) >= 0) { toolStripProgressBarALL.Value++; textBoxLOG.Text += "\r\nRead TABLE '" + m_Worksheet.Name + "'\r\n"; if (m_Worksheet.UsedRange.Rows.Count < 2 && m_Worksheet.UsedRange.Columns.Count < 4) { textBoxERROR.Text += m_Worksheet.Name + " DATA Format Wrong\r\n"; continue; } toolStripProgressBarExcel.Maximum = m_Worksheet.UsedRange.Rows.Count + 1; toolStripProgressBarExcel.Value = 0; for (i = 2; i <= m_Worksheet.UsedRange.Rows.Count; i++) { toolStripProgressBarExcel.Value++; toolStripStatusLabelWarn.Text = "LINE:" + toolStripProgressBarExcel.Value.ToString(); //if (i == 51) //{ // int kkk = 0; //} //跳过空行 if (((Range)(m_Worksheet.Cells[i, 3])) == null) { continue; } if (((Range)(m_Worksheet.Cells[i, 3])).Value2.ToString().Length != 15) { textBoxERROR.Text += m_Worksheet.Name + " Line " + i.ToString() + ": Data Error - Length is not 15\r\n"; continue; } //iICODE iI = 0; sICode = ""; sqlComm.CommandText = "SELECT ID, [Indentor Name], [Indentor Code] FROM indentor WHERE ([Indentor Code] = N'" + ((Range)(m_Worksheet.Cells[i, 1])).Text.ToString() + "')"; sqldr = sqlComm.ExecuteReader(); if (sqldr.HasRows) { sqldr.Read(); iI = int.Parse(sqldr.GetValue(0).ToString()); sICode = sqldr.GetValue(2).ToString(); sqldr.Close(); } else { textBoxERROR.Text += m_Worksheet.Name + " Line " + i.ToString() + ": NO Indentors Code\r\n"; sqldr.Close(); continue; } //iPCode iP = 0; sPCode = ""; sqlComm.CommandText = "SELECT ID, [Product Name], [Product Code], [Number of IMEI], Status, [Factory ID], [Factory Code], [Failure Rate] FROM product WHERE ([Product Code] = N'" + ClassIm.GetPCode(((Range)(m_Worksheet.Cells[i, 2])).Text.ToString()) + "')"; sqldr = sqlComm.ExecuteReader(); if (sqldr.HasRows) { sqldr.Read(); iP = int.Parse(sqldr.GetValue(0).ToString()); sPCode = sqldr.GetValue(2).ToString(); sqldr.Close(); } else { textBoxERROR.Text += m_Worksheet.Name + " Line " + i.ToString() + ": NO Product Code\r\n"; sqldr.Close(); continue; } //检查TAC iTAC = 0; sTAC = ""; var qTAC = from dt2 in dSet.Tables["TAC"].AsEnumerable() //得到TAC初始值 where (dt2.Field <string>("Product Code") == sPCode) && (dt2.Field <string>("Indentor Code") == sICode) //条件 select dt2; if (qTAC.Count() <= 0) { continue; } foreach (var itemTAC in qTAC)//显示查询结果//有TAC { iTAC = itemTAC.Field <int>("Init Number"); sTAC = itemTAC.Field <string>("TAC Code"); break; } //manufacture iTemp1 = int.Parse(((Range)(m_Worksheet.Cells[i, 3])).Value2.ToString().Substring(8, 6)); if (((Range)(m_Worksheet.Cells[i, 3])).Value2.ToString().Substring(0, 8) == "--------") { iTemp1 = -1 * iTemp1; } sqlComm.CommandText = "SELECT ID, [Product ID], [Product Code], [Indentor ID], [Indentor Code], [Start Number], [End Number], [Acquire ID], Date, Year, [Num of Week], [TAC ID], [TAC Code], [Total Number] FROM actual WHERE ([Product Code] = N'" + sPCode + "') AND ([Indentor Code] = N'" + sICode + "')"; sqldr = sqlComm.ExecuteReader(); if (sqldr.HasRows) { sqldr.Read(); iTemp = 0; if (sqldr.GetValue(6).ToString() == "") { iTemp = 0; } else { iTemp = int.Parse(sqldr.GetValue(6).ToString()) + 1; } sqlComm.CommandText = "UPDATE actual SET [Product ID] = " + iP.ToString() + ", [Indentor ID] = " + iI.ToString() + ", [Start Number] = " + iTemp.ToString() + ", [End Number] = " + iTemp1.ToString() + ", Date = CONVERT(DATETIME, '" + dateTimePickerP.Value.ToShortDateString() + " 00:00:00', 102), Year = " + dateTimePickerP.Value.Year.ToString() + ", [Num of Week] = N'" + sNowWeek + "', [TAC ID] = " + iTAC.ToString() + ", [TAC Code] = N'" + sTAC + "', [Total Number] = " + iTemp1.ToString() + " WHERE ([Product Code] = N'" + sPCode + "') AND ([Indentor Code] = N'" + sICode + "')"; } else { sqlComm.CommandText = "INSERT INTO actual ([Product ID], [Indentor ID], [Start Number], [End Number], Date, Year, [Num of Week], [TAC ID], [TAC Code], [Total Number], [Product Code], [Indentor Code]) VALUES (" + iP.ToString() + ", " + iI.ToString() + ", 0, " + iTemp1 + ", CONVERT(DATETIME, '" + dateTimePickerP.Value.ToShortDateString() + " 00:00:00', 102), " + dateTimePickerP.Value.Year.ToString() + ", N'" + sNowWeek + "', " + iTAC.ToString() + ", N'" + sTAC + "', " + iTemp1.ToString() + ", N'" + sPCode + "', N'" + sICode + "')"; } sqldr.Close(); sqlComm.ExecuteNonQuery(); //backlog if (((Range)(m_Worksheet.Cells[i, 4])).Text.ToString() == "") { iTemp1 = 0; } else { iTemp1 = int.Parse(((Range)(m_Worksheet.Cells[i, 4])).Value2.ToString()); } sqlComm.CommandText = "SELECT ID, [Product ID], [Product Code], [Indentor ID], [Indentor Code], [Start Number], [End Number], [Acquire ID], Date, Year, [Num of Week], Numbers, [Order Start Number], [Order End Number], backlog FROM orders WHERE ([Product Code] = N'" + sPCode + "') AND ([Indentor Code] = N'" + sICode + "') AND (Year = " + dateTimePickerP.Value.Year.ToString() + ") AND ([Num of Week] = N'" + sNowWeek + "')"; sqldr = sqlComm.ExecuteReader(); if (sqldr.HasRows) { sqlComm.CommandText = "UPDATE orders SET backlog = " + iTemp1.ToString() + " WHERE ([Product Code] = N'" + sPCode + "') AND ([Indentor Code] = N'" + sICode + "') AND (Year = " + dateTimePickerP.Value.Year.ToString() + ") AND ([Num of Week] = N'" + sNowWeek + "')"; } else { sqlComm.CommandText = "INSERT INTO orders ([Product ID], [Product Code], [Indentor ID], [Indentor Code], [Start Number], [End Number], Date, Year, [Num of Week], Numbers, [Order Start Number], [Order End Number], backlog) VALUES (" + iP.ToString() + ", N'" + sPCode + "', " + iI.ToString() + ", N'" + sICode + "', 0, 0, CONVERT(DATETIME, '" + dateTimePickerP.Value.ToShortDateString() + " 00:00:00', 102), " + dateTimePickerP.Value.Year.ToString() + ", N'" + sNowWeek + "', 0, 0, 0, " + iTemp1 + ")"; } sqldr.Close(); sqlComm.ExecuteNonQuery(); } } //**************************************************************************************** //SAP #region SAP if (m_Worksheet.Name.IndexOf(sSAPorder) >= 0) { toolStripProgressBarALL.Value++; textBoxLOG.Text += "\r\nRead TABLE '" + m_Worksheet.Name + "'\r\n"; if (m_Worksheet.UsedRange.Rows.Count < 2 && m_Worksheet.UsedRange.Columns.Count < 14) { textBoxERROR.Text += m_Worksheet.Name + " DATA Format Wrong\r\n"; continue; } toolStripProgressBarExcel.Maximum = m_Worksheet.UsedRange.Rows.Count + 1; toolStripProgressBarExcel.Value = 0; for (i = 2; i <= m_Worksheet.UsedRange.Rows.Count; i++) { //if (i == 553) //{ // int kkk = 0; //} //跳过空行 toolStripProgressBarExcel.Value++; toolStripStatusLabelWarn.Text = "LINE:" + toolStripProgressBarExcel.Value.ToString(); if (((Range)(m_Worksheet.Cells[i, 14])) == null) { break; } if (((Range)(m_Worksheet.Cells[i, 14])).Text.ToString() == "") { break; } if (((Range)(m_Worksheet.Cells[i, 1])).Value2.ToString() != "") //第一列检查 { continue; } sPCode = ClassIm.GetPCode(((Range)(m_Worksheet.Cells[i, 9])).Value2.ToString()); sICode = ((Range)(m_Worksheet.Cells[i, 5])).Value2.ToString(); string[] sT = ((Range)(m_Worksheet.Cells[i, 11])).Value2.ToString().Split('.'); var alQuery = from cImeOrder cime in alSAP where (cime.sPCode == sPCode && (cime.sICode == sICode) && (cime.iYear == int.Parse(sT[0])) && (cime.iWeek == int.Parse(sT[1]))) select cime; if (alQuery.Count() <= 0) //没有记录 { //iICODE iI = 0; sqlComm.CommandText = "SELECT ID, [Indentor Name], [Indentor Code] FROM indentor WHERE ([Indentor Code] = N'" + sICode + "')"; sqldr = sqlComm.ExecuteReader(); if (sqldr.HasRows) { sqldr.Read(); iI = int.Parse(sqldr.GetValue(0).ToString()); sICode = sqldr.GetValue(2).ToString(); sqldr.Close(); } else { textBoxERROR.Text += m_Worksheet.Name + " Line " + i.ToString() + ": NO Indentors Code\r\n"; sqldr.Close(); continue; } //iPCode iP = 0; sqlComm.CommandText = "SELECT ID, [Product Name], [Product Code], [Number of IMEI], Status, [Factory ID], [Factory Code], [Failure Rate] FROM product WHERE ([Product Code] = N'" + sPCode + "')"; sqldr = sqlComm.ExecuteReader(); if (sqldr.HasRows) { sqldr.Read(); iP = int.Parse(sqldr.GetValue(0).ToString()); sPCode = sqldr.GetValue(2).ToString(); sqldr.Close(); } else { textBoxERROR.Text += m_Worksheet.Name + " Line " + i.ToString() + ": NO Product Code\r\n"; sqldr.Close(); continue; } cImeOrder ci = new cImeOrder(iP, sPCode, iI, sICode, int.Parse(((Range)(m_Worksheet.Cells[i, 14])).Value2.ToString()), int.Parse(sT[0]), int.Parse(sT[1])); alSAP.Add(ci); } else //存在记录,直接加入 { foreach (cImeOrder s in alQuery) { s.iNum += int.Parse(((Range)(m_Worksheet.Cells[i, 14])).Value2.ToString()); } } } } #endregion //**************************************************************************************** //SALES #region SALES iTemp = int.Parse(ClassIm.GetWeek(dateTimePickerP.Value, iSAPControlNumber - 1)); iTemp = int.Parse(ClassIm.sYear) * 100 + iTemp; if (m_Worksheet.Name.IndexOf(sSalesplan) >= 0) { toolStripProgressBarALL.Value++; textBoxLOG.Text += "\r\nRead TABLE '" + m_Worksheet.Name + "'\r\n"; if (m_Worksheet.UsedRange.Rows.Count < 2 && m_Worksheet.UsedRange.Columns.Count < 3) { textBoxERROR.Text += m_Worksheet.Name + " DATA Format Wrong\r\n"; continue; } toolStripProgressBarExcel.Maximum = m_Worksheet.UsedRange.Rows.Count + 1; toolStripProgressBarExcel.Value = 0; for (i = 2; i <= m_Worksheet.UsedRange.Rows.Count; i++) { toolStripProgressBarExcel.Value++; toolStripStatusLabelWarn.Text = "LINE:" + toolStripProgressBarExcel.Value.ToString(); //if (i == 96) //{ // int kkk = 0; //} //iICODE iI = 0; sICode = ""; sqlComm.CommandText = "SELECT ID, [Indentor Name], [Indentor Code] FROM indentor WHERE ([Indentor Code] = N'" + ((Range)(m_Worksheet.Cells[i, 1])).Value2.ToString() + "')"; sqldr = sqlComm.ExecuteReader(); if (sqldr.HasRows) { sqldr.Read(); iI = int.Parse(sqldr.GetValue(0).ToString()); sICode = sqldr.GetValue(2).ToString(); sqldr.Close(); } else { textBoxERROR.Text += m_Worksheet.Name + " Line " + i.ToString() + ": NO Indentors Code\r\n"; sqldr.Close(); continue; } //iPCode iP = 0; sPCode = ""; sqlComm.CommandText = "SELECT ID, [Product Name], [Product Code], [Number of IMEI], Status, [Factory ID], [Factory Code], [Failure Rate] FROM product WHERE ([Product Code] = N'" + ClassIm.GetPCode(((Range)(m_Worksheet.Cells[i, 2])).Value2.ToString()) + "')"; sqldr = sqlComm.ExecuteReader(); if (sqldr.HasRows) { sqldr.Read(); iP = int.Parse(sqldr.GetValue(0).ToString()); sPCode = sqldr.GetValue(2).ToString(); sqldr.Close(); } else { textBoxERROR.Text += m_Worksheet.Name + " Line " + i.ToString() + ": NO Product Code\r\n"; sqldr.Close(); continue; } for (j = 3; j <= m_Worksheet.UsedRange.Columns.Count; j++) { //跳过空行 if (((Range)(m_Worksheet.Cells[i, j])) == null) { continue; } if (((Range)(m_Worksheet.Cells[1, j])).Text.ToString() == "") { continue; } if (((Range)(m_Worksheet.Cells[1, j])).Text.ToString().Length < 8) { continue; } iYear = int.Parse(((Range)(m_Worksheet.Cells[1, j])).Text.ToString().Substring(0, 4)); iWeek = int.Parse(((Range)(m_Worksheet.Cells[1, j])).Text.ToString().Substring(6, 2)); if (iYear * 100 + iWeek <= iTemp) { if (checkBoxDetail.Checked) { textBoxLOG.Text += "Sale plan" + iYear.ToString() + "-" + iWeek.ToString() + " ignore \r\n"; } iTemp3 = 0; } else { iTemp3 = int.Parse(((Range)(m_Worksheet.Cells[i, j])).Value2.ToString()); } var alQuery = from cImeOrder cime in alSales where ((cime.sPCode == sPCode) && (cime.sICode == sICode) && (cime.iYear == iYear) && (cime.iWeek == iWeek)) select cime; if (alQuery.Count() <= 0) //没有记录 { cImeOrder ci = new cImeOrder(iP, sPCode, iI, sICode, iTemp3, iYear, iWeek); alSales.Add(ci); } else //存在记录,直接加入 { foreach (cImeOrder s in alQuery) { s.iNum = iTemp3; } } } } } #endregion }//read EXCEL //处理ORDER //SAP有效周记数 toolStripProgressBarALL.Value = toolStripProgressBarALL.Maximum - 2; textBoxLOG.Text += "\r\nCalculate SAP order & Sales plan:\r\n"; iTemp = int.Parse(ClassIm.GetWeek(dateTimePickerP.Value, iSAPControlNumber - 1)); iTemp = int.Parse(ClassIm.sYear) * 100 + iTemp; iTemp1 = dateTimePickerP.Value.Year * 100 + int.Parse(sNowWeek); toolStripProgressBarExcel.Maximum = alSAP.Count + 1; toolStripProgressBarExcel.Value = 0; foreach (cImeOrder cime in alSAP) { toolStripProgressBarExcel.Value++; toolStripStatusLabelWarn.Text = "ITEM:" + toolStripProgressBarExcel.Value.ToString(); this.Refresh(); textBoxLOG.ScrollToCaret(); textBoxERROR.ScrollToCaret(); var alQuery = from cImeOrder cimeSales in alSales where ((cimeSales.sPCode == cime.sPCode) && (cimeSales.sICode == cime.sICode) && (cimeSales.iYear == cime.iYear) && (cimeSales.iWeek == cime.iWeek)) select cimeSales; if (alQuery.Count() < 1) //没有对应的Sales { alSales.Add(cime); } else { foreach (cImeOrder ci in alQuery) //存在对应的sales记录 ci->sales { if (cime.iYear * 100 + cime.iWeek > iTemp) //不在SAP控制范围内 { ci.iNum = Math.Max(ci.iNum, cime.iNum); //取大值 if (checkBoxDetail.Checked) { textBoxLOG.Text += ci.iYear.ToString() + "-" + ci.iWeek.ToString() + " " + ci.sICode + "|" + ci.sPCode + ":" + ci.iNum.ToString() + "=MAX(" + ci.iNum + "," + cime.iNum + ")\r\n"; } } else //在sap控制范围内 { ci.iNum = cime.iNum; //取SAP值 if (checkBoxDetail.Checked) { textBoxLOG.Text += ci.iYear.ToString() + "-" + ci.iWeek.ToString() + " " + ci.sICode + "|" + ci.sPCode + ":SAP(" + ci.iNum + ")\r\n"; } } } } } //导入数据库 System.Data.SqlClient.SqlTransaction sqlta; sqlta = sqlConn.BeginTransaction(); sqlComm.Transaction = sqlta; toolStripProgressBarALL.Value = toolStripProgressBarALL.Maximum - 1; textBoxLOG.Text += "\r\nInsert Data to Database:\r\n"; toolStripProgressBarExcel.Maximum = alSales.Count + 1; toolStripProgressBarExcel.Value = 0; try { foreach (cImeOrder cime1 in alSales) { toolStripProgressBarExcel.Value++; ScrollToCaret(); this.Refresh(); toolStripStatusLabelWarn.Text = "ITEM:" + toolStripProgressBarExcel.Value.ToString(); sqlComm.CommandText = "SELECT ID, [Product ID], [Product Code], [Indentor ID], [Indentor Code], [Start Number], [End Number], [Acquire ID], Date, Year, [Num of Week], Numbers, [Order Start Number], [Order End Number], backlog FROM orders WHERE ([Product Code] = N'" + cime1.sPCode + "') AND ([Indentor Code] = N'" + cime1.sICode + "') AND (Year = " + cime1.iYear.ToString() + ") AND ([Num of Week] = N'" + cime1.iWeek.ToString() + "')"; sqldr = sqlComm.ExecuteReader(); if (sqldr.HasRows) { sqlComm.CommandText = "UPDATE orders SET backlog = " + iTemp1.ToString() + " WHERE ([Product Code] = N'" + sPCode + "') AND ([Indentor Code] = N'" + sICode + "') AND (Year = " + dateTimePickerP.Value.Year.ToString() + ") AND ([Num of Week] = N'" + sNowWeek + "')"; sqlComm.CommandText = "UPDATE orders SET [Start Number] = 0, [End Number] = 0, [Acquire ID] = 0, Numbers = " + cime1.iNum.ToString() + ", [Order Start Number] =0, [Order End Number] =0 WHERE ([Product Code] = N'" + cime1.sPCode + "') AND ([Indentor Code] = N'" + cime1.sICode + "') AND (Year = " + cime1.iYear.ToString() + ") AND ([Num of Week] = N'" + cime1.iWeek.ToString() + "')"; } else { sqlComm.CommandText = "INSERT INTO orders ([Product ID], [Product Code], [Indentor ID], [Indentor Code], [Start Number], [End Number], [Acquire ID], Date, Year, [Num of Week], Numbers,[Order Start Number],[Order End Number],backlog) VALUES (" + cime1.iPCode.ToString() + ", N'" + cime1.sPCode + "', " + cime1.iICode.ToString() + ", N'" + cime1.sICode + "', 0, 0, 0, CONVERT(DATETIME, '" + System.DateTime.Now.ToShortDateString() + " 00:00:00', 102), " + cime1.iYear.ToString() + ", N'" + cime1.iWeek.ToString() + "', " + cime1.iNum.ToString() + ",0,0,0)"; } sqldr.Close(); sqlComm.ExecuteNonQuery(); } sqlta.Commit(); } catch (Exception ex) { MessageBox.Show("error:" + ex.Message.ToString(), "error", MessageBoxButtons.OK, MessageBoxIcon.Error); sqlta.Rollback(); } finally { } toolStripProgressBarExcel.Value = toolStripProgressBarExcel.Maximum; toolStripProgressBarALL.Value = toolStripProgressBarALL.Maximum; toolStripStatusLabelWarn.Text = ""; #endregion MessageBox.Show("Input Finish", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception exc) { MessageBox.Show("Input Fail:" + exc.Message.ToString(), "Error"); } finally { sqlConn.Close(); m_Worksheet = null; m_Workbook = null; m_xlsApp.Quit(); int generation = System.GC.GetGeneration(m_xlsApp); m_xlsApp = null; System.GC.Collect(generation); } }
private bool countAmount() { bool bCheck = true; int itemp = 0, itemp1 = 0; int iR = 0; bool bFirst = true; this.dataGridViewP.CellValidating -= dataGridViewP_CellValidating; ClassIm.ClearDataGridViewErrorText(dataGridViewP); for (int i = 0; i < dtAcquire.Rows.Count; i++) { dtAcquire.Rows[i][11] = 0; if (dtAcquire.Rows[i][10].ToString() == "") { dtAcquire.Rows[i][11] = 1; } if (int.Parse(dtAcquire.Rows[i][10].ToString()) < 0) { dtAcquire.Rows[i][11] = 1; } //取值大于跳转 if (int.Parse(dtAcquire.Rows[i][10].ToString()) > iRange) { dtAcquire.Rows[i][11] = 3; } if (int.Parse(dtAcquire.Rows[i][11].ToString()) != 0) { continue; } dtAcquire.Rows[i][11] = 0; //判断区间 itemp = int.Parse(dtAcquire.Rows[i][5].ToString()); itemp1 = itemp + int.Parse(dtAcquire.Rows[i][10].ToString()) * int.Parse(dtAcquire.Rows[i][2].ToString()) - 1; if (!checkBoxCon.Checked) //不连续计算 { iR = itemp / iRange; iR = iR * iRange; bFirst = true; while (true) { if (itemp >= iR && itemp1 <= iR + iRange) //找到区间 { dtAcquire.Rows[i][11] = 0; dtAcquire.Rows[i][6] = itemp; dtAcquire.Rows[i][7] = itemp1; dtAcquire.Rows[i][8] = 0; dtAcquire.Rows[i][9] = ""; var q2 = from dt2 in dSet.Tables["TAC"].AsEnumerable() //查询TAC区间 where (dt2.Field <string>("Product Code") == dtAcquire.Rows[i][1].ToString()) && (dt2.Field <string>("Indentor Code") == dtAcquire.Rows[i][4].ToString()) && (dt2.Field <int>("Init Number") == iR) //条件 select dt2; foreach (var item in q2)//显示查询结果 { dtAcquire.Rows[i][8] = item.Field <int>("ID"); dtAcquire.Rows[i][9] = item.Field <string>("TAC Code"); break; } break; } else { iR += iRange; itemp = iR; itemp1 = itemp + int.Parse(dtAcquire.Rows[i][10].ToString()) * int.Parse(dtAcquire.Rows[i][2].ToString()); } } } else //连续计算 { dtAcquire.Rows[i][11] = 0; dtAcquire.Rows[i][6] = itemp; dtAcquire.Rows[i][7] = itemp1; dtAcquire.Rows[i][8] = 0; dtAcquire.Rows[i][9] = ""; var q2 = from dt2 in dSet.Tables["TAC"].AsEnumerable() //查询TAC区间 where (dt2.Field <string>("Product Code") == dtAcquire.Rows[i][1].ToString()) && (dt2.Field <string>("Indentor Code") == dtAcquire.Rows[i][4].ToString()) && (dt2.Field <int>("Init Number") == iR) //条件 select dt2; foreach (var item in q2)//显示查询结果 { dtAcquire.Rows[i][8] = item.Field <int>("ID"); dtAcquire.Rows[i][9] = item.Field <string>("TAC Code"); break; } } } for (int i = 0; i < dataGridViewP.RowCount; i++) { switch (dataGridViewP.Rows[i].Cells[11].Value.ToString()) { case "1": dataGridViewP.Rows[i].Cells[10].ErrorText = "please input number"; break; //case "2": // dataGridViewP.Rows[i].Cells[10].ErrorText = "NO TAC Define"; // break; case "3": dataGridViewP.Rows[i].Cells[10].ErrorText = "OUT OF TAC Define"; break; default: break; } } this.dataGridViewP.CellValidating += dataGridViewP_CellValidating; //dataGridViewP.EndEdit(); return(bCheck); }
private void toolStripButtonEDIT_Click(object sender, EventArgs e) { int i; if (!countAmount()) { MessageBox.Show("please check data", "information", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return; } System.Data.SqlClient.SqlTransaction sqlta; sqlConn.Open(); sqlta = sqlConn.BeginTransaction(); sqlComm.Transaction = sqlta; try { for (i = 0; i < dataGridViewP.RowCount; i++) { if (int.Parse(dataGridViewP.Rows[i].Cells[11].Value.ToString()) != 0) { continue; } if (int.Parse(dataGridViewP.Rows[i].Cells[10].Value.ToString()) <= 0) { continue; } //dateTimePickerP.Value.w sqlComm.CommandText = "INSERT INTO acquire([Product ID], [Product Code], [Indentor ID], [Indentor Code], [Start Number], [End Number], Date, Year, [Num of Week], [TAC ID], [TAC Code]) VALUES (" + dataGridViewP.Rows[i].Cells[0].Value.ToString() + ", N'" + dataGridViewP.Rows[i].Cells[1].Value.ToString() + "', " + dataGridViewP.Rows[i].Cells[3].Value.ToString() + ", N'" + dataGridViewP.Rows[i].Cells[4].Value.ToString() + "', " + dataGridViewP.Rows[i].Cells[6].Value.ToString() + ", " + dataGridViewP.Rows[i].Cells[7].Value.ToString() + ", '" + System.DateTime.Now.ToShortDateString() + " 00:00:00', " + dateTimePickerP.Value.Year.ToString() + ",N'" + ClassIm.GetWeek(dateTimePickerP.Value).ToString() + "', " + dataGridViewP.Rows[i].Cells[8].Value.ToString() + ", N'" + dataGridViewP.Rows[i].Cells[9].Value.ToString() + "')"; sqlComm.ExecuteNonQuery(); } sqlta.Commit(); MessageBox.Show("Acquire finished"); } catch (Exception ex) { MessageBox.Show("error:" + ex.Message.ToString(), "error", MessageBoxButtons.OK, MessageBoxIcon.Error); sqlta.Rollback(); return; } finally { sqlConn.Close(); } initDatatable(false, false); }
private void toolStripButtonInit_Click(object sender, EventArgs e) { int i; System.Data.SqlClient.SqlTransaction sqlta; sqlConn.Open(); sqlta = sqlConn.BeginTransaction(); sqlComm.Transaction = sqlta; try { for (i = 0; i < dataGridViewP.RowCount; i++) { //查重 sqlComm.CommandText = "SELECT ID, [Product Code], [Indentor Code], Year, [Num of Week] FROM actual WHERE ([Product ID] = N'" + dataGridViewP.Rows[i].Cells[0].Value.ToString() + "') AND ([Indentor ID] = N'" + dataGridViewP.Rows[i].Cells[3].Value.ToString() + "')"; sqldr = sqlComm.ExecuteReader(); if (!sqldr.HasRows) { sqlComm.CommandText = "INSERT INTO actual([Product ID], [Product Code], [Indentor ID], [Indentor Code], [Start Number], [End Number], [Acquire ID], Date, Year, [Num of Week], [TAC ID], [TAC Code], [Total Number]) VALUES (" + dataGridViewP.Rows[i].Cells[0].Value.ToString() + ", N'" + dataGridViewP.Rows[i].Cells[1].Value.ToString() + "', " + dataGridViewP.Rows[i].Cells[3].Value.ToString() + ", N'" + dataGridViewP.Rows[i].Cells[4].Value.ToString() + "', " + dataGridViewP.Rows[i].Cells[8].Value.ToString() + ", " + dataGridViewP.Rows[i].Cells[9].Value.ToString() + ", 0, CONVERT(DATETIME, '" + System.DateTime.Now.ToShortDateString() + " 00:00:00', 102), " + dateTimePickerP.Value.Year.ToString() + ", N'" + ClassIm.GetWeek(dateTimePickerP.Value).ToString() + "',0, N'', 0)"; } sqldr.Close(); sqlComm.ExecuteNonQuery(); } sqlta.Commit(); MessageBox.Show("Manufacture Initialize finished"); } catch (Exception ex) { MessageBox.Show("error:" + ex.Message.ToString(), "error", MessageBoxButtons.OK, MessageBoxIcon.Error); sqlta.Rollback(); return; } finally { sqlConn.Close(); } initDatatable(false, false); }
private void toolStripButtonEDIT_Click(object sender, EventArgs e) { int i; if (!countAmount()) { MessageBox.Show("please check data", "information", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return; } System.Data.SqlClient.SqlTransaction sqlta; sqlConn.Open(); sqlta = sqlConn.BeginTransaction(); sqlComm.Transaction = sqlta; try { for (i = 0; i < dataGridViewP.RowCount; i++) { if (int.Parse(dataGridViewP.Rows[i].Cells[11].Value.ToString()) != 0) { continue; } //查重 sqlComm.CommandText = "SELECT ID, [Product Code], [Indentor Code], Year, [Num of Week] FROM actual WHERE ([Product ID] = N'" + dataGridViewP.Rows[i].Cells[0].Value.ToString() + "') AND ([Indentor ID] = N'" + dataGridViewP.Rows[i].Cells[3].Value.ToString() + "') AND (Year = " + dateTimePickerP.Value.Year.ToString() + ") AND ([Num of Week] = N'" + ClassIm.GetWeek(dateTimePickerP.Value).ToString() + "')"; sqldr = sqlComm.ExecuteReader(); if (!sqldr.HasRows) { sqlComm.CommandText = "INSERT INTO actual([Product ID], [Product Code], [Indentor ID], [Indentor Code], [Start Number], [End Number], [Acquire ID], Date, Year, [Num of Week], [TAC ID], [TAC Code], [Total Number]) VALUES (" + dataGridViewP.Rows[i].Cells[0].Value.ToString() + ", N'" + dataGridViewP.Rows[i].Cells[1].Value.ToString() + "', " + dataGridViewP.Rows[i].Cells[3].Value.ToString() + ", N'" + dataGridViewP.Rows[i].Cells[4].Value.ToString() + "', " + dataGridViewP.Rows[i].Cells[8].Value.ToString() + ", " + dataGridViewP.Rows[i].Cells[9].Value.ToString() + ", 0, CONVERT(DATETIME, '" + System.DateTime.Now.ToShortDateString() + " 00:00:00', 102), " + dateTimePickerP.Value.Year.ToString() + ", N'" + ClassIm.GetWeek(dateTimePickerP.Value).ToString() + "', " + dataGridViewP.Rows[i].Cells[5].Value.ToString() + ", N'" + dataGridViewP.Rows[i].Cells[6].Value.ToString() + "', " + dataGridViewP.Rows[i].Cells[10].Value.ToString() + ")"; } else { sqlComm.CommandText = "UPDATE actual SET [End Number] = " + dataGridViewP.Rows[i].Cells[9].Value.ToString() + ", Date = CONVERT(DATETIME, '" + System.DateTime.Now.ToShortDateString() + " 00:00:00', 102), [TAC ID] = " + dataGridViewP.Rows[i].Cells[5].Value.ToString() + ", [TAC Code] = N'" + dataGridViewP.Rows[i].Cells[6].Value.ToString() + "', [Total Number] = " + dataGridViewP.Rows[i].Cells[10].Value.ToString() + " WHERE ([Product ID] = N'" + dataGridViewP.Rows[i].Cells[0].Value.ToString() + "') AND ([Indentor ID] = N'" + dataGridViewP.Rows[i].Cells[3].Value.ToString() + "') AND (Year = " + dateTimePickerP.Value.Year.ToString() + ") AND ([Num of Week] = N'" + ClassIm.GetWeek(dateTimePickerP.Value).ToString() + "')";; } sqldr.Close(); sqlComm.ExecuteNonQuery(); //backlog if (dataGridViewP.Rows[i].Cells[12].Value.ToString() != "0") { //查重 sqlComm.CommandText = "SELECT ID, [Product ID], [Product Code], [Indentor ID], [Indentor Code], [Start Number], [End Number], [Acquire ID], Date, Year, [Num of Week], Numbers, [Order Start Number], [Order End Number], backlog FROM orders WHERE ([Product ID] = N'" + dataGridViewP.Rows[i].Cells[0].Value.ToString() + "') AND ([Indentor ID] = N'" + dataGridViewP.Rows[i].Cells[3].Value.ToString() + "') AND (Year = " + dateTimePickerP.Value.Year.ToString() + ") AND ([Num of Week] = N'" + ClassIm.GetWeek(dateTimePickerP.Value).ToString() + "')"; sqldr = sqlComm.ExecuteReader(); if (!sqldr.HasRows) { sqlComm.CommandText = "INSERT INTO orders ([Product ID], [Product Code], [Indentor ID], [Indentor Code], [Start Number], [End Number], [Acquire ID], Date, Year, [Num of Week], Numbers, [Order Start Number], [Order End Number], backlog) VALUES (" + dataGridViewP.Rows[i].Cells[0].Value.ToString() + ", N'" + dataGridViewP.Rows[i].Cells[1].Value.ToString() + "', " + dataGridViewP.Rows[i].Cells[3].Value.ToString() + ", N'" + dataGridViewP.Rows[i].Cells[4].Value.ToString() + "', 0, 0, 0, CONVERT(DATETIME, '" + System.DateTime.Now.ToShortDateString() + " 00:00:00', 102), " + dateTimePickerP.Value.Year.ToString() + ", N'" + ClassIm.GetWeek(dateTimePickerP.Value).ToString() + "', 0, 0, 0, " + dataGridViewP.Rows[i].Cells[12].Value.ToString() + ")"; } else { sqlComm.CommandText = "UPDATE orders SET [backlog] = " + dataGridViewP.Rows[i].Cells[12].Value.ToString() + ", Date = CONVERT(DATETIME, '" + System.DateTime.Now.ToShortDateString() + " 00:00:00', 102) WHERE ([Product ID] = N'" + dataGridViewP.Rows[i].Cells[0].Value.ToString() + "') AND ([Indentor ID] = N'" + dataGridViewP.Rows[i].Cells[3].Value.ToString() + "') AND (Year = " + dateTimePickerP.Value.Year.ToString() + ") AND ([Num of Week] = N'" + ClassIm.GetWeek(dateTimePickerP.Value).ToString() + "')";; } sqldr.Close(); sqlComm.ExecuteNonQuery(); } } sqlta.Commit(); MessageBox.Show("Manufacture finished"); } catch (Exception ex) { MessageBox.Show("error:" + ex.Message.ToString(), "error", MessageBoxButtons.OK, MessageBoxIcon.Error); sqlta.Rollback(); return; } finally { sqlConn.Close(); } initDatatable(false, false); }
private bool countAmount() { bool bCheck = true; this.dataGridViewP.CellValidating -= dataGridViewP_CellValidating; ClassIm.ClearDataGridViewErrorText(dataGridViewP); for (int i = 0; i < dtManu.Rows.Count; i++) { dtManu.Rows[i][11] = 0; //TAC 检验 if (dtManu.Rows[i][6].ToString() == "") { dtManu.Rows[i][11] = 1; } var q2 = from dt2 in dSet.Tables["TAC"].AsEnumerable() //查询TAC区间 where (dt2.Field <string>("Product Code") == dtManu.Rows[i][1].ToString()) && (dt2.Field <string>("Indentor Code") == dtManu.Rows[i][4].ToString()) && (dt2.Field <string>("TAC Code") == dtManu.Rows[i][6].ToString()) //条件 select dt2; if (q2.Count() <= 0) { dtManu.Rows[i][11] = 1; dtManu.Rows[i][5] = 0; dtManu.Rows[i][6] = ""; dtManu.Rows[i][7] = 0; } else { foreach (var item in q2)//显示查询结果 { dtManu.Rows[i][5] = item.Field <int>("ID"); dtManu.Rows[i][6] = item.Field <string>("TAC Code"); dtManu.Rows[i][7] = item.Field <int>("Init Number"); break; } } if (int.Parse(dtManu.Rows[i][11].ToString()) != 0) { continue; } //结束号检验 if (int.Parse(dtManu.Rows[i][9].ToString()) > iRange || int.Parse(dtManu.Rows[i][9].ToString()) <= 0 || int.Parse(dtManu.Rows[i][9].ToString()) <= int.Parse(dtManu.Rows[i][8].ToString())) { dtManu.Rows[i][11] = 2; } if (dtManu.Rows[i][12].ToString() == "") { dtManu.Rows[i][11] = 2; } if (int.Parse(dtManu.Rows[i][12].ToString()) < 0) { dtManu.Rows[i][12] = 2; } //取值大于跳转 if (int.Parse(dtManu.Rows[i][12].ToString()) > iRange) { dtManu.Rows[i][12] = 2; } if (int.Parse(dtManu.Rows[i][11].ToString()) != 0) { continue; } dtManu.Rows[i][10] = int.Parse(dtManu.Rows[i][9].ToString()) + int.Parse(dtManu.Rows[i][7].ToString()); } for (int i = 0; i < dataGridViewP.RowCount; i++) { switch (dataGridViewP.Rows[i].Cells[11].Value.ToString()) { case "1": dataGridViewP.Rows[i].Cells[6].ErrorText = "TAC Code Error"; break; //case "2": // dataGridViewP.Rows[i].Cells[10].ErrorText = "NO TAC Define"; // break; case "2": dataGridViewP.Rows[i].Cells[9].ErrorText = "number error"; break; default: break; } } this.dataGridViewP.CellValidating += dataGridViewP_CellValidating; //dataGridViewP.EndEdit(); return(bCheck); }
private void dataGridViewP_CellValidating(object sender, DataGridViewCellValidatingEventArgs e) { int intOut = 0; if (dataGridViewP.Rows[e.RowIndex].IsNewRow) { return; } ClassIm.ClearDataGridViewErrorText(dataGridViewP); switch (e.ColumnIndex) { case 6: if (e.FormattedValue.ToString() == "") { break; } //取得TAC var q2 = from dt2 in dSet.Tables["TAC"].AsEnumerable() //查询TAC区间 where (dt2.Field <string>("Product Code") == dataGridViewP.Rows[e.RowIndex].Cells[1].Value.ToString()) && (dt2.Field <string>("Indentor Code") == dataGridViewP.Rows[e.RowIndex].Cells[4].Value.ToString()) && (dt2.Field <string>("TAC Code") == e.FormattedValue.ToString()) //条件 select dt2; if (q2.Count() <= 0) { dataGridViewP.Rows[e.RowIndex].Cells[6].ErrorText = "TAC Code error"; e.Cancel = true; } break; case 9: if (e.FormattedValue.ToString() == "") { break; } if (int.TryParse(e.FormattedValue.ToString(), out intOut)) { if (intOut < 0) { dataGridViewP.Rows[e.RowIndex].Cells[9].ErrorText = "data error"; e.Cancel = true; } if (intOut > iRange) { dataGridViewP.Rows[e.RowIndex].Cells[9].ErrorText = "data error"; e.Cancel = true; } } else { dataGridViewP.Rows[e.RowIndex].Cells[9].ErrorText = "data error"; e.Cancel = true; } break; case 12: if (e.FormattedValue.ToString() == "") { break; } if (int.TryParse(e.FormattedValue.ToString(), out intOut)) { if (intOut < 0) { dataGridViewP.Rows[e.RowIndex].Cells[12].ErrorText = "data error"; e.Cancel = true; } if (intOut > iRange) { dataGridViewP.Rows[e.RowIndex].Cells[12].ErrorText = "data error"; e.Cancel = true; } } else { dataGridViewP.Rows[e.RowIndex].Cells[12].ErrorText = "data error"; e.Cancel = true; } break; default: break; } //dataGridViewP.EndEdit(); }