/DTI_PID/ID2PSN/DB.cs - Diff - HYTOS - 일정관리

개정판 d1afd412

ID	d1afd412e5ce886a8463e2fd251633da531e4f80
상위	c3b48db0
하위	add4c093

이지연이(가) 약 3년 전에 추가함

issue #000: Eq Tag Pump 진행

Change-Id: Iac6d96c53d5e1a6a1085e70fb2bd699c938429a3

DTI_PID/ID2PSN/DB.cs
162	162
163	163	matched = names.FirstOrDefault(param => param == PSN_PIPESYSTEMNETWORK);
164	164	dicColCheck.Clear();
165		dicColCheck.Add("OID", "NVARCHAR(255)");
166		dicColCheck.Add("Type", "NVARCHAR(255)");
167		dicColCheck.Add("OrderNumber", "NVARCHAR(255)");
168		dicColCheck.Add("Pipeline_OID", "NVARCHAR(255)");
169		dicColCheck.Add("From_Data", "NVARCHAR(255)");
	165	dicColCheck.Add("OID", "NVARCHAR(255)");
	166	dicColCheck.Add("Type", "NVARCHAR(255)");
	167	dicColCheck.Add("OrderNumber", "NVARCHAR(255)");
	168	dicColCheck.Add("Pipeline_OID", "NVARCHAR(255)");
	169	dicColCheck.Add("From_Data", "NVARCHAR(255)");
170	170	dicColCheck.Add("To_Data", "NVARCHAR(255)");
171	171	dicColCheck.Add("TopologySet_OID_Key", "NVARCHAR(125)");
172	172	dicColCheck.Add("PSNRevisionNumber", "NVARCHAR(255)");
...	...
177	177	dicColCheck.Add("IncludingVirtualData", "NVARCHAR(10)");
178	178	dicColCheck.Add("PSNAccuracy", "REAL");
179	179	dicColCheck.Add("Pocket", "NVARCHAR(10)");
180
	180	dicColCheck.Add("EGTag", "NVARCHAR(255)");
	181	dicColCheck.Add("HasMLTags", "NVARCHAR(50)");
	182
181	183	if (matched == null)
182	184	{
183	185	var query = $"CREATE TABLE {PSN_PIPESYSTEMNETWORK} (OID NVARCHAR(255), Type NVARCHAR(255), OrderNumber NVARCHAR(255), Pipeline_OID NVARCHAR(255), From_Data NVARCHAR(255), " +
184	186	"To_Data NVARCHAR(255), TopologySet_OID_Key NVARCHAR(125), PSNRevisionNumber NVARCHAR(255), IsValid INTEGER, Status NVARCHAR(255), PBS NVARCHAR(255), Drawings NVARCHAR(255), " +
185		"IncludingVirtualData NVARCHAR(10), PSNAccuracy REAL, Pocket NVARCHAR(10))";
	187	"IncludingVirtualData NVARCHAR(10), PSNAccuracy REAL, Pocket NVARCHAR(10), EGTag NVARCHAR(255), HasMLTags NVARCHAR(50))";
186	188	using (var cmd = connection.GetSqlStringCommand(query))
187	189	{
188	190	cmd.ExecuteNonQuery();
...	...
740	742
741	743	return dt;
742	744	}
743
	745
744	746	public static DataTable SelectInsulationPurpose()
745	747	{
746	748	DataTable dt = null;
...	...
852	854	{
853	855	try
854	856	{
855		// var query = "SELECT DISTINCT Attribute, DisplayAttribute FROM SymbolAttribute;";
	857	// var query = "SELECT DISTINCT Attribute, DisplayAttribute FROM SymbolAttribute;";
856	858	var query = "SELECT DISTINCT Attribute FROM SymbolAttribute;";
857	859	using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
858	860	{
...	...
1137	1139	return dt;
1138	1140	}
1139	1141
1140
	1142
1141	1143	public static DataTable SelectEquipmentNoPocketSetting()
1142	1144	{
1143	1145	DataTable dt = null;
...	...
1183	1185	}
1184	1186	}
1185	1187
1186		return dt;
	1188	return dt;
1187	1189	}
1188	1190
1189	1191	public static bool SaveHeaderSetting(List<HeaderInfo> headerInfos)
...	...
1213	1215	}
1214	1216	txn.Commit();
1215	1217	}
1216
	1218
