private void btnDownload_Click(object sender, EventArgs e)
{
if (!this.rbtnConsumption.Checked && !this.rbtnOriginalGoods.Checked)
{
MessageBox.Show("Please select consumption or original goods to download first.", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
if (this.dgvConsumption.RowCount == 0 && this.rbtnConsumption.Checked)
{
MessageBox.Show("The consumption data not exist.", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Stop);
this.btnDownload.Focus();
return;
}
if (this.dgvOriginalGoods.RowCount == 0 && this.rbtnOriginalGoods.Checked)
{
MessageBox.Show("The original goods data not exist.", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Stop);
this.btnDownload.Focus();
return;
}
DialogResult dlgR = MessageBox.Show("Please select a document format:\n[Yes] Generate Excel as new version;\n[No] Generate Excel as old version;\n[Cancel] Reject to handle.", "Prompt", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Information);
if (dlgR == DialogResult.Yes)
{
#region //new version
if (this.rbtnConsumption.Checked && this.dgvConsumption.RowCount > 0)
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks workbooks = excel.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(true);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
string[] strFiledName = { "成品备件号" };
SqlLib lib = new SqlLib();
DataTable dt = lib.SelectDistinct(middleTable, strFiledName);
lib.Dispose(0);
for (int i = 0; i < dt.Rows.Count; i++)
{
string strEHB = dt.Rows[i][0].ToString().Trim();
DataRow[] datarow = middleTable.Select("[成品备件号] = '" + strEHB + "' AND ([重复备件号] IS NULL OR [重复备件号] = '')");
if (datarow.Length > 0)
{
worksheet.Name = strEHB.Replace("/", "X");
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[datarow.Length + 1, 10]).NumberFormatLocal = "@";
for (int j = 0; j < datarow.Length; j++)
{
worksheet.Cells[j + 2, 1] = datarow[j][9].ToString().Trim();
worksheet.Cells[j + 2, 2] = datarow[j][0].ToString().Trim();
worksheet.Cells[j + 2, 3] = datarow[j][3].ToString().Trim();
worksheet.Cells[j + 2, 4] = datarow[j][4].ToString().Trim();
worksheet.Cells[j + 2, 5] = datarow[j][5].ToString().Trim();
worksheet.Cells[j + 2, 6] = "0";
worksheet.Cells[j + 2, 7] = datarow[j][2].ToString().Trim();
worksheet.Cells[j + 2, 8] = datarow[j][7].ToString().Trim();
worksheet.Cells[j + 2, 9] = string.Empty;
}
worksheet.Cells[1, 1] = "手册号";
worksheet.Cells[1, 2] = "成品货物备件号";
worksheet.Cells[1, 3] = "净耗数量";
worksheet.Cells[1, 4] = "数量损耗率";
worksheet.Cells[1, 5] = "重量损耗率";
worksheet.Cells[1, 6] = "总耗重量";
worksheet.Cells[1, 7] = "原料货物备件号";
worksheet.Cells[1, 8] = "废料货物备件号";
worksheet.Cells[1, 9] = "备注";
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 9]).Font.Bold = true;
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[datarow.Length + 1, 9]).Font.Name = "Verdana";
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[datarow.Length + 1, 9]).Font.Size = 9;
worksheet.Cells.EntireColumn.AutoFit();
if (i < dt.Rows.Count - 1)
{
object missing = System.Reflection.Missing.Value;
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Add(missing, missing, missing, missing);
}
}
}
dt.Clear();
dt.Dispose();
excel.Visible = true;
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
excel = null;
}
if (this.rbtnOriginalGoods.Checked && this.dgvOriginalGoods.RowCount > 0)
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks workbooks = excel.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(true);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
worksheet.Name = "OriginalGoods";
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[this.dgvOriginalGoods.Rows.Count + 1, 6]).NumberFormatLocal = "@";
int iActualRow = 0;
for (int k = 0; k < this.dgvOriginalGoods.Rows.Count; k++)
{
if (String.IsNullOrEmpty(this.dgvOriginalGoods[14, k].Value.ToString().Trim()))
{
iActualRow++;
worksheet.Cells[iActualRow + 1, 1] = this.dgvOriginalGoods[15, k].Value.ToString().Trim();
worksheet.Cells[iActualRow + 1, 2] = this.dgvOriginalGoods[2, k].Value.ToString().Trim();
worksheet.Cells[iActualRow + 1, 3] = this.dgvOriginalGoods[16, k].Value.ToString().Trim();
worksheet.Cells[iActualRow + 1, 4] = "//";
worksheet.Cells[iActualRow + 1, 5] = this.dgvOriginalGoods[17, k].Value.ToString().Trim();
worksheet.Cells[iActualRow + 1, 6] = "1";
}
}
worksheet.Cells[1, 1] = "货主十位数编码";
worksheet.Cells[1, 2] = "原始货物备件号";
worksheet.Cells[1, 3] = "合并货物备件号";
worksheet.Cells[1, 4] = "规格型号";
worksheet.Cells[1, 5] = "仓库号";
worksheet.Cells[1, 6] = "单位净重";
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 6]).Font.Bold = true;
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[this.dgvOriginalGoods.Rows.Count + 1, 6]).Font.Name = "Verdana";
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[this.dgvOriginalGoods.Rows.Count + 1, 6]).Font.Size = 9;
worksheet.Cells.EntireColumn.AutoFit();
excel.Visible = true;
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
excel = null;
}
#endregion
}
else if (dlgR == DialogResult.No)
{
#region //old version
if (this.rbtnConsumption.Checked && this.dgvConsumption.RowCount > 0)
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Application.Workbooks.Add(true);
excel.get_Range(excel.Cells[1, 1], excel.Cells[this.dgvConsumption.RowCount + 1, this.dgvConsumption.ColumnCount - 2]).NumberFormatLocal = "@";
int iActualRow = 0, iLineNo = 0;
string strBatchFg = String.Empty;
for (int i = 0; i < this.dgvConsumption.RowCount; i++)
{
string strBOM1 = this.dgvConsumption[0, i].Value.ToString().Trim();
if (String.Compare(strBOM1, strBatchFg) == 0)
{
iLineNo++;
}
else
{
iLineNo = 1; strBatchFg = strBOM1;
}
string strBOM2 = this.dgvConsumption[8, i].Value.ToString().Trim(); //Mapping 'BOM In Customs'
decimal dConsump = Convert.ToDecimal(this.dgvConsumption[3, i].Value.ToString().Trim()); //Mapping 'Consumption'
if (String.IsNullOrEmpty(strBOM2) && dConsump > 0.0M)
{
iActualRow++;
for (int j = 0; j < this.dgvConsumption.ColumnCount - 2; j++)
{
if (j == 1)
{
excel.Cells[iActualRow + 1, j + 1] = iLineNo;
}
else
{
excel.Cells[iActualRow + 1, j + 1] = this.dgvConsumption[j, i].Value.ToString().Trim();
}
}
}
}
for (int k = 0; k < this.dgvConsumption.ColumnCount - 2; k++)
{
excel.Cells[1, k + 1] = this.dgvConsumption.Columns[k].HeaderText.ToString().Trim();
}
excel.get_Range(excel.Cells[1, 1], excel.Cells[1, this.dgvConsumption.ColumnCount - 2]).Font.Bold = true;
excel.get_Range(excel.Cells[1, 1], excel.Cells[1, this.dgvConsumption.ColumnCount - 2]).AutoFilter(1, Type.Missing, Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true);
excel.get_Range(excel.Cells[1, 1], excel.Cells[iActualRow + 1, this.dgvConsumption.ColumnCount - 2]).Font.Name = "Verdana";
excel.get_Range(excel.Cells[1, 1], excel.Cells[iActualRow + 1, this.dgvConsumption.ColumnCount - 2]).Font.Size = 9;
excel.Cells.EntireColumn.AutoFit();
excel.Visible = true;
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
excel = null;
}
if (this.rbtnOriginalGoods.Checked && this.dgvOriginalGoods.RowCount > 0)
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Application.Workbooks.Add(true);
excel.get_Range(excel.Cells[1, 1], excel.Cells[this.dgvOriginalGoods.RowCount + 1, this.dgvOriginalGoods.ColumnCount - 5]).NumberFormatLocal = "@"; //Set the excel cells format as text
int iActualRow = 0;
for (int m = 0; m < this.dgvOriginalGoods.RowCount; m++)
{
if (String.IsNullOrEmpty(this.dgvOriginalGoods[14, m].Value.ToString().Trim())) //The same mapping 'BOM In Customs'
{
iActualRow++;
for (int n = 1; n < this.dgvOriginalGoods.ColumnCount - 4; n++)
{
excel.Cells[iActualRow + 1, n + 1] = this.dgvOriginalGoods[n + 1, m].Value.ToString().Trim();
}
}
}
for (int y = 1; y <= iActualRow; y++)
{
excel.Cells[y + 1, 1] = y;
} //Regenerate the first column value, since it is the serial number
for (int x = 1; x < this.dgvOriginalGoods.ColumnCount - 4; x++)
{
excel.Cells[1, x] = this.dgvOriginalGoods.Columns[x].HeaderText.ToString().Trim();
}
excel.get_Range(excel.Cells[1, 1], excel.Cells[1, this.dgvOriginalGoods.ColumnCount - 5]).Font.Bold = true;
excel.get_Range(excel.Cells[1, 1], excel.Cells[1, this.dgvOriginalGoods.ColumnCount - 5]).AutoFilter(1, Type.Missing, Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true);
excel.get_Range(excel.Cells[1, 1], excel.Cells[iActualRow + 1, this.dgvOriginalGoods.ColumnCount - 5]).Font.Name = "Verdana";
excel.get_Range(excel.Cells[1, 1], excel.Cells[iActualRow + 1, this.dgvOriginalGoods.ColumnCount - 5]).Font.Size = 9;
excel.Cells.EntireColumn.AutoFit();
excel.Visible = true;
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
excel = null;
}
#endregion
}
}
private void btnApprove_Click(object sender, EventArgs e)
{
if (this.dgvGongDan.RowCount == 0)
{
MessageBox.Show("There is no data.", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Warning); return;
}
if (this.dgvGongDanList.Columns[0].HeaderText == "Select")
{
MessageBox.Show("Please select GongDan No.", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Stop); return;
}
this.GetGongDanData();
SqlLib lib = new SqlLib();
string[] strFieldName = { "工单号" }; //dtGongDanList.Columns[0].ColumnName.Trim()
DataTable dtGD = lib.SelectDistinct(dtGongDanList, strFieldName);
lib.Dispose(0);
SqlConnection apprGdConn = new SqlConnection(SqlLib.StrSqlConnection);
if (apprGdConn.State == ConnectionState.Closed)
{
apprGdConn.Open();
}
SqlCommand apprGdComm = new SqlCommand();
apprGdComm.Connection = apprGdConn;
string strGongDanRecord = null;
apprGdComm.CommandText = "SELECT * FROM B_MultiUser";
string strUserName = Convert.ToString(apprGdComm.ExecuteScalar());
if (!String.IsNullOrEmpty(strUserName))
{
if (String.Compare(strUserName.Trim().ToUpper(), funcLib.getCurrentUserName().Trim().ToUpper()) != 0)
{
MessageBox.Show(strUserName + " is handling RM Balance/Drools Balance data. Please wait for him/her to finish the process.", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Stop);
apprGdComm.Dispose();
apprGdConn.Dispose();
return;
}
}
else
{
apprGdComm.Parameters.Add("@UserName", SqlDbType.NVarChar).Value = funcLib.getCurrentUserName().ToUpper();
apprGdComm.CommandText = "INSERT INTO B_MultiUser([UserName]) VALUES(@UserName)";
apprGdComm.ExecuteNonQuery();
apprGdComm.Parameters.Clear();
}
DateTime dtApproved = Convert.ToDateTime(System.DateTime.Now.ToString("M/d/yyyy HH:mm"));
if (!String.IsNullOrEmpty(this.dtpApprovedDate.Text.Trim()))
{
dtApproved = Convert.ToDateTime(this.dtpApprovedDate.Value.ToString("M/d/yyyy HH:mm"));
}
for (int x = 0; x < dtGD.Rows.Count; x++)
{
string strGongDan = dtGD.Rows[x][0].ToString().Trim();
apprGdComm.Parameters.Add("@GongDanNo", SqlDbType.NVarChar).Value = strGongDan;
apprGdComm.CommandText = "SELECT DISTINCT [Actual Start Date], [Actual End Date], [Batch No], [BOM In Customs], [GongDan No], [FG No], [FG Description], " +
"[IE Type], [Order No], [GongDan Qty], [Order Price], [Order Currency], CAST([Total RM Qty] AS decimal(18, 6)) AS [Total RM Qty], " +
"[Total RM Cost(USD)], CAST([Drools Rate] AS decimal(18,6)) AS [DroolsRate], [CHN Name], [Destination], '" + funcLib.getCurrentUserName() +
"' AS [Creater], [Created Date], '" + dtApproved + "' AS [Approval Date] FROM M_DailyGongDan WHERE [GongDan No] = @GongDanNo";
SqlDataAdapter apprGdAdap = new SqlDataAdapter();
apprGdAdap.SelectCommand = apprGdComm;
DataTable dtApprGongDanM = new DataTable();
apprGdAdap.Fill(dtApprGongDanM);
string strFG = dtApprGongDanM.Rows[0]["FG No"].ToString().Trim();
string strGongDanQty = dtApprGongDanM.Rows[0]["GongDan Qty"].ToString().Trim();
apprGdComm.CommandText = "SELECT [Batch Path], [GongDan No], [Line No], [Item No], [Item Description], [Lot No], [Inventory Type], [RM Category], " +
"[RM EHB], [BGD No], CAST([RM Used Qty] AS decimal(18, 6)) AS [RM Used Qty], [RM Currency], [RM Price], " +
"CAST([Consumption] AS decimal(18, 6)) AS [Consumption], CAST([Drools Quota] AS decimal(18, 8)) AS [Drools Quota], " +
"[Drools EHB] FROM M_DailyGongDan WHERE [GongDan No] = @GongDanNo";
apprGdAdap.SelectCommand = apprGdComm;
DataTable dtApprGongDanD = new DataTable();
apprGdAdap.Fill(dtApprGongDanD);
apprGdComm.CommandText = "SELECT [GongDan No] AS [工单号], N'加工' AS [生产类型], [FG No] + '/' + [Batch No] AS [成品备件号], [GongDan Qty] AS [工单数量], " +
"ROW_NUMBER() OVER (ORDER BY [RM EHB], [BGD No]) AS [项号], [RM EHB] AS [物料备件号], [RM Used Qty] AS [物料耗用数量], " +
"[CN] AS [原产国], [BGD No] AS [批次号], [BOM In Customs], '" + dtApproved + "' AS [Created Date] FROM (" +
"SELECT M.[GongDan No], SUBSTRING(M.[FG Description],0,CHARINDEX('-',M.[FG Description],CHARINDEX('-',M.[FG Description],0)+1)) AS [FG No], " +
"M.[Batch No], M.[GongDan Qty], M.[RM EHB], CAST(SUM(M.[RM Used Qty]) AS decimal(18, 6)) AS [RM Used Qty], N.[CN], " +
"M.[BGD No], M.[BOM In Customs] FROM M_DailyGongDan AS M LEFT OUTER JOIN (SELECT C.[Item No], C.[Lot No], B.[CN] FROM C_RMReceiving AS C " +
"INNER JOIN B_Country AS B ON C.[Country of Origin] = B.[EN]) AS N ON M.[Item No] = N.[Item No] AND M.[Lot No] = N.[Lot No] " +
"WHERE [RM Used Qty] > 0.0 GROUP BY M.[GongDan No], M.[FG Description], M.[Batch No], M.[GongDan Qty], M.[RM EHB], N.[CN], " +
"M.[BGD No], M.[BOM In Customs] HAVING [GongDan No] = @GongDanNo) tbgdd";
apprGdAdap.SelectCommand = apprGdComm;
DataTable dtApprGongDanDoc = new DataTable();
apprGdAdap.Fill(dtApprGongDanDoc);
/*------ check and update 'RM-D' IEType to 'RMB-1418' or 'RMB-D' ------*/
//apprGdComm.CommandText = "SELECT [GongDan No], [IsAllocated] FROM (SELECT M.[GongDan No], B.[IsAllocated], MAX(M.[Line No]) Line, " +
// "COUNT(M.[GongDan No]) CountNo FROM M_DailyGongDan AS M LEFT JOIN (SELECT DISTINCT [Grade], [IsAllocated] FROM B_HsCode) AS B " +
// "ON SUBSTRING(M.[FG Description], 0, CHARINDEX('-', M.[FG Description], 0)) = B.[Grade] " +
// "WHERE M.[IE Type] = 'RM-D' AND M.[RM Category] = 'USD' AND M.[Consumption] > 0.0 GROUP BY M.[GongDan No], " +
// "B.[IsAllocated] HAVING [GongDan No] = @GongDanNo) AS dtie WHERE Line = CountNo";
//apprGdAdap.SelectCommand = apprGdComm;
//DataTable dtChangeIE = new DataTable();
//apprGdAdap.Fill(dtChangeIE);
//apprGdAdap.Dispose();
//foreach(DataRow drChangeIE in dtChangeIE.Rows)
//{
// string strGD = drChangeIE[0].ToString().Trim();
// string strAllocated = drChangeIE[1].ToString().Trim();
// DataRow[] drow = dtApprGongDanM.Select("[GongDan No] = '" + strGD + "'");
// if (drow.Length > 0)
// {
// if (String.Compare(strAllocated, "True") == 0)
// {
// //foreach (DataRow dr in drow) { dr["IE Type"] = "RMB-D"; }
// //strGongDanRecord += strGD + " : RMB-D\n";
// }
// else
// {
// foreach (DataRow dr in drow) { dr["IE Type"] = "RMB-1418"; }
// strGongDanRecord += strGD + " : RMB-1418\n";
// }
// }
//}
//dtChangeIE.Dispose();
dtApprGongDanM.AcceptChanges();
#region //Update C_BOM table's column: GongDan Used Qty
apprGdComm.Parameters.Clear();
apprGdComm.CommandType = CommandType.StoredProcedure;
apprGdComm.CommandText = @"usp_UpdateGongDanUsedQty";
apprGdComm.Parameters.AddWithValue("@GongDanNo", strGongDan);
apprGdComm.Parameters.AddWithValue("@BatchNo", strGongDan.Split('-')[0].Trim());
apprGdComm.Parameters.AddWithValue("@Judge", "ADD");
apprGdComm.ExecuteNonQuery();
apprGdComm.Parameters.Clear();
#endregion
#region //Handle C_GongDan, C_GongDanDetail, E_GongDan table's data
apprGdComm.CommandText = @"usp_InsertGongDan";
apprGdComm.Parameters.AddWithValue("@tvp_GongDanMaster", dtApprGongDanM);
apprGdComm.ExecuteNonQuery();
apprGdComm.Parameters.Clear();
dtApprGongDanM.Dispose();
apprGdComm.CommandText = @"usp_InsertGongDanDetail";
apprGdComm.Parameters.AddWithValue("@tvp_GongDanDetail", dtApprGongDanD);
apprGdComm.ExecuteNonQuery();
apprGdComm.Parameters.Clear();
dtApprGongDanD.Dispose();
apprGdComm.CommandText = @"usp_InsertGongDanDoc";
apprGdComm.Parameters.AddWithValue("@tvp_GongDanDoc", dtApprGongDanDoc);
apprGdComm.ExecuteNonQuery();
apprGdComm.Parameters.Clear();
dtApprGongDanDoc.Dispose();
#endregion
#region //Update C_RMBalance table's column: Available RM Balance, GongDan Pending
apprGdComm.CommandText = "usp_UpdateRMBalanceByGongDan";
apprGdComm.Parameters.AddWithValue("@GongDanNo", strGongDan);
apprGdComm.Parameters.AddWithValue("@Action", "ADD");
apprGdComm.ExecuteNonQuery();
apprGdComm.Parameters.Clear();
#endregion
apprGdComm.CommandType = CommandType.Text;
apprGdComm.Parameters.Add("@GongDanNo", SqlDbType.NVarChar).Value = strGongDan;
apprGdComm.CommandText = "DELETE FROM M_DailyGongDan WHERE [GongDan No] = @GongDanNo";
apprGdComm.ExecuteNonQuery();
apprGdComm.Parameters.Clear();
}
dtGD.Dispose();
apprGdComm.CommandText = "DELETE FROM B_MultiUser";
apprGdComm.ExecuteNonQuery();
apprGdComm.Dispose();
if (apprGdConn.State == ConnectionState.Open)
{
apprGdConn.Close(); apprGdConn.Dispose();
}
string strComment = String.IsNullOrEmpty(strGongDanRecord) ? string.Empty : "\nBelow GongDan's (IE Type) convert to RMB-D or RMB-1418 from RM-D.\nPlease inform Logistics team and XFZ !!!" + strGongDanRecord;
if (MessageBox.Show("Successfully approve." + strComment, "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Information) == DialogResult.OK)
{
dtGongDanList.Clear();
this.btnGongDan_Click(sender, e);
}
}
private void btnGongDan_Click(object sender, EventArgs e)
{
SqlConnection gdConn = new SqlConnection(SqlLib.StrSqlConnection);
if (gdConn.State == ConnectionState.Closed)
{
gdConn.Open();
}
string strSQL = "SELECT [GongDan No] AS [工单号], N'加工' AS [生产类型], [FG No] + '/' + [Batch No] AS [成品备件号], [GongDan Qty] AS [工单数量], " +
"0 AS [项号], [RM EHB] AS [物料备件号], [RM Used Qty] AS [物料耗用数量], [CN] AS [原产国], [BGD No] AS [批次号], [BOM In Customs] FROM ( " +
"SELECT M.[GongDan No], SUBSTRING(M.[FG Description],0,CHARINDEX('-',M.[FG Description],CHARINDEX('-',M.[FG Description],0)+1)) AS [FG No], " +
"M.[Batch No], M.[GongDan Qty], M.[RM EHB], N.[CN], SUM(CAST(M.[RM Used Qty] AS decimal(18,6))) AS [RM Used Qty], M.[BGD No], " +
"M.[BOM In Customs] FROM M_DailyGongDan AS M LEFT OUTER JOIN (SELECT C.[Item No], C.[Lot No], B.[CN] FROM C_RMReceiving AS C INNER JOIN B_Country AS B ON C.[Country of Origin] = B.[EN]) AS N ON M.[Item No] = N.[Item No] AND M.[Lot No] = N.[Lot No] " +
"WHERE M.[RM Used Qty] > 0.0 GROUP BY M.[GongDan No], M.[FG Description], M.[Batch No], M.[GongDan Qty], M.[RM EHB], N.[CN], " +
"M.[BGD No], M.[BOM In Customs]) AS tbgd";
SqlDataAdapter gdAdapter = new SqlDataAdapter(strSQL, gdConn);
dtGongDanDoc.Clear();
gdAdapter.Fill(dtGongDanDoc);
gdAdapter.Dispose();
SqlLib sqlLib = new SqlLib();
string[] strFieldName = { "工单号" };
DataTable dtGongDanNo = sqlLib.SelectDistinct(dtGongDanDoc, strFieldName);
sqlLib.Dispose(0);
string strGongDan = "";
int iLine = 0;
foreach (DataRow drow in dtGongDanDoc.Rows)
{
string strGD = drow[0].ToString().Trim();
if (String.Compare(strGongDan, strGD) == 0)
{
iLine++;
drow["项号"] = iLine;
}
else
{
strGongDan = strGD;
iLine = 1;
drow["项号"] = iLine;
}
}
dtGongDanDoc.AcceptChanges();
if (dtGongDanDoc.Rows.Count > 0)
{
this.dgvGongDan.DataSource = dtGongDanDoc;
this.dgvGongDanList.DataSource = dtGongDanNo;
}
else
{
this.dgvGongDan.DataSource = DBNull.Value;
this.dgvGongDanList.DataSource = DBNull.Value;
}
this.dgvGongDanList.Columns[0].HeaderText = "Select";
if (gdConn.State == ConnectionState.Open)
{
gdConn.Close(); gdConn.Dispose();
}
}
private void btnDownload_Click(object sender, EventArgs e)
{
if (!this.rbtnConsumption.Checked && !this.rbtnOriginalGoods.Checked)
{
MessageBox.Show("Please select consumption or original goods to download first.", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Warning); return;
}
if (this.dgvConsmpt.RowCount == 0 && this.rbtnConsumption.Checked)
{
MessageBox.Show("There is no data for consumption.", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return;
}
if (this.dgvOrgGoods.RowCount == 0 && this.rbtnOriginalGoods.Checked)
{
MessageBox.Show("There is no data for Original Goods.", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return;
}
DialogResult dlgR = MessageBox.Show("Please select a document format:\n[Yes] Generate Excel as old version to upload to Customs system;\n[No] Generate Excel as new version to keep record and print out paper for Customs declaration;\n[Cancel] Reject to handle.", "Prompt", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Information);
if (dlgR == DialogResult.Yes)
{
#region //Generate Excel as old version to upload to Customs system
if (this.rbtnConsumption.Checked && this.dgvConsmpt.RowCount > 0)
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks workbooks = excel.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(true);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
string[] strFiledName = { "成品备件号" };
SqlLib lib = new SqlLib();
DataTable dt = lib.SelectDistinct(dtBomDoc, strFiledName);
lib.Dispose(0);
for (int i = 0; i < dt.Rows.Count; i++)
{
string strEHB = dt.Rows[i][0].ToString().Trim();
//if this batch no has no single bonded material, it has to be stopped to submit data for customs declaration. June.29.2017
if (strBatchListWithoutUSDcomponent.IndexOf(strEHB.Substring(strEHB.IndexOf("/") + 1)) > 0)
{
strEHB = "";
}
;
DataRow[] datarow = dtBomDoc.Select("[成品备件号] = '" + strEHB + "' AND ([BOM In Customs] IS NULL OR [BOM In Customs] = '')");
if (datarow.Length > 0)
{
worksheet.Name = strEHB.Replace("/", "|");
//worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[datarow.Length + 1, 10]).NumberFormatLocal = "@";
for (int j = 0; j < datarow.Length; j++)
{
worksheet.Cells[j + 2, 1] = datarow[j][9].ToString().Trim();
worksheet.Cells[j + 2, 2] = datarow[j][0].ToString().Trim();
worksheet.Cells[j + 2, 3] = datarow[j][3].ToString().Trim();
worksheet.Cells[j + 2, 4] = datarow[j][4].ToString().Trim();
worksheet.Cells[j + 2, 5] = datarow[j][5].ToString().Trim();
worksheet.Cells[j + 2, 6] = "0";
worksheet.Cells[j + 2, 7] = datarow[j][2].ToString().Trim();
worksheet.Cells[j + 2, 8] = datarow[j][7].ToString().Trim();
worksheet.Cells[j + 2, 9] = string.Empty;
}
worksheet.Cells[1, 1] = "手册号";
worksheet.Cells[1, 2] = "成品货物备件号";
worksheet.Cells[1, 3] = "净耗数量";
worksheet.Cells[1, 4] = "数量损耗率";
worksheet.Cells[1, 5] = "重量损耗率";
worksheet.Cells[1, 6] = "总耗重量";
worksheet.Cells[1, 7] = "原料货物备件号";
worksheet.Cells[1, 8] = "废料货物备件号";
worksheet.Cells[1, 9] = "备注";
//worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 9]).Font.Bold = true;
//worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[datarow.Length + 1, 9]).Font.Name = "Verdana";
//worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[datarow.Length + 1, 9]).Font.Size = 9;
worksheet.Cells.EntireColumn.AutoFit();
excel.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignJustify;
if (i < dt.Rows.Count - 1)
{
object missing = System.Reflection.Missing.Value;
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Add(missing, missing, missing, missing);
}
}
}
dt.Clear();
dt.Dispose();
excel.Visible = true;
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
excel = null;
}
if (this.rbtnOriginalGoods.Checked && this.dgvOrgGoods.RowCount > 0)
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks workbooks = excel.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(true);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
worksheet.Name = "OriginalGoods";
//worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[this.dgvOrgGoods.Rows.Count + 1, 6]).NumberFormatLocal = "@";
int iActualRow = 0;
for (int k = 0; k < this.dgvOrgGoods.Rows.Count; k++)
{
//if this batch no has no single bonded material, it has to be stopped to submit data for customs declaration. June.29.2017
if (strBatchListWithoutUSDcomponent.IndexOf(this.dgvOrgGoods[2, k].Value.ToString().Substring(this.dgvOrgGoods[2, k].Value.ToString().IndexOf("/") + 1)) >= 0)
{
continue;
}
; // Just skip for this iteration
if (String.IsNullOrEmpty(this.dgvOrgGoods[14, k].Value.ToString().Trim()))
{
iActualRow++;
worksheet.Cells[iActualRow + 1, 1] = this.dgvOrgGoods[15, k].Value.ToString().Trim();
worksheet.Cells[iActualRow + 1, 2] = this.dgvOrgGoods[2, k].Value.ToString().Trim();
worksheet.Cells[iActualRow + 1, 3] = this.dgvOrgGoods[16, k].Value.ToString().Trim();
worksheet.Cells[iActualRow + 1, 4] = "//";
worksheet.Cells[iActualRow + 1, 5] = this.dgvOrgGoods[17, k].Value.ToString().Trim();
worksheet.Cells[iActualRow + 1, 6] = "1";
}
}
worksheet.Cells[1, 1] = "货主十位数编码";
worksheet.Cells[1, 2] = "原始货物备件号";
worksheet.Cells[1, 3] = "合并货物备件号";
worksheet.Cells[1, 4] = "规格型号";
worksheet.Cells[1, 5] = "仓库号";
worksheet.Cells[1, 6] = "单位净重";
//worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 6]).Font.Bold = true;
//worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[this.dgvOrgGoods.Rows.Count + 1, 6]).Font.Name = "Verdana";
//worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[this.dgvOrgGoods.Rows.Count + 1, 6]).Font.Size = 9;
worksheet.Cells.EntireColumn.AutoFit();
excel.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignJustify;
excel.Visible = true;
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
excel = null;
}
#endregion
}
else if (dlgR == DialogResult.No)
{
#region //Generate new version of Spreadsheet files for filing purpose
if (this.rbtnConsumption.Checked && this.dgvConsmpt.RowCount > 0)
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Application.Workbooks.Add(true);
// excel.get_Range(excel.Cells[1, 1], excel.Cells[this.dgvConsmpt.RowCount + 1, this.dgvConsmpt.ColumnCount - 2]).NumberFormatLocal = "@";
int iActualRow = 0, iLineNo = 0;
string strBatchFg = String.Empty;
for (int i = 0; i < this.dgvConsmpt.RowCount; i++)
{
//if this batch no has no single bonded material, it does not need to be saved for filing purpose. July.20.2017
// Get the value of the third column in the grid, the value looks like 1000-GY7G350T/0006337069
if (strBatchListWithoutUSDcomponent.IndexOf(this.dgvConsmpt[3, i].Value.ToString().Trim().Substring(this.dgvConsmpt[3, i].Value.ToString().Trim().IndexOf("/") + 1)) > 0)
{
continue;
}
; // Just skip for this iteration;
string strBOM1 = this.dgvConsmpt[0, i].Value.ToString().Trim();
if (String.Compare(strBOM1, strBatchFg) == 0)
{
iLineNo++;
}
else
{
iLineNo = 1; strBatchFg = strBOM1;
}
string strBOM2 = this.dgvConsmpt[8, i].Value.ToString().Trim(); //Mapping 'BOM In Customs'
decimal dConsump = Convert.ToDecimal(this.dgvConsmpt[3, i].Value.ToString().Trim()); //Mapping 'Consumption'
if (String.IsNullOrEmpty(strBOM2) && dConsump > 0.0M)
{
iActualRow++;
for (int j = 0; j < this.dgvConsmpt.ColumnCount - 2; j++)
{
if (j == 1)
{
excel.Cells[iActualRow + 1, j + 1] = iLineNo;
}
else
{
excel.Cells[iActualRow + 1, j + 1] = this.dgvConsmpt[j, i].Value.ToString().Trim();
}
}
}
}
for (int k = 0; k < this.dgvConsmpt.ColumnCount - 2; k++)
{
excel.Cells[1, k + 1] = this.dgvConsmpt.Columns[k].HeaderText.ToString().Trim();
}
//excel.get_Range(excel.Cells[1, 1], excel.Cells[1, this.dgvConsmpt.ColumnCount - 2]).Font.Bold = true;
//excel.get_Range(excel.Cells[1, 1], excel.Cells[1, this.dgvConsmpt.ColumnCount - 2]).AutoFilter(1, Type.Missing, Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true);
//excel.get_Range(excel.Cells[1, 1], excel.Cells[iActualRow + 1, this.dgvConsmpt.ColumnCount - 2]).Font.Name = "Verdana";
//excel.get_Range(excel.Cells[1, 1], excel.Cells[iActualRow + 1, this.dgvConsmpt.ColumnCount - 2]).Font.Size = 9;
excel.Cells.EntireColumn.AutoFit();
excel.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignJustify;
excel.Visible = true;
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
excel = null;
}
if (this.rbtnOriginalGoods.Checked && this.dgvOrgGoods.RowCount > 0)
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Application.Workbooks.Add(true);
//excel.get_Range(excel.Cells[1, 1], excel.Cells[this.dgvOrgGoods.RowCount + 1, this.dgvOrgGoods.ColumnCount - 5]).NumberFormatLocal = "@"; //Set the excel cells format as text
int iActualRow = 0;
for (int m = 0; m < this.dgvOrgGoods.RowCount; m++)
{
//if this batch no has no single bonded material, it does not need to be saved for filing purpose. July.20.2017
// Get the value of the second column in the grid, the value looks like 1000-GY7G350T/0006337069
if (strBatchListWithoutUSDcomponent.IndexOf(this.dgvConsmpt[2, m].Value.ToString().Trim().Substring(this.dgvConsmpt[2, m].Value.ToString().Trim().IndexOf("/") + 1)) > 0)
{
continue;
}
; // Just skip for this iteration;
if (String.IsNullOrEmpty(this.dgvOrgGoods[14, m].Value.ToString().Trim())) //The same mapping 'BOM In Customs'
{
iActualRow++;
for (int n = 1; n < this.dgvOrgGoods.ColumnCount - 4; n++)
{
excel.Cells[iActualRow + 1, n + 1] = this.dgvOrgGoods[n + 1, m].Value.ToString().Trim();
}
}
}
for (int y = 1; y <= iActualRow; y++)
{
excel.Cells[y + 1, 1] = y;
} //Regenerate the first column value, since it is the serial number
for (int x = 1; x < this.dgvOrgGoods.ColumnCount - 4; x++)
{
excel.Cells[1, x] = this.dgvOrgGoods.Columns[x].HeaderText.ToString().Trim();
}
//excel.get_Range(excel.Cells[1, 1], excel.Cells[1, this.dgvOrgGoods.ColumnCount - 5]).Font.Bold = true;
//excel.get_Range(excel.Cells[1, 1], excel.Cells[1, this.dgvOrgGoods.ColumnCount - 5]).AutoFilter(1, Type.Missing, Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true);
//excel.get_Range(excel.Cells[1, 1], excel.Cells[iActualRow + 1, this.dgvOrgGoods.ColumnCount - 5]).Font.Name = "Verdana";
//excel.get_Range(excel.Cells[1, 1], excel.Cells[iActualRow + 1, this.dgvOrgGoods.ColumnCount - 5]).Font.Size = 9;
excel.Cells.EntireColumn.AutoFit();
excel.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignJustify;
excel.Visible = true;
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
excel = null;
}
#endregion
}
}