public IList<FieldInfoDto> FetchFieldList(int processId, bool includeInheritedFields, bool includeSystemFields) { var result = new List<FieldInfoDto>(); var cmdText = new StringBuilder(@" SELECT DISTINCT f.[Id], f.[Name], f.[SystemName], f.[FieldTypeId], p.[BaseProcessId], p.[SystemName], CASE WHEN f.[FieldTypeId] = 6 AND ISNULL(crrs.AllowMultiple, 0) = 1 THEN 'MultiReference' WHEN f.[FieldTypeId] = 9 AND ISNULL(rcrrs.DisplayMultiple, 0) = 1 THEN 'ReverseMultiReference' WHEN f.[FieldTypeId] = 1 AND ISNULL(nr.[NumberOfDigits], 0) = 0 AND ISNULL(nr.[Minimum], 0) >= @intMin AND ISNULL(nr.[Maximum], 0) <= @intMax AND ISNULL(nr.[NumericType], 0) = 0 THEN 'Integer' ELSE ft.[DataType] END, f.Guid, sto.[UseRichText], CASE WHEN LEN(ISNULL(CAST(es.[CalculatedExpression] AS NVARCHAR(MAX)), '')) > 0 THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END AS 'IsCalculated', (SELECT CAST( CASE WHEN EXISTS ( SELECT * from [dbo].[RequiredRuleConfigFields] rrcf INNER JOIN [dbo].[RequiredRuleConfigs] rrc on rrc.[Id] = rrcf.[RequiredConfigId] WHERE rrc.[ProcessId] = p.[Id] and rrcf.[FieldGuid] = f.[Guid]) THEN 1 ELSE 0 END AS BIT)) as [IsRequired], CAST(0 AS BIT) AS [IsBuiltIn] ,f.[IncludeInFilter] ,(SELECT p.[SystemName] FROM [dbo].[Processes] p INNER JOIN [dbo].[PublishedProcesses] pp ON pp.[ProcessId] = p.[Id] WHERE pp.[Id] = CASE WHEN f.[FieldTypeId] = 6 THEN crrs.[CrossRefProcessId] WHEN f.[FieldTypeId] = 9 THEN rcrrs.[ReverseCrossRefProcessId] WHEN f.[FieldTypeId] = 24 THEN tvrs.[ReverseCrossRefProcessId] ELSE NULL END ) AS [ReferencedProcessSystemName] ,drs.[DateTimeFormat] AS [DateTimeFormat] ,CASE WHEN f.[FieldTypeId] = 9 THEN rcrrs.[CrossRefFieldName] WHEN f.[FieldTypeId] = 24 THEN tvrs.[CrossRefFieldName] END AS [CrossReferenceFieldName] ,CASE WHEN f.[FieldTypeId] = 6 THEN crrs.[DisplayFieldName] WHEN f.[FieldTypeId] = 9 THEN rcrrs.[DisplayFieldName] WHEN f.[FieldTypeId] = 24 THEN tvrs.[DisplayFieldName] ELSE NULL END AS [DisplayFieldName] FROM [dbo].[Fields] f INNER JOIN [dbo].[Sections] s ON f.[SectionId] = s.[Id] INNER JOIN [dbo].[Processes] p ON s.[ProcessId] = p.[Id] INNER JOIN [dbo].[PublishedProcesses] pp on p.Id = pp.[ProcessId] INNER JOIN [dbo].[FieldTypes] ft ON ft.[Id] = f.[FieldTypeId] LEFT JOIN [dbo].[CrossRefRequredFieldStep] crrs ON crrs.[FieldId] = f.[Id] AND f.[FieldTypeId] = 6 LEFT JOIN [dbo].[ReverseCrossRefRequiredFieldStep] rcrrs ON rcrrs.[FieldId] = f.[Id] AND f.[FieldTypeId] = 9 LEFT JOIN [dbo].[stepTextOptions] sto ON sto.[FieldId] = f.[Id] LEFT JOIN [dbo].[stepExpressions] es ON es.[FieldId] = f.[Id] LEFT JOIN [dbo].[NumericRequiredFieldStep] nr ON nr.[FieldId] = f.[Id] LEFT JOIN [dbo].[TreeViewRequiredFieldStep] tvrs ON tvrs.[FieldId] = f.[Id] LEFT JOIN [dbo].[stepDateRequiredSettings] drs ON drs.[FieldId] = f.[Id] WHERE pp.[ProcessId] = @processId AND f.[IsRemoved] = 0 UNION SELECT 0 [Id], 'Id' Name, 'Id' SystemName, (SELECT [Id] FROM FieldTypes WHERE [DataType] = 'Numeric') FieldTypeId, NULL BaseProcessId, (SELECT SystemName FROM Processes p WHERE p.[Id] = @processId) ProcessSystemName, 'Integer' DataType, '00000000-0000-0000-0000-000000000000' [Guid], CAST(0 AS BIT) UseRichText, CAST(0 AS BIT) IsCalculated, CAST(0 AS BIT) IsRequired, CAST(1 AS BIT) AS [IsBuiltIn] ,CAST(1 AS BIT) AS [IncludeInFilter] ,NULL AS [ReferencedProcessSystemName] ,NULL AS [DateTimeFormat] ,NULL AS [CrossReferenceFieldName] ,NULL AS [DisplayFieldName] "); if (includeSystemFields) { cmdText.AppendLine().Append("UNION").AppendLine().Append(GetSystemFieldsSelect()); } cmdText.AppendLine().Append(@" SELECT pp.ProcessId FROM Processes p INNER JOIN PublishedProcesses pp ON pp.Id = p.BaseProcessId INNER JOIN Processes p2 ON p2.Id = pp.ProcessId AND p2.IsRemoved = 0 WHERE p.id = @processId"); using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false)) { var cn = ctx.Connection; using (var cmd = new SqlCommand(cmdText.ToString(), cn)) { cmd.Parameters.AddWithValue("@processId", processId); cmd.Parameters.AddWithValue("@intMin", int.MinValue); cmd.Parameters.AddWithValue("@intMax", int.MaxValue); using (var reader = new SafeDataReader(cmd.ExecuteReader())) { while (reader.Read()) { result.Add(ReadFieldInfo(reader)); } if (includeInheritedFields && reader.NextResult() && reader.Read()) { var baseProcessId = reader.GetInt32(0); result.AddRange(this.FetchFieldList(baseProcessId, true, includeSystemFields)); } } } } return result.Distinct(new FieldInfoDtoEqualityComparer(x => x.SystemName)).ToList(); }
public void SetUserAuthInfo(User_info userinfo) { //设置用户分组 userinfo.UserGroup = Role_groupBLL.Current.CacheAllDataList().Where(item => userinfo.UsersGroupsList.Contains(item.GroupID)).ToList(); //调用用户资源 //先获取去重复的资源ID列表 var resids = new List<int>(); userinfo.UserGroup.ForEach(item => resids.AddRange(item.Auth_ResourceList)); resids = resids.Distinct().ToList(); userinfo.UserResource = Role_resourceBLL.Current.CacheAllDataList().Where(item => resids.Contains(item.ResourceID)).OrderByDescending(item => item.Sort).ToList(); //设置用户actions var actionIDs = new List<int>(); userinfo.UserGroup.ForEach(item => actionIDs.AddRange(item.Auth_ActionList)); actionIDs = actionIDs.Distinct().ToList(); userinfo.UserAction = Role_actionBLL.Current.CacheAllDataList().Where(item => actionIDs.Contains(item.ActionID)).OrderByDescending(item => item.Sort).ToList(); }
public ActionResult SalesByCountryItem(DateTime date) { if (!GlobalUtilities.VerifySqlTableExists(GlobalSettings.Exigo.Api.Sql.ConnectionStrings.SqlReporting, "Custom_DailyItemSalesbyCountry")) { return new JsonNetResult(new { success = false, error = "Report not configured" }); } ViewBag.Date = date; ViewBag.BeginningOfMonthDate = new DateTime(date.Year, date.Month, 1); ViewBag.BeginningOfYearDate = new DateTime(date.Year, 1, 1); var webCategoryItems = new List<WebCategoryItem>(); var itemSales = new List<ItemSalesRecord>(); var itemRefunds = new List<ItemSalesRecord>(); using (var context = Exigo.Sql()) { var data = context.QueryMultiple(@" set transaction isolation level read uncommitted -- Categories select Category = wc.WebCategoryDescription, i.ItemCode from WebCategoryItems wci inner join Items i on i.ItemID = wci.ItemID inner join WebCategories wc on wc.WebCategoryID = wci.WebCategoryID and wc.WebID = wci.WebID where wci.WebID = 1 and wc.ParentID = 77 order by wc.SortOrder, wci.SortOrder -- Sales Select i.ItemDescription AS ItemDescription, i.ItemCode AS ItemCode, Country, COALESCE(QuantityPerDay, 0) as QuantityPerDay, COALESCE(AmountPerDay, 0) as AmountPerDay, COALESCE(TaxPerDay, 0) as TaxPerDay, COALESCE(TotalPerDay, 0) as TotalPerDay, COALESCE(QuantityPerMonth, 0) as QuantityPerMonth, COALESCE(AmountPerMonth, 0) as AmountPerMonth, COALESCE(TaxPerMonth, 0) as TaxPerMonth, COALESCE(TotalPerMonth, 0) as TotalPerMonth, COALESCE(QuantityPerYear, 0) as QuantityPerYear, COALESCE(AmountPerYear, 0) as AmountPerYear, COALESCE(TaxPerYear, 0) as TaxPerYear, COALESCE(TotalPerYear, 0) as TotalPerYear, COALESCE(QuantityPerLastYear, 0) as QuantityPerLastYear, COALESCE(AmountPerLastYear, 0) as AmountPerLastYear, COALESCE(TaxPerLastYear, 0) as TaxPerLastYear, COALESCE(TotalPerLastYear, 0) as TotalPerLastYear from items i inner join (select itemid, Country, sum(case when SalesDate = @Date then Quantity else 0 end) as Quantityperday, sum(case when SalesDate = @Date then pricetotal else 0 end) as amountperday, sum(case when SalesDate = @Date then TaxTotal else 0 end) as Taxperday, sum(case when SalesDate = @Date then TaxTotal + PriceTotal else 0 end) as Totalperday, sum(case when month(SalesDate) = month(@Date) then Quantity else 0 end) as QuantityperMonth, sum(case when month(SalesDate) = month(@Date) then pricetotal else 0 end) as amountperMonth, sum(case when month(SalesDate) = month(@Date) then TaxTotal else 0 end) as TaxperMonth, sum(case when month(SalesDate) = month(@Date) then TaxTotal + PriceTotal else 0 end) as TotalperMonth, sum(case when year(SalesDate) = year(@Date) then Quantity else 0 end) as QuantityperYear, sum(case when year(SalesDate) = year(@Date) then pricetotal else 0 end) as amountperYear, sum(case when year(SalesDate) = year(@Date) then TaxTotal else 0 end) as TaxperYear, sum(case when year(SalesDate) = year(@Date) then TaxTotal + PriceTotal else 0 end) as TotalperYear, sum(case when year(SalesDate) = year(@Date) - 1 then Quantity else 0 end) as QuantityperLastYear, sum(case when year(SalesDate) = year(@Date) - 1 then pricetotal else 0 end) as amountperLastYear, sum(case when year(SalesDate) = year(@Date) - 1 then TaxTotal else 0 end) as TaxperLastYear, sum(case when year(SalesDate) = year(@Date) - 1 then TaxTotal + PriceTotal else 0 end) as TotalperLastYear from Custom_DailyItemSalesbyCountry where year(SalesDate) = year(@date) and Isreturn = 0 group by itemid , Country ) d on d.itemid = i.itemid order by i.ItemDescription -- Returns Select i.ItemDescription AS ItemDescription, i.ItemCode AS ItemCode, Country, COALESCE(QuantityPerDay, 0) as QuantityPerDay, COALESCE(AmountPerDay, 0) as AmountPerDay, COALESCE(TaxPerDay, 0) as TaxPerDay, COALESCE(TotalPerDay, 0) as TotalPerDay, COALESCE(QuantityPerMonth, 0) as QuantityPerMonth, COALESCE(AmountPerMonth, 0) as AmountPerMonth, COALESCE(TaxPerMonth, 0) as TaxPerMonth, COALESCE(TotalPerMonth, 0) as TotalPerMonth, COALESCE(QuantityPerYear, 0) as QuantityPerYear, COALESCE(AmountPerYear, 0) as AmountPerYear, COALESCE(TaxPerYear, 0) as TaxPerYear, COALESCE(TotalPerYear, 0) as TotalPerYear, COALESCE(QuantityPerLastYear, 0) as QuantityPerLastYear, COALESCE(AmountPerLastYear, 0) as AmountPerLastYear, COALESCE(TaxPerLastYear, 0) as TaxPerLastYear, COALESCE(TotalPerLastYear, 0) as TotalPerLastYear from items i inner join (select itemid, Country, sum(case when SalesDate = @Date then Quantity else 0 end) as Quantityperday, sum(case when SalesDate = @Date then pricetotal else 0 end) as amountperday, sum(case when SalesDate = @Date then TaxTotal else 0 end) as Taxperday, sum(case when SalesDate = @Date then TaxTotal + PriceTotal else 0 end) as Totalperday, sum(case when month(SalesDate) = month(@Date) then Quantity else 0 end) as QuantityperMonth, sum(case when month(SalesDate) = month(@Date) then pricetotal else 0 end) as amountperMonth, sum(case when month(SalesDate) = month(@Date) then TaxTotal else 0 end) as TaxperMonth, sum(case when month(SalesDate) = month(@Date) then TaxTotal + PriceTotal else 0 end) as TotalperMonth, sum(case when year(SalesDate) = year(@Date) then Quantity else 0 end) as QuantityperYear, sum(case when year(SalesDate) = year(@Date) then pricetotal else 0 end) as amountperYear, sum(case when year(SalesDate) = year(@Date) then TaxTotal else 0 end) as TaxperYear, sum(case when year(SalesDate) = year(@Date) then TaxTotal + PriceTotal else 0 end) as TotalperYear, sum(case when year(SalesDate) = year(@Date) - 1 then Quantity else 0 end) as QuantityperLastYear, sum(case when year(SalesDate) = year(@Date) - 1 then pricetotal else 0 end) as amountperLastYear, sum(case when year(SalesDate) = year(@Date) - 1 then TaxTotal else 0 end) as TaxperLastYear, sum(case when year(SalesDate) = year(@Date) - 1 then TaxTotal + PriceTotal else 0 end) as TotalperLastYear from Custom_DailyItemSalesbyCountry where year(SalesDate) = year(@date) and Isreturn = 1 group by itemid , Country ) d on d.itemid = i.itemid order by i.ItemDescription ", new { Date = date }); webCategoryItems = data.Read<WebCategoryItem>().ToList(); itemSales = data.Read<ItemSalesRecord>().ToList(); itemRefunds = data.Read<ItemSalesRecord>().ToList(); } // Create the model var model = new List<ItemSalesByCountryCollection>(); // Arrange the items by country and category var countries = new List<string>(); countries.AddRange(itemSales.Select(c => c.Country).ToList()); countries.AddRange(itemRefunds.Select(c => c.Country).ToList()); countries = countries.Distinct().ToList(); foreach (var country in countries) { var salesInCountry = itemSales.Where(c => c.Country == country).ToList(); var refundsInCountry = itemRefunds.Where(c => c.Country == country).ToList(); // Create the country collection var countryItemsCollection = new ItemSalesByCountryCollection(); countryItemsCollection.Country = country; // Arrange the items by categories var distinctWebCategories = webCategoryItems.Select(c => c.Category).Distinct().ToList(); foreach (var category in distinctWebCategories) { var collection = new ItemSalesByCategoryCollection(); collection.Category = category; var salesItemCodesInCategory = webCategoryItems.Where(c => c.Category == category).Select(c => c.ItemCode).ToList(); var salesItemSalesInCategory = salesInCountry.Where(c => salesItemCodesInCategory.Contains(c.ItemCode)).ToList(); foreach (var record in salesItemSalesInCategory) { collection.Sales.Add(record); } var refundsItemCodesInCategory = webCategoryItems.Where(c => c.Category == category).Select(c => c.ItemCode).ToList(); var refundsItemrefundsInCategory = refundsInCountry.Where(c => refundsItemCodesInCategory.Contains(c.ItemCode)).ToList(); foreach (var record in refundsItemrefundsInCategory) { collection.Refunds.Add(record); } countryItemsCollection.Categories.Add(collection); } // Create the "All" category var masterCollection = new ItemSalesByCategoryCollection(); masterCollection.Category = "All"; foreach (var record in salesInCountry) { masterCollection.Sales.Add(record); } foreach (var record in refundsInCountry) { masterCollection.Refunds.Add(record); } countryItemsCollection.Categories.Add(masterCollection); model.Add(countryItemsCollection); } if (Request.IsAjaxRequest()) return PartialView("_SalesByCountryItemReport", model); else return View("_SalesByCountryItemReport", model); }
/// <summary> /// 從服務器下載近段時間的工程數據到本地 /// </summary> /// <param name="machineID">機台編號</param> /// <param name="days">days日內</param> /// <returns></returns> public bool LoadData(string machineID, int days) { try { mLoger.Info("run DataManager.LoadData"); ShowMessage("同步數據中..."); try { if (!ToPPC(machineID)) { return false; } IList<ShiftInfo_sifo_Info> shiftInfo_ser = new List<ShiftInfo_sifo_Info>(); List<ScheduleProjList_swl_Info> swl_ser = new List<ScheduleProjList_swl_Info>(); List<PrintProject_ppj_Info> ppj_ser = new List<PrintProject_ppj_Info>(); List<ShiftProjList_spl_Info> spl_ser = new List<ShiftProjList_spl_Info>(); List<ProjectStopRecord_psrd_Info> psrd_ser = new List<ProjectStopRecord_psrd_Info>(); List<ProjectStopReason_pjsr_Info> pjsr_ser = new List<ProjectStopReason_pjsr_Info>(); List<ProjectQCRecord_pqc_Info> pqc_ser = new List<ProjectQCRecord_pqc_Info>(); List<ProjectQCProblem_pqcp_Info> pqcp_ser = new List<ProjectQCProblem_pqcp_Info>(); #region Load data using (DBContext server = DBContext.Begin(NHConfigFactory.DBConfig.HBIMSN)) { shiftInfo_ser = server.CurrentSession.QueryOver<ShiftInfo_sifo_Info>().And(d => d.sifo_dProdDate >= DateTime.Now.AddDays(-days)).And(d => d.sifo_cMachineNO == machineID).List(); foreach (var shift in shiftInfo_ser) { IList<ShiftProjList_spl_Info> spls = server.CurrentSession.QueryOver<ShiftProjList_spl_Info>().And(d => d.spl_SIFOID == shift.sifo_RecordID).List(); spl_ser.AddRange(spls); foreach (var spl in spls) { IList<PrintProject_ppj_Info> ppjs = server.CurrentSession.QueryOver<PrintProject_ppj_Info>().And(d => d.ppj_RecordID == spl.spl_PPJID).List(); ppj_ser.AddRange(ppjs); foreach (var ppj in ppjs) { IList<ScheduleProjList_swl_Info> swls = server.CurrentSession.QueryOver<ScheduleProjList_swl_Info>().And(d => d.swl_RecordID == ppj.ppj_SWLID).List(); swl_ser.AddRange(swls); } IList<ProjectStopRecord_psrd_Info> psrds = server.CurrentSession.QueryOver<ProjectStopRecord_psrd_Info>().And(d => d.psrd_SPLID == spl.spl_PPJID).List(); psrd_ser.AddRange(psrds); foreach (var psrd in psrds) { IList<ProjectStopReason_pjsr_Info> pjsrs = server.CurrentSession.QueryOver<ProjectStopReason_pjsr_Info>().And(d => d.pjsr_PSRDID == psrd.psrd_RecordID).List(); pjsr_ser.AddRange(pjsrs); } IList<ProjectQCRecord_pqc_Info> pqcs = server.CurrentSession.QueryOver<ProjectQCRecord_pqc_Info>().And(d => d.pqc_RecordID == spl.spl_PPJID).List(); pqc_ser.AddRange(pqcs); foreach (var pqc in pqcs) { IList<ProjectQCProblem_pqcp_Info> pqcps = server.CurrentSession.QueryOver<ProjectQCProblem_pqcp_Info>().And(d => d.pqcp_PQCID == pqc.pqc_RecordID).List(); pqcp_ser.AddRange(pqcps); } } } } #endregion #region SaveData using (DBContext local = DBContext.Begin(NHConfigFactory.DBConfig.PPC)) { foreach (var item in shiftInfo_ser) { local.CurrentSession.Save(item); } swl_ser = swl_ser.Distinct().ToList(); foreach (var item in swl_ser) { local.CurrentSession.SaveOrUpdate(item); } ppj_ser = ppj_ser.Distinct().ToList(); foreach (var item in ppj_ser) { local.CurrentSession.Save(item); } foreach (var item in spl_ser) { local.CurrentSession.Save(item); } foreach (var item in psrd_ser) { local.CurrentSession.Save(item); } foreach (var item in pjsr_ser) { local.CurrentSession.Save(item); } foreach (var item in pqc_ser) { local.CurrentSession.Save(item); } foreach (var item in pqcp_ser) { local.CurrentSession.Save(item); } } #endregion } catch (Exception ex) { mLoger.Error("run DataManager.LoadData", ex); return false; } ShowMessage("同步資料成功。"); return true; } catch (Exception ex) { ShowMessage("同步資料出現異常:" + ex.Message); return false; // throw ex; } }
/// <summary> /// /// </summary> /// <param name="dids"></param> /// <param name="gids"></param> /// <param name="pageSize"></param> /// <param name="pageIndex"></param> /// <param name="rowCount"></param> /// <returns></returns> protected List<P_PersonInfo> GetPersonListByGroupDepartment(string dids, string gids,out int rowCount, int pageSize=-1,int pageIndex=-1) { List<int> list_dids = new List<int>(); List<int> list_gids = new List<int>(); if (dids != "") { var list_dids_temp = (from g in dids.Split(',') where g != "" select g).ToList(); list_dids_temp.ForEach(g => list_dids.Add(int.Parse(g))); } if (gids != "") { var list_gids_temp = (from g in gids.Split(',') where g != "" select g).ToList(); list_gids_temp.ForEach(g => list_gids.Add(int.Parse(g))); } //2 根据department以及group的id查询其对应的Person对象集合 List<P_PersonInfo> list_person = new List<P_PersonInfo>(); var list_department = departmentBLL.GetListByIds(list_dids); list_department.ForEach(d => list_person.AddRange(d.P_PersonInfo)); var list_group = groupBLL.GetListByIds(list_gids); list_group.ForEach(g => list_person.AddRange(g.P_PersonInfo)); //3 将联系人集合去重 list_person = list_person.Distinct(new P_PersonEqualCompare()).ToList().Select(p => p.ToMiddleModel()).ToList(); list_person = list_person.OrderByDescending(a => a.isVIP).ToList(); rowCount = list_person.Count(); if (pageIndex != -1 && pageSize != -1) { //分页 list_person = list_person.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList(); } return list_person; }
private void Search() { Playlist1.UpdateListToBase(); _CurrentSong = -1; if (textboxSearch.Text != "") { List<Song> SongList = new List<Song>((List<Song>)Playlist1.SearchSong(textboxSearch.Text.ToLower())); SongList.AddRange(VKAPI1.SearchAudio(textboxSearch.Text.ToLower(),_CurrentUser.AccessToken)); SongList = SongList.Distinct(new SongComparer()).ToList<Song>(); // if (Playlist1.SearchSong(textboxSearch.Text.ToLower()).Count > 0) SongList[Playlist1.SearchSong(textboxSearch.Text.ToLower()).Count-1].BorderBrush = (Brush)new BrushConverter().ConvertFrom("#F59184"); Playlist1.UpdateList(new ObservableCollection<Song>(SongList)); } RenderPlaylist(Playlist1.GetList()); BorderPaintOff(); Playlist1.LostSort(); }