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