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
}