1217	1219	}
1218	1220	catch (Exception ex)
1219	1221	{
...	...
1312	1314	{
1313	1315	var query = $"DELETE FROM {PSN_TRANSFORMKEYWORD_SETTING}";
1314	1316	connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
1315
	1317
1316	1318	foreach (KeywordItem item in keywordItems)
1317	1319	{
1318	1320	query = $"INSERT INTO {PSN_TRANSFORMKEYWORD_SETTING} ([INDEX], NAME, KEYWORD) VALUES (@INDEX, @NAME, @KEYWORD)";
1319	1321	var cmd = connection.GetSqlStringCommand(query);
1320	1322	AddWithValue(cmd, "@INDEX", item.Index);
1321	1323	AddWithValue(cmd, "@NAME", item.Name);
1322		AddWithValue(cmd, "@KEYWORD", item.Keyword);
	1324	AddWithValue(cmd, "@KEYWORD", item.Keyword);
1323	1325	connection.ExecuteNonQuery(cmd, txn);
1324	1326	}
1325
	1327
1326	1328	txn.Commit();
1327	1329	}
1328	1330	catch (Exception ex)
...	...
1534	1536	{
1535	1537	if (names.Count == 0)
1536	1538	{
1537
	1539
1538	1540	var query = $"CREATE TABLE ARS_COMMON.dbo.PSNRevision ([OID] [uniqueidentifier] ,[Project_OID] [uniqueidentifier] ," +
1539	1541	$"[ProjectCode] nvarchar(255), [RevNumber] int ,[UserName] nvarchar(255) ,[TimeData] nvarchar(255) ," +
1540	1542	$"[PSNDatabasePath] nvarchar(255) , [PsnByPBSFilter] varchar(20) ,[PsnByNPDFilter] varchar(20) ,[PidDrawings] int , " +
...	...
1616	1618	$", @PsnByPBSFilter, @PsnByNPDFilter, @PidDrawings, @PipeSystems, @PipeLines, @Topologies, @PipeSystemNetworks, @InValidPSNs, " +
1617	1619	$"@E2E, @E2B, @B2E, @HDE, @HD2, @HDB, @B2B, @LastModificationdate, @ID2_PSN)";
1618	1620	var cmd = connection.GetSqlStringCommand(query);
1619
	1621
1620	1622	AddWithValue(cmd, "@ProjectCode", ProjectCode);
1621	1623	AddWithValue(cmd, "@RevNumber", RevNumber);
1622	1624	AddWithValue(cmd, "@UserName", UserName);
...	...
1680	1682	using (var txn = connection.BeginTransaction())
1681	1683	{
1682	1684	try
1683		{
1684
	1685	{
	1686
1685	1687	var query = $"INSERT INTO ARS_COMMON.dbo.PSNRevision (OID, Project_OID, ProjectCode, RevNumber, UserName, TimeData, PSNDatabasePath, " +
1686	1688	$"PsnByPBSFilter, PsnByNPDFilter, PidDrawings, PipeSystems, PipeLines, Topologies, PipeSystemNetworks, InValidPSNs, E2E, E2B, B2E, HDE, " +
1687	1689	$"HD2, HDB, B2B, LastModificationdate, ID2_PSN) VALUES (NEWID(), NEWID(), @ProjectCode, @RevNumber, @UserName, @TimeData, @PSNDatabasePath" +
...	...
1720	1722	// AddWithValue(cmd, "@LastModificationdate", oDateTime.ToString(ci));
1721	1723	AddWithValue(cmd, "@LastModificationdate", "");
1722	1724	AddWithValue(cmd, "@ID2_PSN", "Y");
1723
	1725
1724	1726	connection.ExecuteNonQuery(cmd, txn);
1725
	1727
1726	1728	txn.Commit();
1727	1729	}
1728	1730	catch (Exception ex)
...	...
1746	1748	{
1747	1749	ID2Info id2Info = ID2Info.GetInstance();
1748	1750
1749		bool result = true;
	1751	bool result = true;
1750	1752
1751	1753	using (IAbstractDatabase connection = id2Info.CreateConnection())
1752	1754	{
...	...
1888	1890	foreach (DataRow row in item.PipeSystemNetwork.Rows)
1889	1891	{
1890	1892	query = $"INSERT INTO {PSN_PIPESYSTEMNETWORK} " +
1891		$"(OID, Type, OrderNumber, Pipeline_OID, FROM_DATA, TO_DATA, TopologySet_OID_Key, PSNRevisionNumber, PBS, Drawings, IsValid, Status, IncludingVirtualData, PSNAccuracy, Pocket) VALUES " +
1892		$"(@OID, @Type, @OrderNumber, @Pipeline_OID, @FROM_DATA, @TO_DATA, @TopologySet_OID_Key, @PSNRevisionNumber, @PBS, @Drawings, @IsValid, @Status, @IncludingVirtualData, @PSNAccuracy, @Pocket)";
	1893	$"(OID, Type, OrderNumber, Pipeline_OID, FROM_DATA, TO_DATA, TopologySet_OID_Key, PSNRevisionNumber, PBS, Drawings, IsValid, Status, IncludingVirtualData, PSNAccuracy, Pocket, EGTag, HasMLTags) VALUES " +
	1894	$"(@OID, @Type, @OrderNumber, @Pipeline_OID, @FROM_DATA, @TO_DATA, @TopologySet_OID_Key, @PSNRevisionNumber, @PBS, @Drawings, @IsValid, @Status, @IncludingVirtualData, @PSNAccuracy, @Pocket, @EGTag, @HasMLTags)";
1893	1895	var cmd = connection.GetSqlStringCommand(query);
1894	1896	AddWithValue(cmd, "@OID", string.IsNullOrEmpty(row["OID"].ToString()) ? "" : row["OID"].ToString());
1895	1897	AddWithValue(cmd, "@Type", string.IsNullOrEmpty(row["Type"].ToString()) ? "" : row["Type"].ToString());
...	...
1922	1924
1923	1925	AddWithValue(cmd, "@Pocket", string.IsNullOrEmpty(row["Pocket"].ToString()) ? "Yes" : row["Pocket"].ToString());
1924	1926
	1927	AddWithValue(cmd, "@EGTag", string.IsNullOrEmpty(row["EGTag"].ToString()) ? "" : row["EGTag"].ToString());
	1928	AddWithValue(cmd, "@HasMLTags", string.IsNullOrEmpty(row["HasMLTags"].ToString()) ? "False" : row["HasMLTags"].ToString());
1925	1929	connection.ExecuteNonQuery(cmd, txn);
1926	1930	}
1927	1931
...	...
1960	1964	connection.ExecuteNonQuery(cmd, txn);
1961	1965	}
1962	1966
1963		if(id2Info.ID2DBType == ID2DB_Type.MSSQL)
	1967	if (id2Info.ID2DBType == ID2DB_Type.MSSQL)
1964	1968	{
1965	1969	query = $"If(db_id(N'" + PSN_COMMON + "') IS NULL) CREATE DATABASE [" + PSN_COMMON + "]";
1966	1970	connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
1967	1971	}
1968
	1972
1969	1973
1970	1974	txn.Commit();
1971	1975	}
...	...
1977	1981
1978	1982	}
1979	1983	void AddColumn(string TableName, Dictionary<string, string> dicCol, List<string> colnames)
1980		{
	1984	{
1981	1985	bool check = false;
1982	1986	if (colnames != null)
1983	1987	{
...	...
2249	2253	param.Value = row["UID"].ToString();
2250	2254	cmd.Parameters.Add(param);
2251	2255	}
2252
	2256
2253	2257	{
2254	2258	var param = cmd.CreateParameter();
2255	2259	param.ParameterName = "@Code";
...	...
2263	2267	param.Value = row["Description"].ToString();
2264	2268	cmd.Parameters.Add(param);
2265	2269	}
2266
	2270
2267	2271	{
2268	2272	var param = cmd.CreateParameter();
2269	2273	param.ParameterName = "@Remarks";
...	...
2345	2349	newRow["PSNAccuracy"] = row["PSNAccuracy"].ToString();
2346	2350
2347	2351	newRow["Pocket"] = row["Pocket"].ToString();
2348
	2352
	2353	newRow["EGTag"] = row["EGTag"].ToString();
	2354	newRow["HasMLTags"] = row["HasMLTags"].ToString();
2349	2355	result.PipeSystemNetwork.Rows.Add(newRow);
2350	2356	}
2351	2357	}
...	...
2537	2543
2538	2544	newRow["PBS"] = row["PBS"].ToString();
2539	2545	newRow["Drawings"] = row["Drawings"].ToString();
2540
	2546
2541	2547	newRow["IncludingVirtualData"] = row["IncludingVirtualData"].ToString();
2542	2548	newRow["PSNAccuracy"] = row["PSNAccuracy"].ToString();
2543	2549
2544	2550	newRow["Pocket"] = row["Pocket"].ToString();
2545
	2551	newRow["EGTag"] = row["EGTag"].ToString();
	2552	newRow["HasMLTags"] = row["HasMLTags"].ToString();
	2553
2546	2554	dt.Rows.Add(newRow);
2547	2555	}
2548	2556	}
...	...
2580	2588	return dt;
2581	2589	}
2582	2590
2583
	2591
2584	2592	//Anohter DB
2585	2593	public static bool ConnTestAndCreateAnotherTable()
2586	2594	{
...	...
2675	2683	dicColCheck.Add("IncludingVirtualData", "NVARCHAR(10)");
2676	2684	dicColCheck.Add("PSNAccuracy", "REAL");
2677	2685	dicColCheck.Add("Pocket", "NVARCHAR(10)");
2678
	2686	dicColCheck.Add("EGTag", "NVARCHAR(255)");
	2687	dicColCheck.Add("HasMLTags", "NVARCHAR(50)");
	2688
2679	2689	if (matched == null)
2680	2690	{
2681	2691	var query = $"CREATE TABLE {PSN_PIPESYSTEMNETWORK} (OID NVARCHAR(255), Type NVARCHAR(255), OrderNumber NVARCHAR(255), Pipeline_OID NVARCHAR(255), From_Data NVARCHAR(255), " +
2682	2692	"To_Data NVARCHAR(255), TopologySet_OID_Key NVARCHAR(125), PSNRevisionNumber NVARCHAR(255), IsValid INTEGER, Status NVARCHAR(255), PBS NVARCHAR(255), Drawings NVARCHAR(255), " +
2683		"IncludingVirtualData NVARCHAR(10), PSNAccuracy REAL, Pocket NVARCHAR(10))";
	2693	"IncludingVirtualData NVARCHAR(10), PSNAccuracy REAL, Pocket NVARCHAR(10), EGTag NVARCHAR(255), HasMLTags NVARCHAR(50))";
2684	2694	using (var cmd = connection.GetSqlStringCommand(query))
2685	2695	{
2686	2696	cmd.ExecuteNonQuery();
...	...
3012	3022	{
3013	3023	AddColumn(PSN_AIRFINCOOLERSETTING, dicColCheck);
3014	3024	}
3015
	3025
3016	3026
3017	3027	var query2 = $"If(db_id(N'" + PSN_COMMON + "') IS NULL) CREATE DATABASE [" + PSN_COMMON + "]";
3018	3028	if (id2Info.ID2DBType == AnotherID2DB_Type.MSSQL)
...	...
3098	3108	AddWithValue(cmd, "@UserName", UserName);
3099	3109
3100	3110	System.Globalization.CultureInfo ci = new System.Globalization.CultureInfo("en-US");
3101		DateTime oDateTime = DateTime.Now;
	3111	DateTime oDateTime = DateTime.Now;
3102	3112
3103	3113	AddWithValue(cmd, "@TimeData", oDateTime.ToString(ci)); //String.Format("{0:G}", dt) // DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")
3104
	3114
3105	3115	AddWithValue(cmd, "@PSNDatabasePath", PSNDatabasePath);
3106	3116
3107	3117	AddWithValue(cmd, "@PsnByPBSFilter", PsnByPBSFilter);
...	...
3123	3133
3124	3134	AddWithValue(cmd, "@LastModificationdate", oDateTime.ToString(ci));
3125	3135	AddWithValue(cmd, "@ID2_PSN", "Y");
3126
	3136
3127	3137	connection.ExecuteNonQuery(cmd, txn);
3128	3138
3129	3139	txn.Commit();
...	...
3411	3421	foreach (DataRow row in item.PipeSystemNetwork.Rows)
3412	3422	{
3413	3423	query = $"INSERT INTO {PSN_PIPESYSTEMNETWORK} " +
3414		$"(OID, Type, OrderNumber, Pipeline_OID, FROM_DATA, TO_DATA, TopologySet_OID_Key, PSNRevisionNumber, PBS, Drawings, IsValid, Status, IncludingVirtualData, PSNAccuracy, Pocket) VALUES " +
3415		$"(@OID, @Type, @OrderNumber, @Pipeline_OID, @FROM_DATA, @TO_DATA, @TopologySet_OID_Key, @PSNRevisionNumber, @PBS, @Drawings, @IsValid, @Status, @IncludingVirtualData, @PSNAccuracy, @Pocket)";
	3424	$"(OID, Type, OrderNumber, Pipeline_OID, FROM_DATA, TO_DATA, TopologySet_OID_Key, PSNRevisionNumber, PBS, Drawings, IsValid, Status, IncludingVirtualData, PSNAccuracy, Pocket, EGTag, HasMLTags) VALUES " +
	3425	$"(@OID, @Type, @OrderNumber, @Pipeline_OID, @FROM_DATA, @TO_DATA, @TopologySet_OID_Key, @PSNRevisionNumber, @PBS, @Drawings, @IsValid, @Status, @IncludingVirtualData, @PSNAccuracy, @Pocket, @EGTag, @HasMLTags)";
3416	3426	var cmd = connection.GetSqlStringCommand(query);
3417	3427	AddWithValue(cmd, "@OID", string.IsNullOrEmpty(row["OID"].ToString()) ? "" : row["OID"].ToString());
3418	3428	AddWithValue(cmd, "@Type", string.IsNullOrEmpty(row["Type"].ToString()) ? "" : row["Type"].ToString());
...	...
3444	3454	AddWithValue(cmd, "@PSNAccuracy", row["PSNAccuracy"].ToString().Replace("%", ""));
3445	3455
3446	3456	AddWithValue(cmd, "@Pocket", string.IsNullOrEmpty(row["Pocket"].ToString()) ? "Yes" : row["Pocket"].ToString());
	3457	AddWithValue(cmd, "@EGTag", string.IsNullOrEmpty(row["EGTag"].ToString()) ? "" : row["EGTag"].ToString());
	3458	AddWithValue(cmd, "@HasMLTags", string.IsNullOrEmpty(row["HasMLTags"].ToString()) ? "False" : row["HasMLTags"].ToString());
	3459
3447	3460	connection.ExecuteNonQuery(cmd, txn);
3448	3461	}
3449	3462
...	...
3748	3761	AddWithValue(cmd, "@NAME", row["NAME"].ToString());
3749	3762	connection.ExecuteNonQuery(cmd, txn);
3750	3763	}
3751
	3764
3752	3765
3753	3766	txn.Commit();
3754	3767	}
...	...
3782	3795	{
3783	3796	try
3784	3797	{
3785
	3798
3786	3799	var query = $"If(db_id(N'" + PSN_COMMON + "') IS NULL) CREATE DATABASE [" + PSN_COMMON + "]";
3787	3800	if (id2Info.ID2DBType == AnotherID2DB_Type.MSSQL)
3788		{
	3801	{
3789	3802	connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
3790	3803	}
3791	3804

내보내기 Unified diff

프로젝트

일반

사용자정보

HYTOS

개정판 d1afd412