private void HistoryBeiAnDanDataForm_Load(object sender, EventArgs e) { this.dtpFrom.CustomFormat = " "; this.dtpTo.CustomFormat = " "; this.dtpTaxPaidDate.CustomFormat = " "; this.dtpCustomsReleaseDate.CustomFormat = " "; this.btnUpdate.Enabled = false; this.btnDelete.Enabled = false; SqlLib sqlLib = new SqlLib(); dtIE.Rows.Clear(); dtIE.Columns.Clear(); dtIE = sqlLib.GetDataTable(@"SELECT [ObjectValue] AS [IE Type] FROM B_SysInfo WHERE [ObjectName] = 'IE Type' AND [ObjectValue] <> 'RM-D'").Copy(); DataRow dr = dtIE.NewRow(); dr["IE Type"] = String.Empty; dtIE.Rows.InsertAt(dr, 0); this.cmbIEtype.DisplayMember = this.cmbIEtype.ValueMember = "IE Type"; this.cmbIEtype.DataSource = dtIE; sqlLib.Dispose(); }
private void AddNewRecordsOrUpdateExistingOnesFromExcelFile(String strFilePath) { string strConn = SqlLib.getOleBbConnnectionStringPerSpeadsheetFileExtension(strFilePath); OleDbConnection ConnToSpreadsheet = new OleDbConnection(strConn); ConnToSpreadsheet.Open(); OleDbDataAdapter oleAdapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", ConnToSpreadsheet); DataTable RM_ReceivingDataFromExcelFile = new DataTable(); oleAdapter.Fill(RM_ReceivingDataFromExcelFile); oleAdapter.Dispose(); ConnToSpreadsheet.Dispose(); if (foundBadDataIntegrityInUploadExcelFileDataTable(RM_ReceivingDataFromExcelFile)) { RM_ReceivingDataFromExcelFile.Dispose(); return; } funcLib.releaseExclusiveControlOverDataTable(); if (!funcLib.currentUserToUseDataTableExclusively()) { return; } ; SqlConnection oneConn = new SqlConnection(SqlLib.StrSqlConnection); if (oneConn.State == ConnectionState.Closed) { oneConn.Open(); } SqlCommand oneComm = new SqlCommand(); oneComm.Connection = oneConn; SqlTransaction Trans1 = oneConn.BeginTransaction(); oneComm.Transaction = Trans1; StringBuilder keyReferenceMessage = new StringBuilder(""); try { String InboundDeliveryNo = String.Empty; String ItemNo = String.Empty; String LotNo = String.Empty; for (int i = 0; i < RM_ReceivingDataFromExcelFile.Rows.Count; i++) { keyReferenceMessage.Clear(); keyReferenceMessage.Append("Record Number is " + (i + 1).ToString()); InboundDeliveryNo = RM_ReceivingDataFromExcelFile.Rows[i]["Inbound Delivery No"].ToString().Trim().ToUpper(); ItemNo = RM_ReceivingDataFromExcelFile.Rows[i]["Item No"].ToString().Trim().ToUpper(); LotNo = RM_ReceivingDataFromExcelFile.Rows[i]["Lot No"].ToString().Trim().ToUpper(); keyReferenceMessage.Append("\n Inbound Delivery No is " + InboundDeliveryNo); keyReferenceMessage.Append("\n Item No is " + ItemNo); keyReferenceMessage.Append("\n Lot N is " + LotNo); oneComm.CommandText = "SELECT * FROM C_RMReceiving WHERE " + "[Inbound Delivery No]='" + InboundDeliveryNo + "' AND [Item No]='" + ItemNo + "' AND [Lot No]='" + LotNo + "'"; Boolean hasRecord = Convert.ToInt32(oneComm.ExecuteScalar()) > 0; Boolean OverwriteOldRecord = true; if (hasRecord) { if (MessageBox.Show("Are you sure to overwrite the old record with the latest uploaded data?", "Prompt", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.No) { OverwriteOldRecord = false; } } if (OverwriteOldRecord) { oneComm.CommandText = "DELETE FROM C_RMReceiving WHERE " + "[Inbound Delivery No]='" + InboundDeliveryNo + "' AND [Item No]='" + ItemNo + "' AND [Lot No]='" + LotNo + "'"; oneComm.ExecuteNonQuery(); } else { continue; } oneComm.Parameters.Add("@TransactionType", SqlDbType.NVarChar).Value = RM_ReceivingDataFromExcelFile.Rows[i]["Transaction Type"].ToString().Trim().ToUpper(); oneComm.Parameters.Add("@CustomsEntryNo", SqlDbType.NVarChar).Value = RM_ReceivingDataFromExcelFile.Rows[i]["Customs Entry No"].ToString().Trim().ToUpper(); oneComm.Parameters.Add("@InboundDeliveryNo", SqlDbType.NVarChar).Value = InboundDeliveryNo; oneComm.Parameters.Add("@ItemNo", SqlDbType.NVarChar).Value = ItemNo; oneComm.Parameters.Add("@ItemDescription", SqlDbType.NVarChar).Value = RM_ReceivingDataFromExcelFile.Rows[i]["Item Description"].ToString().Trim().ToUpper(); oneComm.Parameters.Add("@LotNo", SqlDbType.NVarChar).Value = LotNo; oneComm.Parameters.Add("@BGDNo", SqlDbType.NVarChar).Value = RM_ReceivingDataFromExcelFile.Rows[i]["BGD No"].ToString().Trim().ToUpper(); oneComm.Parameters.Add("@RMEHB", SqlDbType.NVarChar).Value = RM_ReceivingDataFromExcelFile.Rows[i]["RM EHB"].ToString().Trim().ToUpper(); oneComm.Parameters.Add("@RMChnName", SqlDbType.NVarChar).Value = RM_ReceivingDataFromExcelFile.Rows[i]["RM CHN Name"].ToString().Trim().ToUpper(); string strPoInvoiceQty = RM_ReceivingDataFromExcelFile.Rows[i]["PO Invoice Qty"].ToString().Trim(); if (String.IsNullOrEmpty(strPoInvoiceQty)) { oneComm.Parameters.Add("@PoInvoiceQty", SqlDbType.Decimal).Value = 0.0M; } else { oneComm.Parameters.Add("@PoInvoiceQty", SqlDbType.Decimal).Value = Math.Round(Convert.ToDecimal(strPoInvoiceQty), 6); } string strPoInvoiceAmt = RM_ReceivingDataFromExcelFile.Rows[i]["PO Invoice Amount"].ToString().Trim(); if (String.IsNullOrEmpty(strPoInvoiceAmt)) { oneComm.Parameters.Add("@PoInvoiceAmount", SqlDbType.Decimal).Value = 0.0M; } else { oneComm.Parameters.Add("@PoInvoiceAmount", SqlDbType.Decimal).Value = Math.Round(Convert.ToDecimal(strPoInvoiceAmt), 2); } decimal dPoUnitPrice = 0.0M; if (!String.IsNullOrEmpty(strPoInvoiceQty)) { dPoUnitPrice = Math.Round(Convert.ToDecimal(strPoInvoiceAmt) / Convert.ToDecimal(strPoInvoiceQty), 2); } oneComm.Parameters.Add("@PoUnitPrice", SqlDbType.Decimal).Value = dPoUnitPrice; oneComm.Parameters.Add("@PoCurrency", SqlDbType.NVarChar).Value = RM_ReceivingDataFromExcelFile.Rows[i]["PO Currency"].ToString().Trim().ToUpper(); oneComm.Parameters.Add("@SapPoNo", SqlDbType.NVarChar).Value = RM_ReceivingDataFromExcelFile.Rows[i]["SAP PO No"].ToString().Trim().ToUpper(); oneComm.Parameters.Add("@RcvdSloc", SqlDbType.NVarChar).Value = RM_ReceivingDataFromExcelFile.Rows[i]["Rcvd SLOC"].ToString().Trim().ToUpper(); string strGoodsRcvdDate = RM_ReceivingDataFromExcelFile.Rows[i]["Goods Rcvd Date"].ToString().Trim(); if (String.IsNullOrEmpty(strGoodsRcvdDate)) { oneComm.Parameters.Add("@GoodsRcvdDate", SqlDbType.DateTime).Value = DBNull.Value; } else { oneComm.Parameters.Add("@GoodsRcvdDate", SqlDbType.DateTime).Value = Convert.ToDateTime(strGoodsRcvdDate); } oneComm.Parameters.Add("@ShipFromCountry", SqlDbType.NVarChar).Value = RM_ReceivingDataFromExcelFile.Rows[i]["ShipFrom Country"].ToString().Trim().ToUpper(); oneComm.Parameters.Add("@CountryofOrigin", SqlDbType.NVarChar).Value = RM_ReceivingDataFromExcelFile.Rows[i]["Country of Origin"].ToString().Trim().ToUpper(); oneComm.Parameters.Add("@Creater", SqlDbType.NVarChar).Value = funcLib.getCurrentUserName(); oneComm.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = Convert.ToDateTime(System.DateTime.Now.ToString("M/d/yyyy HH:mm:ss")); string CustomsRcvdDate = RM_ReceivingDataFromExcelFile.Rows[i]["Customs Rcvd Date"].ToString().Trim(); if (String.IsNullOrEmpty(CustomsRcvdDate)) { oneComm.Parameters.Add("@CustomsRcvdDate", SqlDbType.DateTime).Value = DBNull.Value; } else { oneComm.Parameters.Add("@CustomsRcvdDate", SqlDbType.DateTime).Value = Convert.ToDateTime(CustomsRcvdDate); } oneComm.Parameters.Add("@ReceiptsID", SqlDbType.NVarChar).Value = RM_ReceivingDataFromExcelFile.Rows[i]["Receipts ID"].ToString().Trim().ToUpper(); oneComm.Parameters.Add("@ReceiptsStatus", SqlDbType.NVarChar).Value = RM_ReceivingDataFromExcelFile.Rows[i]["Receipts Status"].ToString().Trim().ToUpper(); string GateInDate = RM_ReceivingDataFromExcelFile.Rows[i]["Gate In Date"].ToString().Trim(); if (String.IsNullOrEmpty(GateInDate)) { oneComm.Parameters.Add("@GateInDate", SqlDbType.DateTime).Value = DBNull.Value; } else { oneComm.Parameters.Add("@GateInDate", SqlDbType.DateTime).Value = Convert.ToDateTime(GateInDate); } oneComm.CommandText = "INSERT INTO C_RMReceiving([Transaction Type], [Customs Entry No], [Inbound Delivery No], [Item No], [Item Description], " + "[Lot No], [BGD No], [RM EHB], [RM CHN Name], [PO Invoice Qty], [PO Invoice Amount], [PO Unit Price], [PO Currency], " + "[SAP PO No], [Rcvd SLOC], [Goods Rcvd Date], [ShipFrom Country], [Country of Origin],[Customs Rcvd Date],[Receipts ID], [Receipts Status],[Gate In Date],[Creater], [Created Date]) " + "VALUES(@TransactionType, @CustomsEntryNo, @InboundDeliveryNo, @ItemNo, @ItemDescription, @LotNo, @BGDNo, @RMEHB, " + "@RMChnName, @PoInvoiceQty, @PoInvoiceAmount, @PoUnitPrice, @PoCurrency, @SapPoNo, @RcvdSloc, @GoodsRcvdDate," + "@ShipFromCountry, @CountryofOrigin, @CustomsRcvdDate, @ReceiptsID, @ReceiptsStatus, @GateInDate, @Creater, @CreatedDate)"; oneComm.ExecuteNonQuery(); oneComm.Parameters.Clear(); } Trans1.Commit(); MessageBox.Show("The number of records added or updated is " + RM_ReceivingDataFromExcelFile.Rows.Count, "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception) { Trans1.Rollback(); MessageBox.Show("Something wrong: " + keyReferenceMessage.ToString(), "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { Trans1.Dispose(); oneComm.Dispose(); } oneComm.Dispose(); RM_ReceivingDataFromExcelFile.Dispose(); if (oneConn.State == ConnectionState.Open) { oneConn.Close(); oneConn.Dispose(); } funcLib.releaseExclusiveControlOverDataTable(); }
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 ImportExcelData(string strFilePath, bool bJudge) { string strConn; if (bJudge) { strConn = @"Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" + strFilePath + "; Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'"; } else { strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFilePath + "; Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"; } OleDbConnection myConn = new OleDbConnection(strConn); myConn.Open(); OleDbDataAdapter myAdapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$] WHERE [GongDan No] IS NOT NULL AND [GongDan No] <> ''", myConn); DataTable dtFileGD = new DataTable(); myAdapter.Fill(dtFileGD); myAdapter = new OleDbDataAdapter("SELECT DISTINCT [GongDan No] FROM [Sheet1$]", myConn); DataTable dtFileGDN = new DataTable(); myAdapter.Fill(dtFileGDN); myAdapter.Dispose(); myConn.Dispose(); if (dtFileGD.Rows.Count == 0) { MessageBox.Show("There is no data to upload.", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Stop); dtFileGD.Dispose(); dtFileGDN.Dispose(); return; } if (MessageBox.Show("Are you sure to upload the file to batch update existing data?", "Prompt", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.No) { dtFileGD.Dispose(); dtFileGDN.Dispose(); return; } SqlConnection Conn = new SqlConnection(SqlLib.StrSqlConnection); if (Conn.State == ConnectionState.Closed) { Conn.Open(); } SqlCommand Comm = new SqlCommand(); Comm.Connection = Conn; string strGongDan = null; foreach (DataRow dr in dtFileGDN.Rows) { strGongDan += "'" + dr[0].ToString().Trim() + "',"; } strGongDan = strGongDan.Remove(strGongDan.Length - 1); Comm.CommandText = "DELETE FROM M_DailyGongDan WHERE [GongDan No] IN (" + strGongDan + ")"; Comm.ExecuteNonQuery(); dtFileGDN.Dispose(); SqlLib sqlLib = new SqlLib(); foreach (DataRow dr in dtFileGD.Rows) { Comm.Parameters.Add("@ActualStartDate", SqlDbType.NVarChar).Value = dr["Actual Start Date"].ToString().Trim(); Comm.Parameters.Add("@ActualEndDate", SqlDbType.NVarChar).Value = dr["Actual End Date"].ToString().Trim(); Comm.Parameters.Add("@BatchPath", SqlDbType.NVarChar).Value = dr["Batch Path"].ToString().Trim().ToUpper(); Comm.Parameters.Add("@BatchNo", SqlDbType.NVarChar).Value = dr["Batch No"].ToString().Trim().ToUpper(); Comm.Parameters.Add("@BomInCustoms", SqlDbType.NVarChar).Value = dr["BOM In Customs"].ToString().Trim().ToUpper(); Comm.Parameters.Add("@GongDanNo", SqlDbType.NVarChar).Value = dr["GongDan No"].ToString().Trim().ToUpper(); Comm.Parameters.Add("@FgNo", SqlDbType.NVarChar).Value = dr["FG No"].ToString().Trim(); Comm.Parameters.Add("@FgDescription", SqlDbType.NVarChar).Value = dr["FG Description"].ToString().Trim(); Comm.Parameters.Add("@LineNo", SqlDbType.Int).Value = Convert.ToInt32(dr["Line No"].ToString().Trim()); Comm.Parameters.Add("@ItemNo", SqlDbType.NVarChar).Value = dr["Item No"].ToString().Trim().ToUpper(); Comm.Parameters.Add("@ItemDescription", SqlDbType.NVarChar).Value = dr["Item Description"].ToString().Trim().ToUpper(); Comm.Parameters.Add("@LotNo", SqlDbType.NVarChar).Value = dr["Lot No"].ToString().Trim().ToUpper(); Comm.Parameters.Add("@InventoryType", SqlDbType.NVarChar).Value = dr["Inventory Type"].ToString().Trim().ToUpper(); Comm.Parameters.Add("@RMCategory", SqlDbType.NVarChar).Value = dr["RM Category"].ToString().Trim().ToUpper(); Comm.Parameters.Add("@RMEHB", SqlDbType.NVarChar).Value = dr["RM EHB"].ToString().Trim().ToUpper(); Comm.Parameters.Add("@BgdNo", SqlDbType.NVarChar).Value = dr["BGD No"].ToString().Trim(); Comm.Parameters.Add("@IeType", SqlDbType.NVarChar).Value = dr["IE Type"].ToString().Trim(); Comm.Parameters.Add("@OrderNo", SqlDbType.NVarChar).Value = dr["Order No"].ToString().Trim().ToUpper(); string strGongDanQty = dr["GongDan Qty"].ToString().Trim(); if (String.IsNullOrEmpty(strGongDanQty)) { Comm.Parameters.Add("@GongDanQty", SqlDbType.Int).Value = 0; } else { Comm.Parameters.Add("@GongDanQty", SqlDbType.Int).Value = Convert.ToInt32(strGongDanQty); } string strOrderPrice = dr["Order Price"].ToString().Trim(); if (String.IsNullOrEmpty(strOrderPrice)) { Comm.Parameters.Add("@OrderPrice", SqlDbType.Decimal).Value = 0.0M; } else { Comm.Parameters.Add("@OrderPrice", SqlDbType.Decimal).Value = Math.Round(Convert.ToDecimal(strOrderPrice), 2); } Comm.Parameters.Add("@OrderCurr", SqlDbType.NVarChar).Value = dr["Order Currency"].ToString().Trim().ToUpper(); string strTotalRmQty = dr["Total RM Qty"].ToString().Trim(); if (String.IsNullOrEmpty(strTotalRmQty)) { Comm.Parameters.Add("@TotalRmQty", SqlDbType.Decimal).Value = 0.0M; } else { Comm.Parameters.Add("@TotalRmQty", SqlDbType.Decimal).Value = Math.Round(Convert.ToDecimal(sqlLib.doubleFormat(double.Parse(strTotalRmQty))), 6); } string strTotalRmCost = dr["Total RM Cost(USD)"].ToString().Trim(); if (String.IsNullOrEmpty(strTotalRmCost)) { Comm.Parameters.Add("@TotalRmCost", SqlDbType.Decimal).Value = 0.0M; } else { Comm.Parameters.Add("@TotalRmCost", SqlDbType.Decimal).Value = Convert.ToDecimal(strTotalRmCost); } string strRmUsedQty = dr["RM Used Qty"].ToString().Trim(); if (String.IsNullOrEmpty(strRmUsedQty)) { Comm.Parameters.Add("@RmUsedQty", SqlDbType.Decimal).Value = 0.0M; } else { Comm.Parameters.Add("@RmUsedQty", SqlDbType.Decimal).Value = Math.Round(Convert.ToDecimal(sqlLib.doubleFormat(double.Parse(strRmUsedQty))), 6); } Comm.Parameters.Add("@RMCurr", SqlDbType.NVarChar).Value = dr["RM Currency"].ToString().Trim().ToUpper(); string strRmPrice = dr["RM Price"].ToString().Trim(); if (String.IsNullOrEmpty(strRmPrice)) { Comm.Parameters.Add("@RMPrice", SqlDbType.Decimal).Value = 0.0M; } else { Comm.Parameters.Add("@RMPrice", SqlDbType.Decimal).Value = Convert.ToDecimal(strRmPrice); } string strConsmpt = dr["Consumption"].ToString().Trim(); if (String.IsNullOrEmpty(strConsmpt)) { Comm.Parameters.Add("@Consumption", SqlDbType.Decimal).Value = 0.0M; } else { Comm.Parameters.Add("@Consumption", SqlDbType.Decimal).Value = Math.Round(Convert.ToDecimal(sqlLib.doubleFormat(double.Parse(strConsmpt))), 6); } string strDroolsQ = dr["Drools Quota"].ToString().Trim(); if (String.IsNullOrEmpty(strDroolsQ)) { Comm.Parameters.Add("@DroolsQuota", SqlDbType.Decimal).Value = 0.0M; } else { Comm.Parameters.Add("@DroolsQuota", SqlDbType.Decimal).Value = Math.Round(Convert.ToDecimal(sqlLib.doubleFormat(double.Parse(strDroolsQ))), 6); } string strDroolsR = dr["Drools Rate"].ToString().Trim(); if (String.IsNullOrEmpty(strDroolsR)) { Comm.Parameters.Add("@DroolsRate", SqlDbType.Decimal).Value = 0.0M; } else { Comm.Parameters.Add("@DroolsRate", SqlDbType.Decimal).Value = Math.Round(Convert.ToDecimal(sqlLib.doubleFormat(double.Parse(strDroolsR))), 6); } Comm.Parameters.Add("@ChnName", SqlDbType.NVarChar).Value = dr["CHN Name"].ToString().Trim().ToUpper(); Comm.Parameters.Add("@DroolsEHB", SqlDbType.NVarChar).Value = dr["Drools EHB"].ToString().Trim().ToUpper(); Comm.Parameters.Add("@Destination", SqlDbType.NVarChar).Value = dr["Destination"].ToString().Trim().ToUpper(); Comm.Parameters.Add("@Creater", SqlDbType.NVarChar).Value = funcLib.getCurrentUserName(); Comm.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = Convert.ToDateTime(System.DateTime.Now.ToString("M/d/yyyy")); Comm.CommandText = "INSERT INTO M_DailyGongDan([Actual Start Date], [Actual End Date], [Batch Path], [Batch No], [BOM In Customs], [GongDan No], " + "[FG No], [FG Description], [Line No], [Item No], [Item Description], [Lot No], [Inventory Type], [RM Category], [RM EHB], " + "[BGD No], [IE Type], [Order No], [GongDan Qty], [Order Price], [Order Currency], [Total RM Qty], [Total RM Cost(USD)], " + "[RM Used Qty], [RM Currency], [RM Price], [Consumption], [Drools Quota], [Drools Rate], [CHN Name], [Drools EHB], [Destination], " + "[Creater], [Created Date]) VALUES(@ActualStartDate, @ActualEndDate, @BatchPath, @BatchNo, @BomInCustoms, @GongDanNo, @FgNo, " + "@FgDescription, @LineNo, @ItemNo, @ItemDescription, @LotNo, @InventoryType, @RMCategory, @RMEHB, @BgdNo, @IeType, @OrderNo, " + "@GongDanQty, @OrderPrice, @OrderCurr, @TotalRmQty, @TotalRmCost, @RmUsedQty, @RMCurr, @RMPrice, @Consumption, @DroolsQuota, " + "@DroolsRate, @ChnName, @DroolsEHB, @Destination, @Creater, @CreatedDate)"; Comm.ExecuteNonQuery(); Comm.Parameters.Clear(); } sqlLib.Dispose(0); dtFileGD.Dispose(); Comm.Dispose(); if (Conn.State == ConnectionState.Open) { Conn.Close(); Conn.Dispose(); } MessageBox.Show("Upload successfully.", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Information); }
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 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 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 } }