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
}
}