Пример #1
0
        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();
        }
Пример #2
0
        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();
        }
Пример #3
0
        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
            }
        }
Пример #4
0
        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);
        }
Пример #5
0
        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();
            }
        }
Пример #6
0
        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);
            }
        }
Пример #7
0
        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
            }
        }