public static Dictionary <string, string> GetSortingProcessInfo(string sortingdate, string taskno, string picklinecode) { Dictionary <string, string> sortingDictionary = new Dictionary <string, string>(); using (var cn = new MySqlConnection(AppUtility.AppUtil._LocalConnectionString)) { cn.Open(); using (var cm1 = cn.CreateCommand()) { cm1.CommandText = "SELECT * FROM t_sortline_process WHERE " + "(1 = 1 and ((@SORTINGTASKNO is null) or (SORTINGTASKNO = @SORTINGTASKNO))) AND" + "(1 = 1 and ((@SORT_DATE is null) or (ORDERDATE = @SORT_DATE))) AND" + "(1 = 1 and ((@SORTLINE_CODE is null) or (PICKLINECODE = @SORTLINE_CODE)))"; cm1.Parameters.AddWithValue("@SORTINGTASKNO", taskno); cm1.Parameters.AddWithValue("@SORT_DATE", sortingdate); cm1.Parameters.AddWithValue("@SORTLINE_CODE", picklinecode); using (var dr = new Csla.Data.SafeDataReader(cm1.ExecuteReader())) { if (dr.Read()) { sortingDictionary.Add("SORTINGTASKNO", dr.GetString("SORTINGTASKNO")); sortingDictionary.Add("ORDERDATE ", dr.GetString("ORDERDATE")); sortingDictionary.Add("PICKLINECODE", dr.GetString("PICKLINECODE")); sortingDictionary.Add("PICKLINENAME", dr.GetString("PICKLINENAME")); sortingDictionary.Add("QTY_PRODCUT_TOT", dr.GetInt32("QTY_PRODCUT_TOT").ToString()); sortingDictionary.Add("QTY_ROUTE_TOT", dr.GetInt32("QTY_ROUTE_TOT").ToString()); sortingDictionary.Add("QTY_CUSTOMER_TOT", dr.GetInt32("QTY_CUSTOMER_TOT").ToString()); sortingDictionary.Add("QTY_PRODUCT", dr.GetInt32("QTY_PRODUCT").ToString()); sortingDictionary.Add("QTY_ROUTE", dr.GetInt32("QTY_ROUTE").ToString()); sortingDictionary.Add("QTY_CUSTOMER", dr.GetInt32("QTY_CUSTOMER").ToString()); sortingDictionary.Add("CUSTOMER_CODE", dr.GetString("CUSTOMER_CODE") != "" ? dr.GetString("CUSTOMER_CODE"):"无"); sortingDictionary.Add("CUSTOMER_DESC", dr.GetString("CUSTOMER_DESC") != "" ? dr.GetString("CUSTOMER_DESC"):"无"); sortingDictionary.Add("ROUTE_CODE", dr.GetString("ROUTE_CODE") != "" ? dr.GetString("ROUTE_CODE") : "无"); sortingDictionary.Add("ROUTE_NAME", dr.GetString("ROUTE_NAME") != "" ? dr.GetString("ROUTE_NAME") : "无"); sortingDictionary.Add("RECEIVE_TIME", dr.GetDateTime("RECEIVE_TIME").ToString()); sortingDictionary.Add("EFFICIENCY", dr.GetDouble("EFFICIENCY").ToString("#0.00") + "条/小时"); sortingDictionary.Add("Progress", dr.GetDouble("Progress").ToString()); } } } } return(sortingDictionary); }
public static SortingProcessList GetSortingProcessList(string sortingdate, string taskno, string picklinecode) { SortingProcessList sortingProcessList = new SortingProcessList(); using (var cn = new MySqlConnection(AppUtility.AppUtil._LocalConnectionString)) { cn.Open(); using (var cm1 = cn.CreateCommand()) { cm1.CommandText = "SELECT * FROM t_sortline_process WHERE " + "(1 = 1 and ((@SORTINGTASKNO is null) or (SORTINGTASKNO = @SORTINGTASKNO))) AND" + "(1 = 1 and ((@SORT_DATE is null) or (ORDERDATE = @SORT_DATE))) AND" + "(1 = 1 and ((@SORTLINE_CODE is null) or (PICKLINECODE = @SORTLINE_CODE)))"; cm1.Parameters.AddWithValue("@SORTINGTASKNO", taskno); cm1.Parameters.AddWithValue("@SORT_DATE", sortingdate); cm1.Parameters.AddWithValue("@SORTLINE_CODE", picklinecode); using (var dr = new Csla.Data.SafeDataReader(cm1.ExecuteReader())) { while (dr.Read()) { SortingProcessInfo sortingProcess = new SortingProcessInfo(); sortingProcess.SORTINGTASKNO = dr.GetString("SORTINGTASKNO"); sortingProcess.ORDERDATE = dr.GetString("ORDERDATE"); sortingProcess.PICKLINECODE = dr.GetString("PICKLINECODE"); sortingProcess.PICKLINENAME = dr.GetString("PICKLINENAME"); sortingProcess.QTY_PRODCUT_TOT = dr.GetInt32("QTY_PRODCUT_TOT"); sortingProcess.QTY_ROUTE_TOT = dr.GetInt32("QTY_ROUTE_TOT"); sortingProcess.QTY_CUSTOMER_TOT = dr.GetInt32("QTY_CUSTOMER_TOT"); sortingProcess.QTY_PRODUCT = dr.GetInt32("QTY_PRODUCT"); sortingProcess.QTY_ROUTE = dr.GetInt32("QTY_ROUTE"); sortingProcess.QTY_CUSTOMER = dr.GetInt32("QTY_CUSTOMER"); sortingProcess.CUSTOMER_CODE = dr.GetString("CUSTOMER_CODE"); sortingProcess.CUSTOMER_DESC = dr.GetString("CUSTOMER_DESC"); sortingProcess.ROUTE_CODE = dr.GetString("ROUTE_CODE"); sortingProcess.ROUTE_NAME = dr.GetString("ROUTE_NAME"); sortingProcess.RECEIVE_TIME = dr.GetDateTime("RECEIVE_TIME"); sortingProcess.EFFICIENCY = dr.GetDouble("EFFICIENCY"); sortingProcessList.Add(sortingProcess); } } } } return(sortingProcessList); }
public override void CopyNavigationMenu() { var values = new List<string>(); const string commandText = @" SELECT Id, Name, SystemName, LastModifiedOn, IconURL, Sequence, IconId from NavigationGroups "; using (var ctx = GetMetaDatabaseConnectionManager()) { using (var cmd = new SqlCommand(commandText, ctx.Connection)) { using (var reader = new SafeDataReader(cmd.ExecuteReader())) { while (reader.Read()) { values.Add(string.Format("({0},'{1}','{2}','{3}','{4}',{5},{6})", reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetDateTime(3).ToString("yyyy-MM-dd HH:mm:ss"), reader.GetString(4), reader.GetDouble(5), reader.IsDBNull(6) ? "NULL" : reader.GetInt32(6).ToString())); } } } } var script = string.Format(@" SET IDENTITY_INSERT __NavigationGroup ON MERGE INTO [dbo].[__NavigationGroup] AS TARGET USING ( VALUES {0} ) AS Source([Id], [Name], [SystemName], [LastModifiedOn], [IconURL], [Sequence],[Icon]) ON TARGET.id = Source.Id -- update matched rows WHEN MATCHED THEN UPDATE SET [Name] = Source.[Name], [SystemName] = Source.[SystemName], [LastModifiedOn] = Source.[LastModifiedOn], [IconURL] = Source.[IconURL], [Sequence] = Source.[Sequence], [Icon] = Source.[Icon], [IsRemoved] = 0 -- insert new rows WHEN NOT MATCHED BY TARGET THEN INSERT ([Id], [Name], [SystemName], [LastModifiedOn], [IconURL], [Sequence], [Icon], [IsRemoved]) VALUES ([Id], [Name], [SystemName], [LastModifiedOn], [IconURL], [Sequence], [Icon], 0) -- delete rows that are in the target but not the source WHEN NOT MATCHED BY SOURCE THEN DELETE ; SET IDENTITY_INSERT __NavigationGroup OFF ", string.Join(",", values)); ExecuteSql(script); values.Clear(); const string navItemsCommandText = @" SELECT ni.id, ni.guid, ni.NAME, p.systemname, NULL, ni.lastmodifiedon, sequence, navigationgroupid, ni.[description], c.[Color], ni.IconId FROM [dbo].[navigationitems] ni INNER JOIN publishedprocesses pp ON pp.id = ni.publishedprocessid INNER JOIN processes p ON pp.processid = p.id AND p.IsPublishedCopy = 1 LEFT OUTER JOIN colors as c on c.Id = p.[ColorId] WHERE p.IsRemoved = 0 "; using (var ctx = GetMetaDatabaseConnectionManager()) { using (var cmd = new SqlCommand(navItemsCommandText, ctx.Connection)) { using (var reader = new SafeDataReader(cmd.ExecuteReader())) { while (reader.Read()) { values.Add(string.Format(CultureInfo.InvariantCulture, "({0}, '{1}', '{2}', '{3}', '{4}', '{5}', {6}, {7}, '{8}', {9}, {10})", reader.GetInt32(0), reader.GetGuid(1), Escape(reader.GetString(2)), reader.GetString(3), reader.GetString(4), reader.GetDateTime(5).ToString("yyyy-MM-dd HH:mm:ss"), reader.GetDouble(6), reader.GetInt32(7), Escape(reader.GetString(8)), reader.GetInt64(9), reader.IsDBNull(10) ? "null" : reader.GetInt32(10).ToString())); } } } } if (values.Count == 0) return; script = string.Format(@" SET IDENTITY_INSERT __NavigationItem ON MERGE INTO [dbo].[__NavigationItem] AS TARGET USING ( VALUES {0} ) AS Source([Id], [Guid], [Name], [SystemName], [IconURL], [LastModifiedOn], [Sequence], [NavigationGroupId], [Description], [ProcessColor], [IconId]) ON TARGET.id = Source.Id -- update matched rows WHEN MATCHED THEN UPDATE SET [Guid] = Source.[Guid], [Name] = Source.[Name], [SystemName] = Source.[SystemName], [IconURL] = Source.[IconURL], [LastModifiedOn] = Source.[LastModifiedOn], [NavigationGroup] = Source.[NavigationGroupId], [Description] = Source.[Description], [Sequence] = Source.[Sequence], -- insert new rows [ProcessColor] = Source.[ProcessColor], [Icon] = Source.[Iconid] WHEN NOT MATCHED BY TARGET THEN INSERT ([Id], [Guid], [Name], [SystemName], [IconURL], [LastModifiedOn], [Sequence], [NavigationGroup], [Description], [ProcessColor], [IsRemoved], [Icon]) VALUES ([Id], [Guid], [Name], [SystemName], [IconURL], [LastModifiedOn], [Sequence], [NavigationGroupId], [Description], [ProcessColor], 0, [IconId]) -- delete rows that are in the target but not the source WHEN NOT MATCHED BY SOURCE THEN DELETE ; SET IDENTITY_INSERT __NavigationItem OFF ", string.Join(",", values)); ExecuteSql(script); //------------------------------------------------------ values.Clear(); const string navigationGroupSecurityConfigurationsScript = @" SELECT ngscs.[id], ngscs.[NavigationGroupId], ngscs.[RoleId], ngscs.[CanView] FROM [dbo].[NavigationGroupSecurityConfigurations] ngscs"; using (var ctx = GetMetaDatabaseConnectionManager()) { using (var cmd = new SqlCommand(navigationGroupSecurityConfigurationsScript, ctx.Connection)) { using (var reader = new SafeDataReader(cmd.ExecuteReader())) { while (reader.Read()) { values.Add(string.Format(CultureInfo.InvariantCulture, "({0}, '{1}', '{2}', '{3}')", reader.GetInt32(0),reader.GetInt32(1), reader.GetInt32(2), reader.GetBoolean(3))); } } } } if (values.Count == 0) return; script = string.Format(@"SET IDENTITY_INSERT __NavigationGroupSecurity ON MERGE INTO [dbo].[__NavigationGroupSecurity] AS TARGET USING ( VALUES {0} ) AS Source([Id], [NavigationGroup], [RoleId], [CanView]) ON TARGET.id = Source.Id -- update matched rows WHEN MATCHED THEN UPDATE SET [NavigationGroup] = Source.[NavigationGroup], [RoleId] = Source.[RoleId], [CanView] = Source.[CanView] WHEN NOT MATCHED BY TARGET THEN INSERT ([Id], [NavigationGroup], [RoleId], [CanView], [IsRemoved]) VALUES ([Id], [NavigationGroup], [RoleId], [CanView], 0) -- delete rows that are in the target but not the source WHEN NOT MATCHED BY SOURCE THEN DELETE ; SET IDENTITY_INSERT __NavigationGroupSecurity OFF ", string.Join(",", values)); ExecuteSql(script); //------------------------------------------------------ values.Clear(); const string navigationItemsSecurityConfigurationsScript = @" SELECT ngscs.[id], ngscs.[NavigationItemId], ngscs.[RoleId], ngscs.[CanView] FROM [dbo].[NavigationItemSecurityConfigurations] ngscs"; using (var ctx = GetMetaDatabaseConnectionManager()) { using (var cmd = new SqlCommand(navigationItemsSecurityConfigurationsScript, ctx.Connection)) { using (var reader = new SafeDataReader(cmd.ExecuteReader())) { while (reader.Read()) { values.Add(string.Format(CultureInfo.InvariantCulture, "({0}, '{1}', '{2}', '{3}')", reader.GetInt32(0), reader.GetInt32(1), reader.GetInt32(2), reader.GetBoolean(3))); } } } } if (values.Count == 0) return; script = string.Format(@"SET IDENTITY_INSERT __NavigationItemSecurity ON MERGE INTO [dbo].[__NavigationItemSecurity] AS TARGET USING ( VALUES {0} ) AS Source([Id], [NavigationItem], [RoleId], [CanView]) ON TARGET.id = Source.Id -- update matched rows WHEN MATCHED THEN UPDATE SET [NavigationItem] = Source.[NavigationItem], [RoleId] = Source.[RoleId], [CanView] = Source.[CanView] WHEN NOT MATCHED BY TARGET THEN INSERT ([Id], [NavigationItem], [RoleId], [CanView], [IsRemoved]) VALUES ([Id], [NavigationItem], [RoleId], [CanView], 0) -- delete rows that are in the target but not the source WHEN NOT MATCHED BY SOURCE THEN DELETE ; SET IDENTITY_INSERT __NavigationItemSecurity OFF ", string.Join(",", values)); ExecuteSql(script); }
/// <summary> /// Retrieves reverse cross ref required step. /// </summary> /// <param name="processName">The process name.</param> /// <param name="fieldName">The field name.</param> /// <param name="isPublishedCopy">The is published copy.</param> /// <returns>The <see cref="ReverseCrossRefRequiredStepDto" />.</returns> public ReverseCrossRefRequiredStepDto FetchReverseCrossRefRequiredStep(string processName, string fieldName, bool isPublishedCopy = false) { const string CmdText = @" DECLARE @fieldId AS INT SELECT @fieldId = f.Id FROM [dbo].[Processes] p INNER JOIN [dbo].[Sections] s ON s.ProcessId = p.Id INNER JOIN [dbo].[Fields] f ON f.SectionId = s.Id WHERE p.[SystemName] = @processName AND p.IsRemoved = 0 AND p.IsPublishedCopy = @isPublishedCopy AND f.SystemName = @fieldName; EXEC [dbo].[GetReverseCrossRefRequiredStep] @FieldId = @fieldId; "; using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false)) { using (var cmd = new SqlCommand(CmdText, ctx.Connection)) { cmd.Parameters.AddWithValue("@processName", processName); cmd.Parameters.AddWithValue("@fieldName", fieldName); cmd.Parameters.AddWithValue("@isPublishedCopy", isPublishedCopy); using (var reader = new SafeDataReader(cmd.ExecuteReader())) { if (reader.Read()) { var dto = new ReverseCrossRefRequiredStepDto { Id = reader.GetInt32(0), FieldId = reader.GetInt32(1), ReverseCrossRefProcessId = reader.GetNullableInt(2), CrossRefFieldName = reader.GetString(3), DisplayFieldName = reader.GetString(4), DisplayMultiple = reader.GetBoolean(5) }; if (reader.NextResult()) { while (reader.Read()) { var df = new ReverseCrossReferenceDisplayFieldDto { Id = reader.GetInt32(0), DisplayName = reader.GetString(1), FullPath = reader.GetString(2), Order = reader.GetDouble(3) }; dto.SelectedDisplayFields.Add(df); } } return dto; } } } } return null; }
/// <summary> /// Retrieves numeric required step. /// </summary> /// <param name="processName">The process name.</param> /// <param name="fieldName">The field name.</param> /// <param name="isPublishedCopy">The is published copy.</param> /// <returns>The <see cref="NumericRequiredStepDto" />.</returns> public NumericRequiredStepDto FetchNumericRequiredStep(string processName, string fieldName, bool isPublishedCopy = false) { const string CmdText = @" DECLARE @fieldId AS INT SELECT @fieldId = f.Id FROM [dbo].[Processes] p INNER JOIN [dbo].[Sections] s ON s.ProcessId = p.Id INNER JOIN [dbo].[Fields] f ON f.SectionId = s.Id WHERE p.[SystemName] = @processName AND p.IsRemoved = 0 AND p.IsPublishedCopy = @isPublishedCopy AND f.SystemName = @fieldName; EXEC [dbo].[GetNumericRequiredStep] @FieldId = @fieldId; "; using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false)) { using (var cmd = new SqlCommand(CmdText, ctx.Connection)) { cmd.Parameters.AddWithValue("@processName", processName); cmd.Parameters.AddWithValue("@fieldName", fieldName); cmd.Parameters.AddWithValue("@isPublishedCopy", isPublishedCopy); using (var reader = new SafeDataReader(cmd.ExecuteReader())) { if (reader.Read()) { var dto = new NumericRequiredStepDto { Id = reader.GetInt32(0), NumericType = reader.GetInt32(1), NumberOfDigits = reader.GetInt32(2), Minimum = reader.GetDouble(3), Maximum = reader.GetDouble(4) }; return dto; } } } } return null; }
/// <summary> /// Fetches the group navigation items. /// </summary> /// <param name="connection">The connection.</param> /// <param name="dto">The dto.</param> /// <param name="currentLocalizationId">The current localization identifier.</param> private static void FetchGroupNavigationItems(SqlConnection connection, NavigationGroupEditDto dto, int currentLocalizationId) { const string sql = @" SELECT nv.[Id] ,nv.[Guid] ,ISNULL(pl.ProcessName, p.[Name]) + (CASE WHEN ISNULL(pvl.Name, pv.Name) IS NULL THEN '' ELSE ' - ' + ISNULL(pvl.Name, pv.Name) END) ,p.[SystemName] ,nv.[Sequence] ,nv.[PublishedProcessId] ,nv.[IconURL] ,p.[IconId] ,nv.[ProcessViewGuid] FROM [dbo].[NavigationItems] nv INNER JOIN [dbo].[PublishedProcesses] pp ON nv.[PublishedProcessId] = pp.[Id] INNER JOIN [dbo].[Processes] p ON pp.[ProcessId] = p.[Id] INNER JOIN Processes p2 ON pp.ProcessGuid = p2.[Guid] and p2.[IsPublishedCopy] = 0 LEFT JOIN [dbo].[ProcessViews] pv ON pv.[Guid] = nv.ProcessViewGuid AND pv.ProcessId = p.Id LEFT OUTER JOIN dbo.ProcessLocalizations pl ON p2.Id = pl.ProcessId AND pl.LocalizationId = @locId LEFT OUTER JOIN dbo.ProcessViewLocalizations pvl ON p2.Id = pl.ProcessId AND pvl.LocalizationId = @locId WHERE p.[IsRemoved] = 0 AND p.IsInactive = 0 AND nv.[NavigationGroupId] = @groupId UNION SELECT nv.[Id] ,nv.[Guid] ,nv.[Name] ,NULL ,nv.[Sequence] ,NULL ,nv.[IconURL] ,NULL ,NULL FROM [dbo].[NavigationItems] nv WHERE nv.[SystemName] IS NULL AND nv.[PublishedProcessId] IS NULL AND nv.[NavigationGroupId] = @groupId; "; using (var cmd = new SqlCommand(sql, connection)) { cmd.Parameters.AddWithValue("@groupId", dto.Id); cmd.Parameters.AddWithValue("@locId", currentLocalizationId); using (var reader = new SafeDataReader(cmd.ExecuteReader())) { while (reader.Read()) { var navigationItemDto = new NavigationItemEditDto { Id = reader.GetInt(0), Guid = reader.GetGuid(1), Name = reader.GetString(2), Sequence = reader.GetDouble(4), PublishedProcessId = reader.GetNullableInt(5), IconURL = reader.GetString(6), IconId = reader.GetNullableInt(7), ProcessViewGuid = reader.GetNullableGuid(8) }; //FetchNavigationItemSecurityConfigurations(connection, navigationItemDto); dto.NavigationItems.Add(navigationItemDto); } } foreach (var navigationItemDto in dto.NavigationItems) { FetchNavigationItemSecurityConfigurations(connection, navigationItemDto); } } }
/// <summary> /// Fetches the navigation group edit list. /// </summary> /// <param name="currentLocalizationId">The current localization identifier.</param> /// <returns>IList{NavigationGroupEditDto}.</returns> public IList<NavigationGroupEditDto> FetchNavigationGroupEditList(int currentLocalizationId) { if (currentLocalizationId == 0) currentLocalizationId = GetLocIdByCultureName(Thread.CurrentThread.CurrentUICulture.Name); var result = new List<NavigationGroupEditDto>(); const string sql = @" SELECT [Id] ,[Guid] ,ISNULL(l.[Name], g.[Name]) as Name ,[SystemName] ,[Sequence] ,[IconURL] ,[IconId] FROM [dbo].[NavigationGroups] g left outer join [dbo].[NavigationGroupLocalizations] l on l.NavigationGroupId = g.Id and l.LocalizationId = @locId "; using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false)) { var cn = ctx.Connection; if (cn.State != ConnectionState.Open) { cn.Open(); } using (var cmd = new SqlCommand(sql, cn)) { cmd.Parameters.AddWithValue("@locId", currentLocalizationId); using (var reader = new SafeDataReader(cmd.ExecuteReader())) { while (reader.Read()) { var navigationGroupDto = new NavigationGroupEditDto { Id = reader.GetInt(0), Guid = reader.GetGuid(1), Name = reader.GetString(2), SystemName = reader.GetString(3), Sequence = reader.GetDouble(4), IconURL = reader.GetString(5), IconId = reader.GetNullableInt(6) }; //FetchGroupNavigationItems(cn, navigationGroupDto); //FetchNavigationGroupSecurityConfigurations(cn, navigationGroupDto); result.Add(navigationGroupDto); } } foreach (var navigationGroupDto in result) { FetchGroupNavigationItems(cn, navigationGroupDto, currentLocalizationId); FetchNavigationGroupSecurityConfigurations(cn, navigationGroupDto); } } } return result; }
public static void GetSortingProcessList(object o) { SortingProcessList sortingProcessList = new SortingProcessList(); using (var cn = new MySqlConnection(AppUtility.AppUtil._LocalConnectionString)) { cn.Open(); MySqlTransaction tran = cn.BeginTransaction(); using (var cm = cn.CreateCommand()) { cm.CommandType = CommandType.StoredProcedure; cm.CommandText = "P_SORTLINE_Efficiency"; using (var dr = new Csla.Data.SafeDataReader(cm.ExecuteReader())) { while (dr.Read()) { SortingProcessInfo sortingProcess = new SortingProcessInfo(); sortingProcess.SORTINGTASKNO = dr.GetString("SORTINGTASKNO"); sortingProcess.ORDERDATE = dr.GetString("ORDERDATE"); sortingProcess.PICKLINECODE = dr.GetString("PICKLINECODE"); sortingProcess.PICKLINENAME = dr.GetString("PICKLINENAME"); sortingProcess.QTY_PRODCUT_TOT = dr.GetInt32("QTY_PRODCUT_TOT"); sortingProcess.QTY_ROUTE_TOT = dr.GetInt32("QTY_ROUTE_TOT"); sortingProcess.QTY_CUSTOMER_TOT = dr.GetInt32("QTY_CUSTOMER_TOT"); sortingProcess.QTY_PRODUCT = dr.GetInt32("QTY_PRODUCT"); sortingProcess.QTY_ROUTE = dr.GetInt32("QTY_ROUTE"); sortingProcess.QTY_CUSTOMER = dr.GetInt32("QTY_CUSTOMER"); sortingProcess.CUSTOMER_CODE = dr.GetString("CUSTOMER_CODE"); sortingProcess.CUSTOMER_DESC = dr.GetString("CUSTOMER_DESC"); sortingProcess.ROUTE_CODE = dr.GetString("ROUTE_CODE"); sortingProcess.ROUTE_NAME = dr.GetString("ROUTE_NAME"); sortingProcess.RECEIVE_TIME = DateTime.Now; sortingProcess.EFFICIENCY = dr.GetDouble("EFFICIENCY"); sortingProcess.Progress = dr.GetDouble("Progress"); sortingProcessList.Add(sortingProcess); } } } try { foreach (SortingProcessInfo sortingProcessInfo in sortingProcessList) { if (!sortingProcessInfo.IsExist()) { using (var cm = cn.CreateCommand()) { cm.Transaction = tran; cm.CommandType = CommandType.Text; StringBuilder SQL = new StringBuilder(); SQL.Append("INSERT "); SQL.Append(" INTO t_sortline_process "); SQL.Append(" ( "); SQL.Append( " ID,SORTINGTASKNO,ORDERDATE,PICKLINECODE,PICKLINENAME,QTY_PRODCUT_TOT,QTY_ROUTE_TOT,QTY_CUSTOMER_TOT,QTY_PRODUCT,QTY_ROUTE,QTY_CUSTOMER,CUSTOMER_CODE,CUSTOMER_DESC,ROUTE_CODE,ROUTE_NAME,EFFICIENCY,RECEIVE_TIME,Progress "); SQL.Append(" ) "); SQL.Append(" VALUES "); SQL.Append(" ( "); SQL.Append( " @ID,@SORTINGTASKNO,@SORT_DATE,@SORTLINE_CODE,@SORTLINE_DESC,@QTY_PRODCUT_TOT,@QTY_ROUTE_TOT,@QTY_CUSTOMER_TOT,@QTY_PRODUCT,@QTY_ROUTE,@QTY_CUSTOMER,@CUSTOMER_CODE,@CUSTOMER_DESC,@ROUTE_CODE,@ROUTE_NAME,@EFFICIENCY,@RECEIVE_TIME,@Progress "); SQL.Append(" )"); cm.CommandText = SQL.ToString(); cm.Parameters.AddWithValue("@ID", Guid.NewGuid().ToString()); cm.Parameters.AddWithValue("@SORTINGTASKNO", sortingProcessInfo.SORTINGTASKNO); cm.Parameters.AddWithValue("@SORT_DATE", sortingProcessInfo.ORDERDATE); cm.Parameters.AddWithValue("@SORTLINE_CODE", sortingProcessInfo.PICKLINECODE); cm.Parameters.AddWithValue("@SORTLINE_DESC", sortingProcessInfo.PICKLINENAME); cm.Parameters.AddWithValue("@QTY_PRODCUT_TOT", sortingProcessInfo.QTY_PRODCUT_TOT); cm.Parameters.AddWithValue("@QTY_ROUTE_TOT", sortingProcessInfo.QTY_ROUTE_TOT); cm.Parameters.AddWithValue("@QTY_CUSTOMER_TOT", sortingProcessInfo.QTY_CUSTOMER_TOT); cm.Parameters.AddWithValue("@QTY_PRODUCT", sortingProcessInfo.QTY_PRODUCT); cm.Parameters.AddWithValue("@QTY_ROUTE", sortingProcessInfo.QTY_ROUTE); cm.Parameters.AddWithValue("@QTY_CUSTOMER", sortingProcessInfo.QTY_CUSTOMER); cm.Parameters.AddWithValue("@CUSTOMER_CODE", sortingProcessInfo.CUSTOMER_CODE); cm.Parameters.AddWithValue("@CUSTOMER_DESC", sortingProcessInfo.CUSTOMER_DESC); cm.Parameters.AddWithValue("@ROUTE_CODE", sortingProcessInfo.ROUTE_CODE); cm.Parameters.AddWithValue("@ROUTE_NAME", sortingProcessInfo.ROUTE_NAME); cm.Parameters.AddWithValue("@EFFICIENCY", sortingProcessInfo.EFFICIENCY); cm.Parameters.AddWithValue("@RECEIVE_TIME", sortingProcessInfo.RECEIVE_TIME); cm.Parameters.AddWithValue("@Progress", sortingProcessInfo.Progress); cm.ExecuteNonQuery(); } } else { using (var cm = cn.CreateCommand()) { cm.Transaction = tran; cm.CommandType = CommandType.Text; StringBuilder SQL = new StringBuilder(); if (sortingProcessInfo.PICKLINECODE == o.ToString()) { SQL.Append("UPDATE t_sortline_process "); SQL.Append( " SET PICKLINENAME = @SORTLINE_DESC,QTY_PRODCUT_TOT = @QTY_PRODCUT_TOT,QTY_ROUTE_TOT = @QTY_ROUTE_TOT,QTY_CUSTOMER_TOT = @QTY_CUSTOMER_TOT,QTY_PRODUCT = @QTY_PRODUCT,QTY_ROUTE = @QTY_ROUTE,QTY_CUSTOMER = @QTY_CUSTOMER,CUSTOMER_CODE = @CUSTOMER_CODE,CUSTOMER_DESC = @CUSTOMER_DESC,ROUTE_CODE = @ROUTE_CODE, "); SQL.Append( " ROUTE_NAME = @ROUTE_NAME,RECEIVE_TIME = @RECEIVE_TIME,Progress=@Progress"); SQL.Append( " WHERE SORTINGTASKNO = @SORTINGTASKNO and ORDERDATE = @SORT_DATE and PICKLINECODE = @SORTLINE_CODE"); } else { SQL.Append("UPDATE t_sortline_process "); SQL.Append( " SET PICKLINENAME = @SORTLINE_DESC,QTY_PRODCUT_TOT = @QTY_PRODCUT_TOT,QTY_ROUTE_TOT = @QTY_ROUTE_TOT,QTY_CUSTOMER_TOT = @QTY_CUSTOMER_TOT,QTY_PRODUCT = @QTY_PRODUCT,QTY_ROUTE = @QTY_ROUTE,QTY_CUSTOMER = @QTY_CUSTOMER,CUSTOMER_CODE = @CUSTOMER_CODE,CUSTOMER_DESC = @CUSTOMER_DESC,ROUTE_CODE = @ROUTE_CODE, "); SQL.Append( " ROUTE_NAME = @ROUTE_NAME,Progress=@Progress "); SQL.Append(" WHERE SORTINGTASKNO = @SORTINGTASKNO and ORDERDATE = @SORT_DATE and PICKLINECODE = @SORTLINE_CODE"); } cm.CommandText = SQL.ToString(); cm.Parameters.AddWithValue("@SORTINGTASKNO", sortingProcessInfo.SORTINGTASKNO); cm.Parameters.AddWithValue("@SORT_DATE", sortingProcessInfo.ORDERDATE); cm.Parameters.AddWithValue("@SORTLINE_CODE", sortingProcessInfo.PICKLINECODE); cm.Parameters.AddWithValue("@SORTLINE_DESC", sortingProcessInfo.PICKLINENAME); cm.Parameters.AddWithValue("@QTY_PRODCUT_TOT", sortingProcessInfo.QTY_PRODCUT_TOT); cm.Parameters.AddWithValue("@QTY_ROUTE_TOT", sortingProcessInfo.QTY_ROUTE_TOT); cm.Parameters.AddWithValue("@QTY_CUSTOMER_TOT", sortingProcessInfo.QTY_CUSTOMER_TOT); cm.Parameters.AddWithValue("@QTY_PRODUCT", sortingProcessInfo.QTY_PRODUCT); cm.Parameters.AddWithValue("@QTY_ROUTE", sortingProcessInfo.QTY_ROUTE); cm.Parameters.AddWithValue("@QTY_CUSTOMER", sortingProcessInfo.QTY_CUSTOMER); cm.Parameters.AddWithValue("@CUSTOMER_CODE", sortingProcessInfo.CUSTOMER_CODE); cm.Parameters.AddWithValue("@CUSTOMER_DESC", sortingProcessInfo.CUSTOMER_DESC); cm.Parameters.AddWithValue("@ROUTE_CODE", sortingProcessInfo.ROUTE_CODE); cm.Parameters.AddWithValue("@ROUTE_NAME", sortingProcessInfo.ROUTE_NAME); cm.Parameters.AddWithValue("@Progress", sortingProcessInfo.Progress); if (sortingProcessInfo.PICKLINECODE == o.ToString()) { cm.Parameters.AddWithValue("@RECEIVE_TIME", sortingProcessInfo.RECEIVE_TIME); } cm.ExecuteNonQuery(); } } } tran.Commit(); } catch (Exception) { tran.Rollback(); throw; } } //return sortingProcessList; }
/// <summary> /// Reads process view fields. /// </summary> /// <param name="process">The process.</param> /// <param name="sr">The reader.</param> private static void ReadProcessViewFields(ProcessEditDto process, SafeDataReader sr) { sr.NextResult(); int? sectionId = null; ProcessViewSectionEditDto section = null; while (sr.Read()) { var fieldDto = new ProcessViewFieldEditDto { Id = sr.GetInt32(0), SectionId = sr.GetInt32(1), Guid = sr.GetGuid(2), TemplateFieldGuid = sr.GetGuid(3), FieldSystemName = sr.GetString(4), DisplayOrder = sr.GetDouble(5), DisplayType = sr.GetString(6), IconId = sr.GetNullableInt(7), CustomConfig = sr.GetString(8) }; if (fieldDto.SectionId != sectionId) { section = process.ViewList.SelectMany(v => v.SectionList).First(s => s.Id == fieldDto.SectionId); sectionId = fieldDto.SectionId; } section.FieldList.Add(fieldDto); } }
/// <summary> /// The read KPIs. /// </summary> /// <param name="process">The process.</param> /// <param name="sr">The reader.</param> private static void ReadKpis(ProcessEditDto process, SafeDataReader sr) { sr.NextResult(); while (sr.Read()) { process.Kpis.Add(new ProcessKpiEditDto { Id = sr.GetInt32(0), ProcessId = sr.GetInt32(1), MetricGuid = sr.GetString(2), LastModifiedOn = sr.GetDateTime(3), GuidId = sr.GetString(4), Name = sr.GetString(5), TargetValue = sr.GetDouble(6), FavorableDirection = sr.GetString(7), GreenIconUrl = sr.GetString(8), GreenIconId = sr.GetInt32(9, null), GreenValue = sr.GetDouble(10), YellowIconUrl = sr.GetString(11), YellowIconId = sr.GetInt32(12, null), YellowValue = sr.GetDouble(13), RedIconUrl = sr.GetString(14), RedIconId = sr.GetInt32(15, null), FilterGuid = sr.GetString(16, null), FilterDefinition = sr.GetString(17, null), Documentation = sr.GetString(18), }); } }
/// <summary> /// Reads fields. /// </summary> /// <param name="process">The process.</param> /// <param name="sr">The reader.</param> private void ReadFields(ProcessEditDto process, SafeDataReader sr) { sr.NextResult(); int? sectionId = null; SectionDto section = null; var times = new List<double>(); while (sr.Read()) { var start = DateTime.Now; var fieldDto = new FieldDto { Id = sr.GetInt32(0), Name = sr.GetSafeString(1, string.Empty).Replace(@"""", "''"), FieldTypeId = sr.GetInt32(2), SectionId = sr.GetInt32(3), Width = sr.GetDouble(4), RowSpan = sr.GetInt(5), ShowInList = sr.GetBoolean(6), IncludeInFilter = sr.GetBoolean(7), HideFromDetails = sr.GetBoolean(8), SystemName = sr.GetString(9), Position = sr.GetInt32(10), CopyFieldValueOnCopyItem = sr.GetBool(11), DeepCopy = sr.GetBool(12), Guid = sr.GetGuid(13), SearchPosition = sr.GetInt(14), SearchWidth = sr.GetInt(15), IsBase = sr.GetBoolean(16), UseInGlobalSearch = sr.GetBoolean(19), PublishedCopyId = sr.GetNullableInt(21), AllowLocalizedData = sr.GetBoolean("AllowLocalizedData") }; if (fieldDto.SectionId != sectionId || section == null) { section = process.Sections.First(s => s.Id == fieldDto.SectionId); sectionId = fieldDto.SectionId; } fieldDto.FieldTypeInfo = new FieldTypeDto { Id = fieldDto.FieldTypeId, Name = sr.GetString(17), DataType = sr.GetString(18), CanBeRequired = sr.GetBoolean(20) }; section.FieldList.Add(fieldDto); times.Add((DateTime.Now - start).TotalMilliseconds); } Profiler.Profile(() => this.ReadFieldEditors(process, sr)); }
/// <summary> /// Reads sections. /// </summary> /// <param name="process">The process.</param> /// <param name="reader">The reader.</param> private void ReadSections(ProcessEditDto process, SafeDataReader reader) { reader.NextResult(); while (reader.Read()) { var sectionDto = new SectionDto { Id = reader.GetInt32(0), Name = reader.GetString(1), Guid = reader.GetGuid(2), PaperclipsEnabled = reader.GetBoolean(3), IsBase = reader.GetBoolean(4), Position = reader.GetDouble(6) }; process.Sections.Add(sectionDto); } Profiler.Profile(() => this.ReadFields(process, reader)); }
/// <summary> /// The read states. /// </summary> /// <param name="process">The process.</param> /// <param name="sr">The reader.</param> private static void ReadStates(ProcessEditDto process, SafeDataReader sr) { sr.NextResult(); while (sr.Read()) { var stateDto = new StateDto { Id = sr.GetInt32(0), Name = sr.GetString(1), Documentation = sr.GetString(2), DesignPositionLeft = sr.GetDouble(3), DesignPositionTop = sr.GetDouble(4), DesignDecisionPositionLeft = sr.GetDouble(5), DesignDecisionPositionTop = sr.GetDouble(6), Guid = sr.GetGuid(7), MetaId = sr.GetInt32(8), Color = sr.GetInt64("Color") }; process.States.Add(stateDto); } ReadConnectors(process, sr); }