private void ImportsimpleButton_Click(object sender, EventArgs e) { #region 匯入報價單至資料庫 try { if (SQLMethod.Search_Costofferform(comboBoxEdit1.Text).Count() == 0) { int j = SQLMethod.Count_Costofferform().Select(g => g.pk).Count(); if (j == 0) { j = 1; string NO = comboBoxEdit1.Text; for (int i = 1; i < cell1.Count; i++) { string content = $"{j},'{NO}','{cell1[i].ToString()}','{cell2[i].ToString()}','{cell3[i].ToString()}','{cell4[i].ToString()}','{cell5[i].ToString()}','{cell6[i].ToString()}','{cell7[i].ToString()}','{cell8[i].ToString()}'"; SQLMethod.Insert_costofferforms(content); j += 1; } } else { j = SQLMethod.Count_Costofferform().Select(g => g.pk).Count() + 1; string NO = comboBoxEdit1.Text; for (int i = 1; i < cell1.Count; i++) { string content = $"{j},'{NO}','{cell1[i].ToString()}','{cell2[i].ToString()}','{cell3[i].ToString()}','{cell4[i].ToString()}','{cell5[i].ToString()}','{cell6[i].ToString()}','{cell7[i].ToString()}','{cell8[i].ToString()}'"; SQLMethod.Insert_costofferforms(content); j += 1; } } MessageBox.Show("報價單匯入成功!!"); #region 將資料從請購單匯入至請購計畫資料庫 costofferforms = SQLMethod.Count_Costofferform(); purchaseplans = SQLMethod.Count_purchaseplan(); groupcostofferform = SQLMethod.Group_costofferform(); pk_number = purchaseplans.Select(g => g.pk).Count(); if (pk_number != 0) { pk_number = purchaseplans.Select(g => g.pk).Count() + 1; } else { pk_number = 1; } for (int i = 0; i < groupcostofferform.Count; i++) { for (int k = 0; k < costofferforms.Count - 1; k++) { var first = costofferforms[k].ProjectCode; if (first == groupcostofferform[i].ProjectCode) { if (first.Length == 5) { string content = $"{pk_number},'{comboBoxEdit1.Text}','{first.Substring(0, 1)}',{ProjectItem_number},'{first}','{null}','{costofferforms[k].ProjectName}', '{ DateTime.Now.ToString("yyyy/MM/dd")}','{null}','{null}', '{ DateTime.Now.ToString("yyyy/MM/dd")}', '{null}','{null}','{null}','{null}','{null}','{null}','{null}','{null}','{groupcostofferform[i].Money}','{Math.Round((int)groupcostofferform[i].Money * 0.9)}','{null}','{Math.Round((int)groupcostofferform[i].Money * 0.9)}','{Math.Round((int)groupcostofferform[i].Money * 0.9)}',0,'{null}','{null}' "; SQLMethod.Insert_purchaseplan(content); pk_number += 1; ProjectItem_number += 1; break; } else { string content = $"{pk_number},'{comboBoxEdit1.Text}','{first.Substring(0, 1)}',{ProjectItem_number},'{first.Substring(0, 5)}','{null}','{costofferforms[k].ProjectName}', '{ DateTime.Now.ToString("yyyy/MM/dd")}','{null}','{null}', '{ DateTime.Now.ToString("yyyy/MM/dd")}', '{null}','{null}','{null}','{null}','{null}','{null}','{null}','{null}','{groupcostofferform[i].Money}','{Math.Round((int)groupcostofferform[i].Money * 0.9)}','{null}','{Math.Round((int)groupcostofferform[i].Money * 0.9)}','{Math.Round((int)groupcostofferform[i].Money * 0.9)}',0,'{null}','{null}' "; SQLMethod.Insert_purchaseplan(content); pk_number += 1; ProjectItem_number += 1; string content1 = $"{pk_number},'{comboBoxEdit1.Text}','{first.Substring(6, 1)}',{ProjectItem_number},'{first.Substring(6, 5)}','{null}','{costofferforms[k].ProjectName}', '{ DateTime.Now.ToString("yyyy/MM/dd")}','{null}','{null}', '{ DateTime.Now.ToString("yyyy/MM/dd")}', '{null}','{null}','{null}','{null}','{null}','{null}','{null}','{null}','{groupcostofferform[i].Money}','{Math.Round((int)groupcostofferform[i].Money * 0.9)}','{null}','{Math.Round((int)groupcostofferform[i].Money * 0.9)}','{Math.Round((int)groupcostofferform[i].Money * 0.9)}',0,'{null}','{null}' "; SQLMethod.Insert_purchaseplan(content1); pk_number += 1; ProjectItem_number += 1; break; } } } } #endregion } else { DialogResult Result = MessageBox.Show("此專案已匯入過報價單,是否覆蓋原本的報價單??", "表單訊息", MessageBoxButtons.YesNo); if (Result == System.Windows.Forms.DialogResult.Yes) { SQLMethod.Delete_Costofferform(comboBoxEdit1.Text); SQLMethod.Delete_PurchasePlan(comboBoxEdit1.Text); int j = SQLMethod.Count_Costofferform().Select(g => g.pk).Count(); if (j == 0) { j = 1; string NO = comboBoxEdit1.Text; for (int i = 1; i < cell1.Count; i++) { string content = $"{j},'{NO}','{cell1[i].ToString()}','{cell2[i].ToString()}','{cell3[i].ToString()}','{cell4[i].ToString()}','{cell5[i].ToString()}','{cell6[i].ToString()}','{cell7[i].ToString()}','{cell8[i].ToString()}'"; SQLMethod.Insert_costofferforms(content); j += 1; } } else { j = SQLMethod.Count_Costofferform().Select(g => g.pk).Count() + 1; string NO = comboBoxEdit1.Text; for (int i = 1; i < cell1.Count; i++) { string content = $"{j},'{NO}','{cell1[i].ToString()}','{cell2[i].ToString()}','{cell3[i].ToString()}','{cell4[i].ToString()}','{cell5[i].ToString()}','{cell6[i].ToString()}','{cell7[i].ToString()}','{cell8[i].ToString()}'"; SQLMethod.Insert_costofferforms(content); j += 1; } } MessageBox.Show("報價單重新匯入成功!!"); #region 將資料從請購單匯入至請購計畫資料庫 costofferforms = SQLMethod.Count_Costofferform(); purchaseplans = SQLMethod.Count_purchaseplan(); groupcostofferform = SQLMethod.Group_costofferform(); pk_number = purchaseplans.Select(g => g.pk).Count(); if (pk_number != 0) { pk_number = purchaseplans.Select(g => g.pk).Count() + 1; } else { pk_number = 1; } for (int i = 0; i < groupcostofferform.Count; i++) { for (int k = 0; k < costofferforms.Count - 1; k++) { var first = costofferforms[k].ProjectCode; if (first == groupcostofferform[i].ProjectCode) { if (first.Length == 5) { string content = $"{pk_number},'{comboBoxEdit1.Text}','{first.Substring(0, 1)}',{ProjectItem_number},'{first}','{null}','{costofferforms[k].ProjectName}', '{ DateTime.Now.ToString("yyyy/MM/dd")}','{null}','{null}', '{ DateTime.Now.ToString("yyyy/MM/dd")}', '{null}','{null}','{null}','{null}','{null}','{null}','{null}','{null}','{groupcostofferform[i].Money}','{Math.Round((int)groupcostofferform[i].Money * 0.9)}','{null}','{Math.Round((int)groupcostofferform[i].Money * 0.9)}','{Math.Round((int)groupcostofferform[i].Money * 0.9)}',0,'{null}','{null}' "; SQLMethod.Insert_purchaseplan(content); pk_number += 1; ProjectItem_number += 1; break; } else { string content = $"{pk_number},'{comboBoxEdit1.Text}','{first.Substring(0, 1)}',{ProjectItem_number},'{first.Substring(0, 5)}','{null}','{costofferforms[k].ProjectName}', '{ DateTime.Now.ToString("yyyy/MM/dd")}','{null}','{null}', '{ DateTime.Now.ToString("yyyy/MM/dd")}', '{null}','{null}','{null}','{null}','{null}','{null}','{null}','{null}','{groupcostofferform[i].Money}','{Math.Round((int)groupcostofferform[i].Money * 0.9)}','{null}','{Math.Round((int)groupcostofferform[i].Money * 0.9)}','{Math.Round((int)groupcostofferform[i].Money * 0.9)}',0,'{null}','{null}' "; SQLMethod.Insert_purchaseplan(content); pk_number += 1; ProjectItem_number += 1; string content1 = $"{pk_number},'{comboBoxEdit1.Text}','{first.Substring(6, 1)}',{ProjectItem_number},'{first.Substring(6, 5)}','{null}','{costofferforms[k].ProjectName}', '{ DateTime.Now.ToString("yyyy/MM/dd")}','{null}','{null}', '{ DateTime.Now.ToString("yyyy/MM/dd")}', '{null}','{null}','{null}','{null}','{null}','{null}','{null}','{null}','{groupcostofferform[i].Money}','{Math.Round((int)groupcostofferform[i].Money * 0.9)}','{null}','{Math.Round((int)groupcostofferform[i].Money * 0.9)}','{Math.Round((int)groupcostofferform[i].Money * 0.9)}',0,'{null}','{null}' "; SQLMethod.Insert_purchaseplan(content1); pk_number += 1; ProjectItem_number += 1; break; } } } } #endregion } } } catch (Exception ex) { Log.Error(ex, "報價單匯入失敗!!"); MessageBox.Show("報價單匯入失敗!!"); } #endregion }