public static SQLSetting InitialSQLSetting()
{
if (!Directory.Exists($"{WorkPath}\\stf"))
{
Directory.CreateDirectory($"{WorkPath}\\stf");
}
string setFile = $"{WorkPath}\\stf\\SQLSeverSetting.json";
SQLSetting settings;
if (File.Exists(setFile))
{
string json = File.ReadAllText(setFile, Encoding.UTF8);
settings = JsonConvert.DeserializeObject <SQLSetting>(json);
}
else
{
settings = new SQLSetting()
{
DataSource = "127.0.0.1",
InitialCatalog = "PurchaseProcessSystemDB",
UserID = "sa",
Password = "******"
};
string output = JsonConvert.SerializeObject(settings, Formatting.Indented, new JsonSerializerSettings());
File.WriteAllText(setFile, output, Encoding.UTF8);
}
return(settings);
}
private void ProjectBrowsingForm_Load(object sender, EventArgs e)
{
SQLSettings = InitialMethod.InitialSQLSetting();
SQLMethod = new SQLMethod()
{
setting = SQLSettings
};
SQLMethod.SQLConnect();
string grammar = "USE [PurchaseProcessSystemDB] Select projectdatettime,projectno,ownername,projectleader,projectworkleader,ponumber FROM DispatchDataTable Order By projectdatettime";
DataTable dataTable = SQLMethod.OutPutTable(grammar);
gridControl1.DataSource = dataTable;
gridView1.OptionsView.ColumnAutoWidth = false;
gridView1.Columns[0].BestFit();
gridView1.Columns[0].Caption = "日期";
gridView1.Columns[1].BestFit();
gridView1.Columns[1].Caption = "專案編號";
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 = "PO單號碼";
for (int i = 0; i < gridView1.Columns.Count; i++)
{
gridView1.Columns[i].OptionsColumn.AllowEdit = false;
}
}
public BaseModel(string tableName, string idName)
{
this.sQLSetting = Context.SQLContext.sqlSetting;
this.tableName = tableName;
this.IDName = idName;
ImportRelations();
}
public ReportFQ()
{
InitializeComponent();
SQLSettings = InitialMethod.InitialSQLSetting();
SQLMethod = new SQLMethod()
{
setting = SQLSettings
};
SQLMethod.SQLConnect();
}
public PurchasePlanBrowsingForm()
{
InitializeComponent();
SQLSettings = InitialMethod.InitialSQLSetting();
SQLMethod = new SQLMethod()
{
setting = SQLSettings
};
SQLMethod.SQLConnect();
}
public QuotationSearchForm()
{
InitializeComponent();
SQLSettings = InitialMethod.InitialSQLSetting();
SQLMethod = new SQLMethod()
{
setting = SQLSettings
};
SQLMethod.SQLConnect();
}
public MainForm()
{
InitializeComponent();
Log.Logger = new LoggerConfiguration()
.WriteTo.Console()
.WriteTo.File(path: $"{AppDomain.CurrentDomain.BaseDirectory}\\log\\log.txt",
rollingInterval: RollingInterval.Day,
outputTemplate: "{Timestamp:yyyy-MM-dd HH:mm:ss.fff zzz} [{Level:u3}] {Message:lj}{NewLine}{Exception}")
.CreateLogger(); //宣告Serilog初始化
SQLSettings = InitialMethod.InitialSQLSetting();
}
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;
}
}
}
}
/// <summary>
/// 資料庫連線
/// </summary>
/// <returns></returns>
public static SQLSetting InitialSQLSetting()
{
SQLSetting setting = new SQLSetting();
if (!Directory.Exists($"{WorkPath}\\stf"))
{
Directory.CreateDirectory($"{WorkPath}\\stf");
}
string SettingPath = $"{WorkPath}\\stf\\SQL.json";
try
{
if (File.Exists(SettingPath))
{
string json = File.ReadAllText(SettingPath, Encoding.UTF8);
setting = JsonConvert.DeserializeObject <SQLSetting>(json);
}
else
{
SQLSetting Setting = new SQLSetting()
{
DataSource = "127.0.0.1",
InitialCatalog = "PurchaseProcessSystemDB",
UserID = "sa",
Password = "******"
};
setting = Setting;
string output = JsonConvert.SerializeObject(setting, Formatting.Indented, new JsonSerializerSettings());
File.WriteAllText(SettingPath, output);
}
}
catch (Exception ex)
{
Log.Error(ex, "System setting initial 資料庫JSON failed.");
}
return(setting);
}
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
}
public static void Main(string[] args)
{
sql = new SQLSetting();
xls = new XLSSetting();
msg = new MSGSetting();
}
public ExcelExport(XLSSetting xls, SQLSetting sql)
{
this.xls = xls;
this.sql = sql;
}
