public void DataxrCrossTabChange()
{
string projectnostring = ProjectNOxrLabel.Text.Split('-')[1] + '-' + ProjectNOxrLabel.Text.Split('-')[2];
var costoffer = SQLMethod.Count_Costofferform(projectnostring);
var grammar = $"USE [PurchaseProcessSystemDB] Select ProjectName as '名稱',ProjectUnit as '單位',ProjectAmount as '數量',Remark as '備註' FROM Costofferform Where ProjectCode = '{projectnostring}'";
DataTable dataTable = SQLMethod.OutPutTable(grammar);
// Create a table and add it to the detail band.
XRTable xRTable = new XRTable();
Detail.Controls.Add(xRTable);
int numRows = dataTable.Rows.Count;
int numCols = dataTable.Columns.Count;
xRTable.BeginInit();
for (int i = 0; i < numRows; i++)
{
XRTableRow xRTableRow = new XRTableRow();
xRTable.Rows.Add(xRTableRow);
for (int j = 0; j < numCols; j++)
{
XRTableCell xRTableCell = new XRTableCell();
xRTable.Rows[i].Cells.Add(xRTableCell);
xRTableCell.Text = dataTable.Rows[i][j].ToString();
}
}
xRTable.HeightF = 38 * numRows;
xRTable.WidthF = 777;
xRTable.TextAlignment = DevExpress.XtraPrinting.TextAlignment.MiddleCenter;
xRTable.Borders = DevExpress.XtraPrinting.BorderSide.All;
xRTable.EndInit();
}
private void InsertSQLsimpleButton_Click(object sender, EventArgs e)
{
if (textEdit1.Text == "")
{
MessageBox.Show("專案名稱未填入!!");
}
else
{
SQLSettings = InitialMethod.InitialSQLSetting();
SQLMethod = new SQLMethod()
{
setting = SQLSettings
};
SQLMethod.SQLConnect();
int j = SQLMethod.Count_Costofferform().Select(g => g.pk).Count();
if (j == 0)
{
j = 1;
string NO = textEdit1.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 = textEdit1.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;
}
}
}
}
private void PurchasePlansimpleButton_Click(object sender, EventArgs e)
{
PlanOutputsimpleButton.Enabled = true;
SQLSettings = InitialMethod.InitialSQLSetting();
SQLMethod = new SQLMethod()
{
setting = SQLSettings
};
SQLMethod.SQLConnect();
#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 j = 0; j < costofferforms.Count - 1; j++)
{
var first = costofferforms[j].ProjectCode;
if (first == groupcostofferform[i].ProjectCode)
{
if (first.Length == 5)
{
string content = $"{pk_number},'20M190',{ProjectItem_number},'{first}','謝偉華','{costofferforms[j].ProjectName}', '{ DateTime.Now.ToString("yyyy/MM/dd")}','{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},'20M190',{ProjectItem_number},'{first.Substring(0, 5)}','謝偉華','{costofferforms[j].ProjectName}', '{ DateTime.Now.ToString("yyyy/MM/dd")}','{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},'20M190',{ProjectItem_number},'{first.Substring(6, 5)}','謝偉華','{costofferforms[j].ProjectName}', '{ DateTime.Now.ToString("yyyy/MM/dd")}','{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
#region 請購計畫顯示
string grammar = "USE [PurchaseProcessSystemDB] Select * FROM PurchasePlan Where ProjectNO = '20M190' Order By ProjectCode";
DataTable dataTable = SQLMethod.OutPutTable(grammar);
gridControl1.DataSource = dataTable;
gridView1.OptionsView.ColumnAutoWidth = false;
gridView1.Columns[0].BestFit();
gridView1.Columns[0].Visible = false;
gridView1.Columns[1].BestFit();
gridView1.Columns[1].Visible = false;
gridView1.Columns[2].BestFit();
gridView1.Columns[2].Caption = "項次";
gridView1.Columns[3].BestFit();
gridView1.Columns[3].Caption = "請款編號";
gridView1.Columns[4].BestFit();
gridView1.Columns[4].Caption = "請購人";
gridView1.Columns[5].BestFit();
gridView1.Columns[5].Caption = "請購內容";
gridView1.Columns[6].BestFit();
gridView1.Columns[6].Caption = "預計掛件日期";
gridView1.Columns[7].BestFit();
gridView1.Columns[7].Caption = "實際掛件日期";
gridView1.Columns[8].BestFit();
gridView1.Columns[8].Caption = "採購承辦";
gridView1.Columns[9].BestFit();
gridView1.Columns[9].Caption = "預定決商日";
gridView1.Columns[10].BestFit();
gridView1.Columns[10].Caption = "實際決商日";
gridView1.Columns[11].BestFit();
gridView1.Columns[11].Caption = "長交期設備";
gridView1.Columns[12].BestFit();
gridView1.Columns[12].Caption = "聯繫單編號";
gridView1.Columns[13].BestFit();
gridView1.Columns[13].Caption = "異常單編號";
gridView1.Columns[14].BestFit();
gridView1.Columns[14].Caption = "廠商";
gridView1.Columns[15].BestFit();
gridView1.Columns[15].Caption = "訂單編號";
gridView1.Columns[16].BestFit();
gridView1.Columns[16].Caption = "聯絡人";
gridView1.Columns[17].BestFit();
gridView1.Columns[17].Caption = "連絡電話";
gridView1.Columns[18].BestFit();
gridView1.Columns[18].Caption = "估算成本";
gridView1.Columns[19].BestFit();
gridView1.Columns[19].Caption = "執行目標(A)";
gridView1.Columns[20].BestFit();
gridView1.Columns[20].Caption = "已發包金額";
gridView1.Columns[21].BestFit();
gridView1.Columns[21].Caption = "未發包金額";
gridView1.Columns[22].BestFit();
gridView1.Columns[22].Caption = "小計(B)";
gridView1.Columns[23].BestFit();
gridView1.Columns[23].Caption = "差異金額(A)-(B)";
gridView1.Columns[24].BestFit();
gridView1.Columns[24].Caption = "累積已計價金額";
gridView1.Columns[25].BestFit();
gridView1.Columns[25].Caption = "備註";
for (int i = 0; i < gridView1.Columns.Count; i++)
{
gridView1.Columns[i].OptionsColumn.AllowEdit = false;
}
#endregion
}
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
}