private void CreateBudget() { var cwCreateBjt = new CwCreateUpdateBudget(api); #if !SILVERLIGHT cwCreateBjt.DialogTableId = 2000000074; #endif cwCreateBjt.Closed += async delegate { if (cwCreateBjt.DialogResult == true) { int budgetMethod = 0; DateTime dFromDate = CwCreateUpdateBudget.FromDate; DateTime dToDate = CwCreateUpdateBudget.ToDate; string dEmplNumber = CwCreateUpdateBudget.Employee; string dProjectNumber = CwCreateUpdateBudget.Project; string dBudgetGroup = CwCreateUpdateBudget.Group; string dBudgetComment = CwCreateUpdateBudget.Comment; dBudgetComment = string.IsNullOrEmpty(dBudgetComment) ? Uniconta.ClientTools.Localization.lookup("Budget") : dBudgetComment; // if (string.IsNullOrEmpty(CwCreateUpdateBudget.Group)) { UnicontaMessageBox.Show(FieldCannotBeEmpty(Uniconta.ClientTools.Localization.lookup("BudgetGroup")), Uniconta.ClientTools.Localization.lookup("Warning"), MessageBoxButton.OK); return; } if (dFromDate == DateTime.MinValue) { UnicontaMessageBox.Show(FieldCannotBeEmpty(Uniconta.ClientTools.Localization.lookup("FromDate")), Uniconta.ClientTools.Localization.lookup("Warning"), MessageBoxButton.OK); return; } if (dToDate == DateTime.MinValue) { UnicontaMessageBox.Show(FieldCannotBeEmpty(Uniconta.ClientTools.Localization.lookup("ToDate")), Uniconta.ClientTools.Localization.lookup("Warning"), MessageBoxButton.OK); return; } ErrorCodes res; var master = new List <UnicontaBaseEntity>(); //Delete Budget Lines >> ProjectBudgetGroup budgetGrp = budgetGroupCache.Get(dBudgetGroup); if (budgetGrp._Blocked) { UnicontaMessageBox.Show(string.Format(Uniconta.ClientTools.Localization.lookup("IsBlockedOBJ"), Uniconta.ClientTools.Localization.lookup("Budget")), Uniconta.ClientTools.Localization.lookup("Warning"), MessageBoxButton.OK); return; } busyIndicator.IsBusy = true; master.Add(budgetGrp); if (!string.IsNullOrEmpty(dEmplNumber)) { master.Add(employeeCache.Get(dEmplNumber)); } if (!string.IsNullOrEmpty(dProjectNumber)) { master.Add(projectCache.Get(dProjectNumber)); } List <PropValuePair> pairLine = new List <PropValuePair>(); pairLine.Add(PropValuePair.GenereteWhereElements(nameof(ProjectBudgetLine._Date), typeof(DateTime), String.Format("{0:d}..{1:d}", dFromDate, dToDate))); var projBudgetLineDelete = await api.Query <ProjectBudgetLine>(master, pairLine); if (projBudgetLineDelete.Length != 0) { res = await api.Delete(projBudgetLineDelete); } //Delete Budget Lines << List <PropValuePair> pairBudget = new List <PropValuePair>(); pairBudget.Add(PropValuePair.GenereteWhereElements(nameof(ProjectBudget._Group), typeof(string), dBudgetGroup)); var projBudgetLst = await api.Query <ProjectBudget>(pairBudget); var dictProjBudget = new Dictionary <string, ProjectBudget>(); foreach (ProjectBudget rec in projBudgetLst) { ProjectBudget projBudget; bool hasValue = dictProjBudget.TryGetValue(string.Concat(rec._Project, dBudgetGroup), out projBudget); if (!hasValue) { dictProjBudget.Add(string.Concat(rec._Project, dBudgetGroup), rec); } } List <PropValuePair> filterTrans = new List <PropValuePair>(); filterTrans.Add(PropValuePair.GenereteWhereElements(nameof(ProjectTrans._Date), typeof(DateTime), String.Format("{0:d}..{1:d}", dFromDate.AddYears(-1), dToDate.AddYears(-1)))); var transTask = api.Query(new ProjectTransPivotClient(), master, filterTrans); var projTransLst = await transTask; var projTransLstEntity = new List <ProjectTransPivotClient>(); //string lastProj = null; string lastPrCat = null; string lastPayrollCat = null; //Uniconta.DataModel.ProjectGroup projGrp = null; Uniconta.DataModel.PrCategory prCat = null; Uniconta.DataModel.EmpPayrollCategory payrollCat = null; foreach (var x in projTransLst) { //if (lastProj != x._Project) //{ // lastProj = x._Project; // var proj = x.ProjectRef; // projGrp = projGroupCache.Get(proj._Group); //} if (lastPrCat != x._PrCategory) { lastPrCat = x._PrCategory; prCat = x.CategoryRef; } if (lastPayrollCat != x._PayrollCategory) { lastPayrollCat = x._PayrollCategory; payrollCat = x.PayrollCategoryRef; } if (prCat != null && payrollCat != null && prCat._CatType == CategoryType.Labour && payrollCat._Unit == ItemUnit.Hours && payrollCat._InternalType == InternalType.None) { projTransLstEntity.Add(x); } } if (projTransLstEntity == null) { busyIndicator.IsBusy = false; UnicontaMessageBox.Show(string.Format(Uniconta.ClientTools.Localization.lookup("RecordsUpdated"), 0, string.Empty), Uniconta.ClientTools.Localization.lookup("Information"), MessageBoxButton.OK); return; } //Only create budget project with transactions var insertLst = new List <UnicontaBaseEntity>(); var grpProject = projTransLstEntity.GroupBy(x => x._Project); foreach (var rec in grpProject) { ProjectBudget projBudget; bool hasValue = dictProjBudget.TryGetValue(string.Concat(rec.Key, dBudgetGroup), out projBudget); if (!hasValue) { insertLst.Add(new ProjectBudget() { _Project = rec.Key, _Group = dBudgetGroup, _Name = dBudgetComment, _Current = true }); } } if (insertLst.Count > 0) { res = await api.Insert(insertLst); //Create new Budget-Header } //Insert new header in dictionary for fast lookup foreach (ProjectBudget rec in insertLst) { ProjectBudget projBudget; bool hasValue = dictProjBudget.TryGetValue(string.Concat(rec._Project, dBudgetGroup), out projBudget); if (!hasValue) { dictProjBudget.Add(string.Concat(rec._Project, dBudgetGroup), rec); } } ProjectBudgetLineLocal projBudgetLine; var dictProjBudgetLine = new Dictionary <int, ProjectBudgetLineLocal>(); foreach (var trans in projTransLstEntity) { if (dictProjBudgetLine.TryGetValue(GetHashCode(trans, budgetMethod), out projBudgetLine)) { projBudgetLine._Qty += trans._Qty; projBudgetLine._CostTotal += trans.Cost; projBudgetLine._CostPrice = projBudgetLine._Qty != 0 ? Math.Round(projBudgetLine._CostTotal / projBudgetLine._Qty, 2) : 0; projBudgetLine._SalesTotal += trans.Sales; projBudgetLine._SalesPrice = projBudgetLine._Qty != 0 ? Math.Round(projBudgetLine._SalesTotal / projBudgetLine._Qty, 2) : 0; } else { ProjectBudget projBudget; bool hasValue = dictProjBudget.TryGetValue(string.Concat(trans._Project, dBudgetGroup), out projBudget); if (hasValue) { projBudgetLine = new ProjectBudgetLineLocal(); projBudgetLine.SetMaster(projBudget); projBudgetLine._Project = trans._Project; projBudgetLine._Qty = trans._Qty; projBudgetLine._CostTotal += trans.Cost; projBudgetLine._CostPrice = projBudgetLine._Qty != 0 ? Math.Round(projBudgetLine._CostTotal / projBudgetLine._Qty, 2) : 0; projBudgetLine._SalesTotal += trans.Sales; projBudgetLine._SalesPrice = projBudgetLine._Qty != 0 ? Math.Round(projBudgetLine._SalesTotal / projBudgetLine._Qty, 2) : 0; projBudgetLine._Date = GetDate(trans._Date, budgetMethod); projBudgetLine._Employee = trans._Employee; projBudgetLine._PayrollCategory = budgetMethod == 0 || budgetMethod == 1 ? trans._PayrollCategory : null; projBudgetLine._PrCategory = trans._PrCategory; projBudgetLine._Text = string.Concat("(", TMJournalLineHelper.GetTimeStamp(), ") ", Uniconta.ClientTools.Localization.lookup("Created")); dictProjBudgetLine.Add(GetHashCode(trans, budgetMethod), projBudgetLine); } } } if (dictProjBudgetLine.Count > 0) { res = await api.Insert(dictProjBudgetLine.Values); localMenu_OnItemClicked("RefreshGrid"); } busyIndicator.IsBusy = false; UnicontaMessageBox.Show(string.Format(Uniconta.ClientTools.Localization.lookup("RecordsUpdated"), dictProjBudgetLine.Count, string.Empty), Uniconta.ClientTools.Localization.lookup("Information"), MessageBoxButton.OK); } }; cwCreateBjt.Show(); }
public int _ImportEmailBilag() { MimeMessage message; int antalbilag = 0; clsParam objParam = null; using (var imap_client = new ImapClient()) { imap_client.Connect("imap.gigahost.dk", 993, true); imap_client.AuthenticationMechanisms.Remove("XOAUTH"); imap_client.Authenticate(clsApp.GigaHostImapUser, clsApp.GigaHostImapPW); var Puls3060Bilag = imap_client.GetFolder("_Puls3060Bilag"); var Puls3060BilagArkiv = imap_client.GetFolder("_Puls3060BilagArkiv"); Puls3060Bilag.Open(FolderAccess.ReadWrite); var results = Puls3060Bilag.Search(SearchQuery.All); antalbilag = results.Count(); foreach (var result in results) { message = Puls3060Bilag.GetMessage(result); List <VouchersClient> documentlist = new List <VouchersClient>(); if (message.Body.ContentType.MimeType == "application/ms-tnef") { antalbilag--; continue; } MemoryStream msMail = new MemoryStream(); message.WriteTo(msMail); VouchersClient mail = new VouchersClient() { Fileextension = FileextensionsTypes.EML, Text = "e-Mail", VoucherAttachment = msMail.ToArray(), DocumentDate = DateTime.Now, }; var task1 = m_api.Insert(mail); task1.Wait(); var res1 = task1.Result; documentlist.Add(mail); foreach (var msg_attachment in message.Attachments) { if (msg_attachment is MimePart) { FileextensionsTypes type = FileextensionsTypes.PDF; switch (msg_attachment.ContentType.MediaSubtype.ToUpper()) { case "PDF": type = FileextensionsTypes.PDF; break; case "JPEG": type = FileextensionsTypes.JPEG; break; case "TXT": type = FileextensionsTypes.TXT; break; case "PLAIN": type = FileextensionsTypes.TXT; break; case "MSWORD": type = FileextensionsTypes.DOC; break; case "VND.OPENXMLFORMATS-OFFICEDOCUMENT.SPREADSHEETML.SHEET": type = FileextensionsTypes.XLSX; break; default: type = FileextensionsTypes.UNK; break; } var part = (MimePart)msg_attachment; MemoryStream msstream = new MemoryStream(); part.ContentObject.DecodeTo(msstream); byte[] arrStream = msstream.ToArray(); if (type == FileextensionsTypes.UNK) { if (arrStream[0] == 0x25 && arrStream[1] == 0x50 && arrStream[2] == 0x44 && arrStream[3] == 0x46) // PDF Magic number { type = FileextensionsTypes.PDF; } } VouchersClient attm = new VouchersClient() { Fileextension = type, Text = (msg_attachment as MimePart).FileName, VoucherAttachment = arrStream, DocumentDate = DateTime.Now, }; var task3 = m_api.Insert(attm); task3.Wait(); var res3 = task3.Result; documentlist.Add(attm); } else if (msg_attachment is MessagePart) { string wmsgtext; var msgpart = msg_attachment as MessagePart; if (string.IsNullOrEmpty(msgpart.Message.HtmlBody)) { wmsgtext = msgpart.Message.TextBody; } else { wmsgtext = msgpart.Message.HtmlBody; } var msgtext = Regex.Replace(wmsgtext, "<[^>]*>", String.Empty).Replace(" ", String.Empty).Trim(); string[] splitstring = { "\r\n" }; string[] arrParams = msgtext.Split(splitstring, StringSplitOptions.RemoveEmptyEntries); objParam = new clsParam(arrParams); } } if (documentlist.Count > 0) { VouchersClient folder = new VouchersClient() { _Fileextension = FileextensionsTypes.DIR, _Text = message.Subject, _DocumentDate = DateTime.Now, }; var ref3 = folder.PrimaryKeyId; var task4 = m_api.Insert(folder); task4.Wait(); var res4 = task4.Result; var ref1 = folder.PrimaryKeyId; DocumentAPI docapi = new DocumentAPI(m_api); //TEST //var task5 = docapi.CreateFolder(folder, null); //var task5 = docapi.AppendToFolder(folder, documentlist); var task5 = docapi.CreateFolder(folder, documentlist); task5.Wait(); var res5 = task5.Result; var ref2 = folder.PrimaryKeyId; int DocumentRef = ref2; if (ref1 != ref2) //Delete ref1 { var crit = new List <PropValuePair>(); var pair = PropValuePair.GenereteWhereElements("PrimaryKeyId", typeof(int), ref1.ToString()); crit.Add(pair); var task6 = m_api.Query <VouchersClient>(crit); task6.Wait(); var col = task6.Result; if (col.Count() == 1) { var rec = col[0]; m_api.DeleteNoResponse(rec); } } if ((objParam == null) || (objParam.Delsystem == null)) { objParam = new clsParam() { Delsystem = "Finans", Tekst = "Ukendt post", Kontotype = "Finans", Konto = "5840", Modkontotype = "Finans", Modkonto = "9900", Kredit = 0.00 }; } switch (objParam.Delsystem.ToLower()) { case "finans": InsertFinansJournal(message, DocumentRef, objParam); break; case "kreditor": InsertKøbsOrder(message, DocumentRef, objParam); break; default: break; } // move email to arkiv var newId = Puls3060Bilag.MoveTo(result, Puls3060BilagArkiv); } } Puls3060Bilag.Close(); imap_client.Disconnect(true); } return(antalbilag);// message; }
void localMenu_OnItemClicked(string ActionType) { var fromDate = txtDateFrm.DateTime; var toDate = txtDateTo.DateTime; var lin = dgVatReport.SelectedItem as VatReportLine; switch (ActionType) { case "VatReportSpain": { List <VatReportLine> lst = (List <VatReportLine>)dgVatReport.ItemsSource; if (lst == null) { return; } var array = UnicontaClient.Pages.GL.Reports.VatSpain.calc(lst.ToArray()); AddDockItem(TabControls.VatReportSpain, new object[] { api, array }, "Modelo 303", null, closeIfOpened: true); break; } case "VatReportNorway": if (vatReportSum != null) { AddDockItem(TabControls.VatReportNorway, new object[] { vatReportSum, fromDate, toDate }, "Mva skattemeldingen", null, closeIfOpened: true); } break; case "VatReportDenmark": if (vatReportSum != null) { AddDockItem(TabControls.VatReportDenmark, new object[] { api, this.vatReportSum, fromDate, toDate }, "Momsopgørelse", null, closeIfOpened: true); } break; case "VatReportHolland": if (vatReportSum != null) { AddDockItem(TabControls.VatReportHolland, new object[] { vatReportSum, fromDate, toDate }, "BTW Aangifte", null, closeIfOpened: true); } break; case "VatReportEstonia": if (vatReportSum != null) { AddDockItem(TabControls.VatReportEstonia, new object[] { vatReportSum, fromDate, toDate }, "KM avaldus", null, closeIfOpened: true); } break; case "VatReportUnitedKingdom": if (vatReportSum != null) { AddDockItem(TabControls.VatReportUnitedKingdom, new object[] { vatReportSum, fromDate, toDate }, "VAT statement", null, closeIfOpened: true); } break; case "Transactions": if (lin?.Account != null) { if (string.IsNullOrEmpty(cmbJournal.Text)) { var dt = PropValuePair.GenereteWhereElements("Date", fromDate, CompareOperator.GreaterThanOrEqual); dt.OrList[0].SecundaryValue = NumberConvert.ToString(toDate.Ticks); var filter = new PropValuePair[] { dt, PropValuePair.GenereteWhereElements("Account", lin.AccountNumber, CompareOperator.Equal), PropValuePair.GenereteWhereElements("Vat", lin.Vat != null ? lin.Vat._Vat : "null", CompareOperator.Equal) }; AddDockItem(TabControls.AccountsTransaction, new object[] { api, filter }, string.Format("{0}: {1}", Uniconta.ClientTools.Localization.lookup("Transactions"), lin.AccountNumber)); } else { string header = string.Concat(Uniconta.ClientTools.Localization.lookup("AccountStatement"), "/", lin.Account.AccountNumber); var transactionReport = dockCtrl.AddDockItem(TabControls.TransactionReport, this.ParentControl, new object[] { lin.Account, IdObject.get(true) }, header) as TransactionReport; if (transactionReport != null) { transactionReport.SetControlsAndLoadGLTrans(fromDate, toDate, null, null, null, null, null, cmbJournal.Text); } } } break; case "VatReportIceland": if (vatReportSum != null) { AddDockItem(TabControls.VatReportIceland, new object[] { vatReportSum, fromDate, toDate }, "VAT statement", null, closeIfOpened: true); } break; default: gridRibbon_BaseActions(ActionType); break; } }
public void ecxelPoster() { var api = UCInitializer.GetBaseAPI; CompanyFinanceYear CurrentCompanyFinanceYear = null; var task1 = api.Query <CompanyFinanceYear>(); task1.Wait(); var cols1 = task1.Result; foreach (var col in cols1) { if (col._Current) { CurrentCompanyFinanceYear = col; } } var task2a = api.Query <Debtor>(); task2a.Wait(); var karDebtor = task2a.Result; var task2b = api.Query <Creditor>(); task2b.Wait(); var karCreditor = task2b.Result; KarDebCred karDebCred = new KarDebCred(); foreach (var d in karDebtor) { RecDebCred recDebCred = new RecDebCred() { _Account = d._Account, _Name = d._Name }; karDebCred.Add(recDebCred); } foreach (var k in karCreditor) { RecDebCred recDebCred = new RecDebCred() { _Account = k._Account, _Name = k._Name }; karDebCred.Add(recDebCred); } var task3 = api.Query <GLAccount>(); task3.Wait(); var karGLAccount = task3.Result; var crit = new List <PropValuePair>(); string dateinterval = string.Format("{0}..{1}", CurrentCompanyFinanceYear._FromDate.ToShortDateString(), CurrentCompanyFinanceYear._ToDate.ToShortDateString()); var pair = PropValuePair.GenereteWhereElements("Date", typeof(DateTime), dateinterval); crit.Add(pair); var task4 = api.Query <GLTrans>(null, crit); task4.Wait(); var karGLTrans = task4.Result; DateTime pReadDate = DateTime.Now; string pSheetName = "Poster"; char[] dash = { '-' }; _Excel.Application oXL = null;; _Excel._Workbook oWB; _Excel._Worksheet oSheetPoster; _Excel._Worksheet oSheetRegnskab; _Excel._Worksheet oSheetRegnskab_puls3060 = null; _Excel.Window oWindow; _Excel.Range oRng; string rec_regnskab_Eksportmappe = @"%userprofile%\Documents\SummaSummarum\"; // work rec_regnskab_Eksportmappe = Environment.ExpandEnvironmentVariables(rec_regnskab_Eksportmappe); string SaveAs = rec_regnskab_Eksportmappe + pSheetName + pReadDate.ToString("_yyyyMMdd_HHmmss") + ".xlsx"; var JournalPoster = from h in karGLTrans join d1 in karGLAccount on h._Account equals d1._Account into details1 from x1 in details1.DefaultIfEmpty() join d2 in karDebCred on h._DCAccount equals d2._Account into details2 from x2 in details2.DefaultIfEmpty(new RecDebCred() { _Account = null, _Name = null }) orderby h._JournalPostedId, h._Voucher, h._VoucherLine select new clsJournalposter { ds = DS(x1.AccountTypeEnum), k = IUAP(x1.AccountTypeEnum), Konto = h._Account + "-" + x1._Name, DebKrd = x2._Name, //DebKrd = h._DCAccount, Dato = h._Date, Klade = h._JournalPostedId, Serie = h._NumberSerie, Bilag = h._Voucher, Linie = h._VoucherLine, Tekst = h._Text, Beløb = h._Amount, }; var count = JournalPoster.Count(); using (new ExcelUILanguageHelper()) { try { //Start Excel and get Application object. oXL = new _Excel.Application(); oXL.Visible = true; //oXL.Visible = true; //For debug //Get a new workbook. oWB = oXL.Workbooks.Add((Missing.Value)); oSheetPoster = (_Excel._Worksheet)oWB.ActiveSheet; oWindow = oXL.ActiveWindow; if (pSheetName.Length > 0) { oSheetPoster.Name = pSheetName.Substring(0, pSheetName.Length > 34 ? 34 : pSheetName.Length); } //////////////////////////////////////////////////////////////////////// oSheetPoster.Name = "Poster"; int row = 1; foreach (clsJournalposter m in JournalPoster) { row++; //if (row > 500) break; //<---------------------------------------------- Type objectType = m.GetType(); PropertyInfo[] properties = objectType.GetProperties(); int col = 0; foreach (PropertyInfo property in properties) { col++; string Name = property.Name; //string NamePropertyType = property.GetValue(m, null).GetType().ToString(); oSheetPoster.Cells[row, col] = property.GetValue(m, null); if (row == 2) { object[] CustomAttributes = property.GetCustomAttributes(false); foreach (var att in CustomAttributes) { Type tp = att.GetType(); if (tp.ToString() == "Trans2SummaHDC.Fieldattr") { Fieldattr attr = (Fieldattr)att; string heading = attr.Heading; oSheetPoster.Cells[1, col] = heading; } } } } } oRng = (_Excel.Range)oSheetPoster.Rows[1, Missing.Value]; oRng.Font.Name = "Arial"; oRng.Font.Size = 12; oRng.Font.Strikethrough = false; oRng.Font.Superscript = false; oRng.Font.Subscript = false; oRng.Font.OutlineFont = false; oRng.Font.Shadow = false; oRng.Font.Bold = true; oRng.HorizontalAlignment = _Excel.Constants.xlCenter; oRng.VerticalAlignment = _Excel.Constants.xlBottom; oRng.WrapText = false; oRng.Orientation = 0; oRng.AddIndent = false; oRng.IndentLevel = 0; oRng.ShrinkToFit = false; oRng.MergeCells = false; string BottomRight = "E" + row.ToString(); //<------------------HUSK oRng = oSheetPoster.get_Range("E2", BottomRight); oRng.NumberFormat = "dd-mm-yyyy"; oSheetPoster.ListObjects.AddEx(_Excel.XlListObjectSourceType.xlSrcRange, oSheetPoster.UsedRange, System.Type.Missing, _Excel.XlYesNoGuess.xlYes).Name = "PosterList"; oSheetPoster.Cells.EntireColumn.AutoFit(); oWindow.SplitRow = 1; oWindow.FreezePanes = true; oSheetPoster.get_Range("A1", Missing.Value).Select(); oSheetRegnskab = (_Excel._Worksheet)oWB.Worksheets.Add(oSheetPoster, System.Type.Missing, System.Type.Missing, System.Type.Missing); //oXL.Visible = true; //For debug _Excel.Range x1 = oSheetPoster.Cells[1, 1]; _Excel.Range x2 = oSheetPoster.Cells[row, 11]; //<--------------------HUSK _Excel.Range xx = oSheetPoster.get_Range(x1, x2); _Excel.PivotField _pvtField = null; _Excel.PivotTable _pivot = oSheetPoster.PivotTableWizard( _Excel.XlPivotTableSourceType.xlDatabase, //SourceType xx, //SourceData oSheetRegnskab.get_Range("A3", Missing.Value), //TableDestination "PivotTable1", //TableName System.Type.Missing, //RowGrand System.Type.Missing, //CollumnGrand System.Type.Missing, //SaveData System.Type.Missing, //HasAutoformat System.Type.Missing, //AutoPage System.Type.Missing, //Reserved System.Type.Missing, //BackgroundQuery System.Type.Missing, //OptimizeCache System.Type.Missing, //PageFieldOrder System.Type.Missing, //PageFieldWrapCount System.Type.Missing, //ReadData System.Type.Missing); //Connection _pvtField = (_Excel.PivotField)_pivot.PivotFields("ds"); _pvtField.Orientation = _Excel.XlPivotFieldOrientation.xlRowField; _pvtField = (_Excel.PivotField)_pivot.PivotFields("k"); _pvtField.Orientation = _Excel.XlPivotFieldOrientation.xlRowField; _pvtField = (_Excel.PivotField)_pivot.PivotFields("Konto"); _pvtField.Orientation = _Excel.XlPivotFieldOrientation.xlRowField; _pvtField = (_Excel.PivotField)_pivot.PivotFields("Dato"); _pvtField.Orientation = _Excel.XlPivotFieldOrientation.xlColumnField; _pvtField = (_Excel.PivotField)_pivot.PivotFields("Beløb"); _pvtField.Orientation = _Excel.XlPivotFieldOrientation.xlDataField; _pvtField.Function = _Excel.XlConsolidationFunction.xlSum; _pvtField.NumberFormat = "#,##0"; oSheetRegnskab.Name = "Regnskab"; oSheetRegnskab.Cells[2, 3] = "Regnskab Hafsjold Data Consult"; oRng = oSheetRegnskab.get_Range("D3", Missing.Value); oRng.Select(); bool[] Periods = { false, false, false, false, true, false, false }; oRng.Group(true, true, Missing.Value, Periods); oRng = oSheetRegnskab.get_Range("D4", "P4"); oRng.HorizontalAlignment = _Excel.XlHAlign.xlHAlignRight; oSheetRegnskab.PageSetup.LeftHeader = "&14Regnskab Hafsjold Data Consult"; oSheetRegnskab.PageSetup.CenterHeader = ""; oSheetRegnskab.PageSetup.RightHeader = "&P af &N"; oSheetRegnskab.PageSetup.LeftFooter = "&Z&F"; oSheetRegnskab.PageSetup.CenterFooter = ""; oSheetRegnskab.PageSetup.RightFooter = "&D&T"; oSheetRegnskab.PageSetup.LeftMargin = oXL.InchesToPoints(0.75); oSheetRegnskab.PageSetup.RightMargin = oXL.InchesToPoints(0.75); oSheetRegnskab.PageSetup.TopMargin = oXL.InchesToPoints(1); oSheetRegnskab.PageSetup.BottomMargin = oXL.InchesToPoints(1); oSheetRegnskab.PageSetup.HeaderMargin = oXL.InchesToPoints(0.5); oSheetRegnskab.PageSetup.FooterMargin = oXL.InchesToPoints(0.5); oSheetRegnskab.PageSetup.PrintHeadings = false; oSheetRegnskab.PageSetup.PrintGridlines = true; oSheetRegnskab.PageSetup.CenterHorizontally = false; oSheetRegnskab.PageSetup.CenterVertically = false; oSheetRegnskab.PageSetup.Orientation = _Excel.XlPageOrientation.xlLandscape; oSheetRegnskab.PageSetup.Draft = false; oSheetRegnskab.PageSetup.PaperSize = _Excel.XlPaperSize.xlPaperA4; oSheetRegnskab.PageSetup.FirstPageNumber = 1; oSheetRegnskab.PageSetup.Order = _Excel.XlOrder.xlDownThenOver; oSheetRegnskab.PageSetup.BlackAndWhite = false; oSheetRegnskab.PageSetup.Zoom = 100; oSheetRegnskab.PageSetup.PrintErrors = _Excel.XlPrintErrors.xlPrintErrorsDisplayed; oWB.ShowPivotTableFieldList = false; oSheetRegnskab_puls3060 = oSheetRegnskab; oSheetRegnskab.get_Range("A1", Missing.Value).Select(); ////////////////////////////////////////////////////////////////////////////////////////////// oSheetRegnskab_puls3060.Activate(); oSheetRegnskab_puls3060.get_Range("A1", Missing.Value).Select(); oWB.SaveAs(SaveAs, _Excel.XlFileFormat.xlWorkbookDefault, "", "", false, false, _Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); oWB.Saved = true; oXL.Visible = true; //oXL.Quit(); //oXL = null; } catch (Exception theException) { String errorMessage; errorMessage = "Error: "; errorMessage = String.Concat(errorMessage, theException.Message); errorMessage = String.Concat(errorMessage, " Line: "); errorMessage = String.Concat(errorMessage, theException.Source); MessageBox.Show(errorMessage, "Error"); } } }
private async Task IncludeTimeJournals() { if (!InclTimeJournals || timeTransFound) { return; } ProjectClient proj = null; EmployeeClient emp = null; if (master != null) { emp = master as EmployeeClient; if (emp == null) { proj = master as ProjectClient; } if (proj == null) { var WIPreport = master as UnicontaClient.Pages.ProjectTransLocalClient; if (WIPreport != null) { proj = WIPreport.ProjectRef; } } } if (timetransLst == null && (master == null || emp != null || proj != null)) { timetransLst = new List <ProjectTransClient>(); timeTransFound = true; busyIndicator.IsBusy = true; var pairTM = new List <PropValuePair>(); if (emp != null) { pairTM.Add(PropValuePair.GenereteWhereElements(nameof(TMJournalLineClient.Employee), typeof(string), emp._Number)); pairTM.Add(PropValuePair.GenereteWhereElements(nameof(TMJournalLineClient.Date), emp._TMApproveDate, CompareOperator.GreaterThanOrEqual)); } else if (proj != null) { if (projLst == null) { var strb = StringBuilderReuse.Create(); strb.Append(proj._Number); foreach (var x in Projects) { if (x._MasterProject == proj._Number) { strb.Append(';').Append(x._Number); } } projLst = strb.ToString(); strb.Release(); } var projselected = includeSubProject ? projLst : proj._Number; pairTM.Add(PropValuePair.GenereteWhereElements(nameof(TMJournalLineClient.Project), typeof(string), projselected)); var minApproveDate = Employees.Where(x => x._TMApproveDate != DateTime.MinValue && x._Terminated == DateTime.MinValue).Min(x => x._TMApproveDate as DateTime?) ?? DateTime.MinValue; if (minApproveDate != DateTime.MinValue) { pairTM.Add(PropValuePair.GenereteWhereElements(nameof(TMJournalLineClient.Date), minApproveDate, CompareOperator.GreaterThanOrEqual)); } } else { var minApproveDate = Employees.Where(x => x._TMApproveDate != DateTime.MinValue && x._Terminated == DateTime.MinValue).Min(x => x._TMApproveDate as DateTime?) ?? DateTime.MinValue; if (minApproveDate != DateTime.MinValue) { pairTM.Add(PropValuePair.GenereteWhereElements(nameof(TMJournalLineClient.Date), minApproveDate, CompareOperator.GreaterThanOrEqual)); } } var tmJourLines = await api.Query <TMJournalLineClient>(pairTM); var tmLines = tmJourLines.Where(s => (s.Project != null && s.PayrollCategory != null && s.Date > Employees.First(z => z._Number == s.Employee)._TMApproveDate)).ToArray(); var search = new TMJournalLineClient(); var sort = new TMJournalEmpDateSort(); int pos = 0; Array.Sort(tmLines, sort); string lastEmployee = null; string lastPayroll = null; string lastProject = null; EmpPayrollCategory payrollCat = null; Uniconta.DataModel.Project project = null; var grpEmpDate = tmLines.GroupBy(x => new { x.Employee, x.Date }).Select(g => new { g.Key.Employee, g.Key.Date, EmployeeTable = Employees.Get(g.Key.Employee) }); foreach (var rec in grpEmpDate) { if (lastEmployee != rec.Employee) { lastEmployee = rec.Employee; await priceLookup.EmployeeChanged(rec.EmployeeTable); } search._Employee = rec.Employee; search._Date = rec.Date; pos = Array.BinarySearch(tmLines, search, sort); if (pos < 0) { pos = ~pos; } while (pos < tmLines.Length) { var s = tmLines[pos++]; if (s._Employee != rec.Employee || s._Date != rec.Date) { break; } if (s.Total != 0) { if (lastPayroll != s._PayrollCategory) { payrollCat = (Uniconta.DataModel.EmpPayrollCategory)Payrolls?.Get(s._PayrollCategory); lastPayroll = s._PayrollCategory; } var line = new ProjectTransClient(); line.IsTimeJournal = true; line._Project = s._Project; line._Employee = s._Employee; line._PayrollCategory = s._PayrollCategory; line._PrCategory = payrollCat?._PrCategory; line._Task = s._Task; line._Invoiceable = s._Invoiceable; line._Date = s._Date; if (s._RegistrationType == RegistrationType.Hours) { line._Text = s._Text; line._Unit = (byte)ItemUnit.Hours; if (payrollCat != null && (payrollCat._InternalType == Uniconta.DataModel.InternalType.OverTime || payrollCat._InternalType == Uniconta.DataModel.InternalType.FlexTime)) { line._Qty = payrollCat._Factor == 0 ? s.Total : s.Total * payrollCat._Factor; } else { line._Qty = s.Total; } } else { line._Text = TMJournalLineClient.GetMileageFormattedText(s._Text, s._AddressFrom, s._AddressTo, s._VechicleRegNo); line._Unit = (byte)ItemUnit.km; line._Qty = s.Total; } s.Day1 = s.Total; s.Day2 = s.Day3 = s.Day4 = s.Day5 = s.Day6 = s.Day7 = 0; await priceLookup.GetEmployeePrice(s); line._CostPrice = s.GetCostPricesDayN(1); line._SalesPrice = s.GetSalesPricesDayN(1); if (api.CompanyEntity._DimFromProject) { if (lastProject != s._Project) { project = (Uniconta.DataModel.Project)Projects.Get(s._Project); lastProject = s._Project; } line._Dim1 = project._Dim1; line._Dim2 = project._Dim2; line._Dim3 = project._Dim3; line._Dim4 = project._Dim4; line._Dim5 = project._Dim5; } else { line._Dim1 = rec.EmployeeTable._Dim1; line._Dim2 = rec.EmployeeTable._Dim2; line._Dim3 = rec.EmployeeTable._Dim3; line._Dim4 = rec.EmployeeTable._Dim4; line._Dim5 = rec.EmployeeTable._Dim5; } timetransLst.Add(line); } } } busyIndicator.IsBusy = false; } if (timetransLst != null) { var transLst = ((IEnumerable <ProjectTransClient>)dgProjectTransaction.ItemsSource).ToList(); transLst.AddRange(timetransLst); dgProjectTransaction.SetSource(transLst.ToArray()); } }
async Task LoadGrid() { fromDate = FromDate.DateTime; toDate = ToDate.DateTime; List <PropValuePair> filter = new List <PropValuePair>(); if (includeSubProject) { var propValuePairFolder = PropValuePair.GenereteParameter("IncludeSubProject", typeof(string), "1"); filter.Add(propValuePairFolder); } if (InvoicedTrans > 0) { var propValuePairFolder = PropValuePair.GenereteParameter("InvoicedTrans", typeof(string), NumberConvert.ToString(InvoicedTrans)); filter.Add(propValuePairFolder); } if (fromDate != DateTime.MinValue) { var propValuePairFolder = PropValuePair.GenereteParameter("FromDate", typeof(string), NumberConvert.ToString(fromDate.Ticks)); filter.Add(propValuePairFolder); } if (toDate != DateTime.MinValue) { var propValuePairFolder = PropValuePair.GenereteParameter("ToDate", typeof(string), NumberConvert.ToString(toDate.Ticks)); filter.Add(propValuePairFolder); } var api = this.api; var CompanyId = api.CompanyId; var cats = api.CompanyEntity.GetCache(typeof(PrCategory)) ?? await api.CompanyEntity.LoadCache(typeof(PrCategory), api); var transTask = api.Query(new ProjectTransCategorySumClientLocal(), dgProjectTransCategorySum.masterRecords, filter); var trans = await transTask; if (trans == null) { dgProjectTransCategorySum.SetSource(null); return; } var len = trans.Length; var sort = new ProjectTransCategorySort(); Array.Sort(trans, sort); List <ProjectTransCategorySumClientLocal> extras = null, sums = new List <ProjectTransCategorySumClientLocal>(); if (showBudget) { var budget = await api.Query(new ProjectBudgetCategorySumClient(), dgProjectTransCategorySum.masterRecords, filter); var key = new ProjectTransCategorySumClientLocal(); foreach (var bc in budget) { key._Project = bc._Project; key._PrCategory = bc._PrCategory; var idx = Array.BinarySearch(trans, key, sort); if (idx >= 0 && idx < len) { var t = trans[idx]; t._BudgetSales += bc._Sales; t._BudgetCost += bc._Cost; t._BudgetQty += bc._Qty; } else { var prTrans = new ProjectTransCategorySumClientLocal() { _CompanyId = CompanyId, _BudgetSales = bc._Sales, _BudgetCost = bc._Cost, _BudgetQty = bc._Qty, _PrCategory = bc._PrCategory, _Project = bc._Project }; var cat = (PrCategory)cats.Get(bc._PrCategory); prTrans._CatType = cat._CatType; if (cat._CatType == CategoryType.Sum || cat._CatType == CategoryType.Header) { sums.Add(prTrans); } else { if (extras == null) { extras = new List <ProjectTransCategorySumClientLocal>(); } extras.Add(prTrans); } } } if (extras != null) { Array.Resize(ref trans, len + extras.Count); foreach (var sum in extras) { trans[len++] = sum; } Array.Sort(trans, sort); extras = null; } } foreach (var t in trans) { var cat = (PrCategory)cats.Get(t._PrCategory); if (cat != null && (cat._CatType == CategoryType.Revenue || cat._CatType == CategoryType.OnAccountInvoicing)) { t._InvoicedQty = -t._Qty; t._Invoiced = -t._Sales; t._BudgetInvoicedQty = -t._BudgetQty; t._BudgetInvoiced = -t._BudgetSales; t._Qty = 0; t._BudgetQty = 0; t._Cost = 0; t._Sales = 0; t._BudgetCost = 0; t._BudgetSales = 0; } } int start = 0; while (start < len) { int end; string ProjectNumber; if (master == null) { ProjectNumber = trans[start]._Project; for (end = start; (end < len && trans[end]._Project == ProjectNumber); end++) { ; } } else { ProjectNumber = ((Uniconta.DataModel.Project)master)._Number; end = len; } int headerAddedLast = 0; foreach (var cat in (PrCategory[])cats.GetKeyStrRecords) { if (cat != null && (cat._CatType == CategoryType.Sum || cat._CatType == CategoryType.Header)) { PropValuePair SumList = AccountSum.Generate(cat._Sum); if (SumList != null) { double Sales = 0, Cost = 0, Qty = 0, BudgetSales = 0, BudgetQty = 0, BudgetCost = 0, Invoiced = 0, InvoicedBudget = 0, InvoicedQty = 0, InvoicedBudgetQty = 0; for (int j = start; j < end; j++) { var Acc2 = trans[j]; if (AccountSum.IsIncluded(SumList, Acc2._PrCategory)) { Sales += Acc2._Sales; Cost += Acc2._Cost; Qty += Acc2._Qty; BudgetCost += Acc2._BudgetCost; BudgetSales += Acc2._BudgetSales; BudgetQty += Acc2._BudgetQty; Invoiced += Acc2._Invoiced; InvoicedBudget += Acc2._BudgetInvoiced; InvoicedQty += Acc2._InvoicedQty; InvoicedBudgetQty += Acc2._BudgetInvoicedQty; } } var sum = new ProjectTransCategorySumClientLocal() { _CompanyId = CompanyId, _Project = ProjectNumber, _PrCategory = cat._Number, _CatType = cat._CatType }; sum._Qty = Math.Round(Qty, 2); sum._BudgetQty = Math.Round(BudgetQty, 2); sum._Sales = Math.Round(Sales, 2); sum._Cost = Math.Round(Cost, 2); sum._BudgetSales = Math.Round(BudgetSales, 2); sum._BudgetCost = Math.Round(BudgetCost, 2); sum._Invoiced = Math.Round(Invoiced, 2); sum._BudgetInvoiced = Math.Round(InvoicedBudget, 2); sum._InvoicedQty = Math.Round(InvoicedQty, 2); sum._BudgetInvoicedQty = Math.Round(InvoicedBudgetQty, 2); if (sum._Qty != 0 || sum._BudgetQty != 0 || sum._Sales != 0 || sum._Cost != 0 || sum._BudgetSales != 0 || sum._BudgetCost != 0 || sum._Invoiced != 0 || sum._BudgetInvoiced != 0 || sum._InvoicedQty != 0 || sum._BudgetInvoicedQty != 0) { sums.Add(sum); headerAddedLast = 0; } else if (cat._CatType == CategoryType.Header) { sums.Add(sum); headerAddedLast++; } } else if (cat._CatType == CategoryType.Header) { sums.Add(new ProjectTransCategorySumClientLocal() { _CompanyId = CompanyId, _Project = ProjectNumber, _PrCategory = cat._Number, _CatType = CategoryType.Header }); headerAddedLast++; } } } if (headerAddedLast > 0) { sums.RemoveRange(sums.Count - headerAddedLast, headerAddedLast); } start = end; } if (sums.Count > 0) { Array.Resize(ref trans, len + sums.Count); foreach (var sum in sums) { trans[len++] = sum; } Array.Sort(trans, sort); } dgProjectTransCategorySum.SetSource(trans); }
async Task <List <BalanceClient> > GenerateTemplateGrid(int Cols) { GLReportTemplate template; var templateCache = api.GetCache(typeof(GLReportTemplate)); if (templateCache != null) { template = (GLReportTemplate)templateCache.Get(AppliedTemplate); } else { template = new GLReportTemplate() { _Name = AppliedTemplate }; await api.Read(template); } if (template == null || template.RowId == 0) { return(null); } var reportline = await api.Query <GLReportLine>(template); var items = new TemplateDataContext(); var TemplateReportlist = items.TemplateReportlist; TemplateReportlist.Capacity = reportline.Length; var newBalance = new List <BalanceClient>(reportline.Length); var SumContext = new TemplateSumContext(Cols); var colCount = PassedCriteria.selectedCriteria.Count; bool AnyHidden = false; int i, j; for (j = 0; (j < reportline.Length); j++) { var line = reportline[j]; var amounts = new long[colCount]; if (line._Accounts != null && !line._ExpressionSum) { var SumAccounts = PropValuePair.GenereteWhereElements("Account", typeof(string), line._Accounts); foreach (var balSum in balanceClient) { if (balSum.AccountTypeEnum > GLAccountTypes.CalculationExpression && AccountSum.IsIncluded(SumAccounts, balSum.AccountNo)) { balSum.SumUpAmount(amounts); } } if (Skip0Account) { bool found = false; for (i = 0; (i < Cols); i++) { if (amounts[i] != 0) { found = true; break; } } if (!found) { continue; } } } if (line._InvertSign) { for (i = 0; (i < Cols); i++) { amounts[i] = -amounts[i]; } } if (line._SaveTotal != 0) { SumContext.CalcMethod.AddSum(line._SaveTotal, amounts); } if (line._Hide) { AnyHidden = true; } var newBalanceCol = new BalanceClient(amounts); newBalanceCol.Acc._Name = line._Text; newBalance.Add(newBalanceCol); TemplateReportlist.Add(new TemplateDataItems(newBalanceCol, hdrData, line) { Masterfontsize = template._FontSize }); } // Now we will take all expressions and update. var pars = new parser(SumContext); for (j = 0; (j < TemplateReportlist.Count); j++) { var item = TemplateReportlist[j]; var line = item.line; if (line._ExpressionSum) { var InvertSign = line._InvertSign; var e = pars.parse(line._Accounts, Uniconta.Script.ValueType.Double); if (e != null) { var amounts = item.blc.amount; for (i = 0; (i < Cols); i++) { SumContext.CurIndex = i; var val = NumberConvert.ToLong(e.Value()); amounts[i] = !InvertSign ? val : -val; } if (line._SaveTotal != 0) { SumContext.CalcMethod.AddSum(line._SaveTotal, amounts); } } } } if (AnyHidden) { for (i = TemplateReportlist.Count; (--i >= 0);) { if (TemplateReportlist[i].line._Hide) { TemplateReportlist.RemoveAt(i); } } } AccountName.Visible = AccountNo.Visible = false; Text.Visible = true; dgBalanceReport.ItemsSource = TemplateReportlist; templateReportData = new object[] { items, hdrData, PassedCriteria.ObjBalance, null }; return(newBalance); }
public void InsertGLDailyJournalLines(MemBogfoeringsKlader karKladde) { var crit = new List <PropValuePair>(); var pair = PropValuePair.GenereteWhereElements("KeyStr", typeof(String), "Dag"); crit.Add(pair); var task1 = m_api.Query <GLDailyJournalClient>(crit); task1.Wait(); var col = task1.Result; var rec_Master = col.FirstOrDefault(); foreach (var kk in karKladde) { GLDailyJournalLineClient jl = new GLDailyJournalLineClient() { Date = (DateTime)kk.Dato, Text = kk.Tekst, }; if ((kk.Afstemningskonto == "Bank") && (kk.Kontonr == null)) { jl.Account = "5840"; if (kk.Belob > 0) { jl.Debit = (double)kk.Belob; } else { jl.Credit = -(double)kk.Belob; } } if ((kk.Afstemningskonto == "PayPal") && (kk.Kontonr == null)) { jl.Account = "5830"; if (kk.Belob > 0) { jl.Debit = (double)kk.Belob; } else { jl.Credit = -(double)kk.Belob; } } if ((kk.Afstemningskonto == "PayPal") && //NEW (kk.Kontonr != null)) { jl.Account = "5830"; jl.OffsetAccount = kk.Kontonr.ToString(); if (kk.Belob > 0) { jl.Debit = (double)kk.Belob; } else { jl.Credit = -(double)kk.Belob; } } if ((String.IsNullOrEmpty(kk.Afstemningskonto)) && (kk.Kontonr != null)) { jl.Account = kk.Kontonr.ToString(); if (kk.Belob > 0) { jl.Credit = (double)kk.Belob; } else { jl.Debit = -(double)kk.Belob; } } jl.SetMaster(rec_Master); var task2 = m_api.Insert(jl); task2.Wait(); var err = task2.Result; } }
public int BogforIndBetalinger() { if (m_CurrentCompanyFinanceYear.Closed == true) { return(0); } DateTime?Startdato = m_CurrentCompanyFinanceYear._FromDate; DateTime?Slutdato = m_CurrentCompanyFinanceYear._ToDate; int saveBetid = 0; var bogf = (from bl in m_dbData3060.Tblbetlin where (bl.Pbstranskode == "0236" || bl.Pbstranskode == "0297") && (Startdato <= bl.Indbetalingsdato && bl.Indbetalingsdato <= Slutdato) join b in m_dbData3060.Tblbet on bl.Betid equals b.Id where b.Summabogfort == null || b.Summabogfort == false //<<------------------------------- join p in m_dbData3060.Tblfrapbs on b.Frapbsid equals p.Id orderby p.Id, b.Id, bl.Id select new betrec { Frapbsid = p.Id, Leverancespecifikation = p.Leverancespecifikation, Betid = b.Id, GruppeIndbetalingsbelob = b.Indbetalingsbelob, Betlinid = bl.Id, Betalingsdato = bl.Betalingsdato, Indbetalingsdato = bl.Indbetalingsdato, Indbetalingsbelob = bl.Indbetalingsbelob, Faknr = bl.Faknr, Debitorkonto = bl.Debitorkonto, Nr = bl.Nr }).ToList(); int AntalBetalinger = bogf.Count(); foreach (var b in bogf) { var critMedlem = new List <PropValuePair>(); var pairMedlem = PropValuePair.GenereteWhereElements("KeyStr", typeof(String), b.Nr.ToString()); critMedlem.Add(pairMedlem); var taskMedlem = m_api.Query <Medlem>(critMedlem); taskMedlem.Wait(); var resultMedlem = taskMedlem.Result; var antalMedlem = resultMedlem.Count(); if (antalMedlem == 1) { var recMedlem = resultMedlem.First(); b.DebitorNavn = recMedlem.KeyName; } else { b.DebitorNavn = "Ukendt medlem"; } } if (bogf.Count() > 0) { DateTime nu = DateTime.Now; DateTime ToDay = new DateTime(nu.Year, nu.Month, nu.Day);; int BS1_SidsteNr = 0; MemBogfoeringsKlader karKladde = new MemBogfoeringsKlader(); int count = 0; foreach (var b in bogf) { if (saveBetid != b.Betid) // ny gruppe { saveBetid = b.Betid; recBogfoeringsKlader gkl = new recBogfoeringsKlader { Dato = ToDay, Bilag = ++BS1_SidsteNr, Tekst = "Indbetalingskort K 81131945-" + ((DateTime)b.Indbetalingsdato).Day + "." + ((DateTime)b.Indbetalingsdato).Month, Afstemningskonto = "Bank", Belob = b.GruppeIndbetalingsbelob, Kontonr = null, Faknr = null, Sagnr = null }; karKladde.Add(gkl); var rec_bet = (from ub in m_dbData3060.Tblbet where ub.Id == b.Betid select ub).First(); rec_bet.Summabogfort = true; } char[] trim0 = { '0' }; IQueryable <msmrecs> msm; if (b.Faknr != 0) //Indbetalingskort sendt af Nets { msm = from f in m_dbData3060.Tblfak where f.Faknr == b.Faknr select new msmrecs { faknr = f.Faknr, Nr = f.Nr, name = b.DebitorNavn, bogfkonto = f.Bogfkonto, fradato = f.Fradato, tildato = f.Tildato }; } else //Indbetalingskort ikke sendt af Nets { msm = from f in m_dbData3060.Tblfak where f.Indbetalerident.TrimStart(trim0) == b.Debitorkonto.TrimStart(trim0) select new msmrecs { faknr = f.Faknr, Nr = f.Nr, name = b.DebitorNavn, bogfkonto = f.Bogfkonto, fradato = f.Fradato, tildato = f.Tildato }; } if (msm.Count() == 1) //Kontingent betaling for RSMembership { var f = msm.First(); decimal[] arrBelob = clsPbs602.fordeling((decimal)b.Indbetalingsbelob, (DateTime)f.fradato, (DateTime)f.tildato, (DateTime)Startdato, (DateTime)Slutdato); recBogfoeringsKlader kl; string wTekst = ("F" + f.faknr + " " + f.Nr + " " + f.name).PadRight(40, ' ').Substring(0, 40); try { if (((DateTime)b.Indbetalingsdato - (DateTime)b.Betalingsdato).Days > 300) // faktura mere end 300 dage gammel { wTekst = ("???" + wTekst).PadRight(40, ' ').Substring(0, 40); } } catch { } if (arrBelob[0] > 0) { kl = new recBogfoeringsKlader { Dato = ToDay, Bilag = BS1_SidsteNr, Tekst = wTekst, Afstemningskonto = null, Belob = arrBelob[0], Kontonr = 1003, //f.bogfkonto, Faknr = null, Sagnr = null }; karKladde.Add(kl); } if (arrBelob[1] > 0) { kl = new recBogfoeringsKlader { Dato = ToDay, Bilag = BS1_SidsteNr, Tekst = wTekst, Afstemningskonto = null, Belob = arrBelob[1], Kontonr = 6831, //64200, Faknr = null, Sagnr = null }; karKladde.Add(kl); } } else //Anden betaling { recBogfoeringsKlader kl = new recBogfoeringsKlader { Dato = ToDay, Bilag = BS1_SidsteNr, Tekst = ("Ukendt betaling").PadRight(40, ' ').Substring(0, 40), Afstemningskonto = null, Belob = b.Indbetalingsbelob, Kontonr = 6833, //65050, Faknr = null, Sagnr = null }; karKladde.Add(kl); } } InsertGLDailyJournalLines(karKladde); m_dbData3060.SaveChanges(); } return(AntalBetalinger); }
////summary //// The Execute method to execute on the basis of parameter passed ////summary ////Params UnicontaBaseEntity master :- To pass the master table ////Params UnicontaBaseEntity currentRow :- To pass the current row ////Params IEnumerable<UnicontaBaseEntity> source :- To pass List of UnicontaBaseEntity ////Params String Command :- pass the command ////Params String args :- pass the argument ////Returns ErrorCodes public ErrorCodes Execute(UnicontaBaseEntity master, UnicontaBaseEntity currentRow, IEnumerable <UnicontaBaseEntity> source, string command, string args) { if (currentRow == null || !(currentRow is DebtorOrderClient)) { MessageBox.Show("ERROR: No row or wrong type"); return(ErrorCodes.Exception); } // Parse currentRow var order = currentRow as DebtorOrderClient; // Parse args var itemList = args.Split(',').Select(x => x.Split(':')).ToList(); if (itemList == null) { MessageBox.Show("No Args"); return(ErrorCodes.Exception); } // Getting Items var itemFilter = ""; itemList.ForEach(item => itemFilter += $"{item[0]};"); var itemFilters = new List <PropValuePair> { PropValuePair.GenereteWhereElements("KeyName", typeof(string), itemFilter) }; var items = crudAPI.Query <InvItemClient>(itemFilters).Result.ToList(); // Creating new order lines var newOrderLines = new List <DebtorOrderLineClient>(); foreach (var item in items) { var itemQty = itemList.FirstOrDefault(x => x[0] == item.KeyName); if (itemQty == null) { continue; } var orderLine = new DebtorOrderLineClient { _Item = item.Item, _Qty = double.Parse(itemQty[1]), _Price = item.SalesPrice1 }; orderLine.SetMaster(order); newOrderLines.Add(orderLine); } // Call insert API var insertError = crudAPI.Insert(newOrderLines).Result; if (insertError != ErrorCodes.Succes) { MessageBox.Show("ERROR: Failed to insert order lines"); return(ErrorCodes.Exception); } // Attacting UserDocument to order. var imageFile = File.ReadAllBytes(@"C:\src\Uniconta\Technical-Training-Cases-master\TrainingData\LIGHT-FLOORPANEL.jpg"); UserDocsClient newUserDoc = new UserDocsClient { Created = DateTime.Now, DocumentType = FileextensionsTypes.JPEG, Text = "LIGHT-FLOORPANEL.jpg", _Data = imageFile, }; newUserDoc.SetMaster(order); // Calling insert API if (crudAPI.Insert(newUserDoc).Result != ErrorCodes.Succes) { MessageBox.Show("ERROR: Failed to insert UserDocument"); return(ErrorCodes.Exception); } // Writes file to disk var orderDocuments = crudAPI.Query <UserDocsClient>(order).Result; if (orderDocuments.Length != 0) { var readResult = crudAPI.Read(orderDocuments[0]).Result; var fileBytes = orderDocuments[0]._Data; if (orderDocuments[0].DocumentType == FileextensionsTypes.JPEG) { File.WriteAllBytes(@"C:\src\Uniconta\Technical-Training-Cases-master\TrainingData\LIGHT-FLOORPANEL-return.jpg", fileBytes); } } // Refresh Grid View if (OnExecute != null) { PluginEventArgs arg = new PluginEventArgs(); arg.EventType = PluginEvent.RefreshGrid; OnExecute(null, arg); } return(ErrorCodes.Succes); }
public int betalinger_opdate_uniconta(dbData3060DataContext p_dbData3060, CrudAPI api) { int saveBetid = 0; var rsmbrshp = from bl in p_dbData3060.Tblbetlin where (bl.Pbstranskode == "0236" || bl.Pbstranskode == "0297") join b in p_dbData3060.Tblbet on bl.Betid equals b.Id where b.Rsmembership == null || b.Rsmembership == false join p in p_dbData3060.Tblfrapbs on b.Frapbsid equals p.Id orderby p.Id, b.Id, bl.Id select new { Frapbsid = p.Id, p.Leverancespecifikation, Betid = b.Id, Betlinid = bl.Id, bl.Betalingsdato, bl.Indbetalingsdato, bl.Indbetalingsbelob, bl.Faknr, bl.Debitorkonto }; int AntalBetalinger = rsmbrshp.Count(); Console.WriteLine(string.Format("betalinger_til_rsmembership - AntalBetalinger {0} <----", AntalBetalinger)); if (rsmbrshp.Count() > 0) { foreach (var b in rsmbrshp) { if (saveBetid != b.Betid) // ny gruppe { saveBetid = b.Betid; var rec_bet = (from ub in p_dbData3060.Tblbet where ub.Id == b.Betid select ub).First(); rec_bet.Rsmembership = true; } // Do somthing here var qry = from f in p_dbData3060.Tblfak where f.Faknr == b.Faknr select f; if (qry.Count() == 1) { var fak = qry.First(); var critMedlem = new List <PropValuePair>(); var pairMedlem = PropValuePair.GenereteWhereElements("KeyStr", typeof(String), fak.Nr.ToString()); critMedlem.Add(pairMedlem); var taskMedlem = api.Query <Medlem>(critMedlem); taskMedlem.Wait(); var resultMedlem = taskMedlem.Result; var antalMedlem = resultMedlem.Count(); if (antalMedlem == 1) { var recMedlem = resultMedlem.First(); recMedlem.medlemtil = (DateTime)fak.Tildato; recMedlem.status = "Medlem"; var taskMedlemUpdate = api.Update(recMedlem); //Opdater Medlem } } } p_dbData3060.SaveChanges(); } return(AntalBetalinger); }
public int InsertGLDailyJournalLines(IOrderedQueryable <tblbankkonto> qrybankkonto) { var GLDailyJournalLines = 0; var crit = new List <PropValuePair>(); var pair = PropValuePair.GenereteWhereElements("KeyStr", typeof(String), "Dag"); crit.Add(pair); var task1 = m_api.Query <GLDailyJournalClient>(crit); task1.Wait(); var col = task1.Result; var rec_Master = col.FirstOrDefault(); foreach (var bk in qrybankkonto) { tblmobilepay mp = null; var qrymobilepay = from v in m_dbData3060.tblmobilepays where v.pid == bk.mobilepay_pid && (v.Bogfoert == null || v.Bogfoert == false) select v; var qrycount = qrymobilepay.Count(); if (qrycount == 1) { mp = qrymobilepay.First(); } else { continue; } GLDailyJournalLines++; GLDailyJournalLineClient jl = new GLDailyJournalLineClient() { Date = (DateTime)bk.dato, Text = bk.tekst, Account = "5850", }; if (bk.belob > 0) { jl.Debit = (double)bk.belob; } else { jl.Credit = -(double)bk.belob; } switch (mp.Type.ToUpper()) { case "SALG": jl.OffsetAccount = "1004"; break; case "REFUNDERING": jl.OffsetAccount = "1004"; break; case "GEBYR": jl.OffsetAccount = "4471"; break; case "OVERFØRSEL": jl.OffsetAccount = "5840"; break; default: jl.OffsetAccount = "9900"; break; } jl.SetMaster(rec_Master); var task2 = m_api.Insert(jl); task2.Wait(); var err = task2.Result; mp.Bogfoert = true; } m_dbData3060.SubmitChanges(); return(GLDailyJournalLines); }
public int export() { DateTime ExportFromDate = DateTime.Now.AddDays(-3); var crit = new List <PropValuePair>(); var pair = PropValuePair.GenereteWhereElements("KeyName", typeof(string), "Mobilepay"); crit.Add(pair); var taskQryBankStatment = m_api.Query <BankStatementClient>(crit); taskQryBankStatment.Wait(); var col = taskQryBankStatment.Result; if (col.Count() == 1) { ExportFromDate = col[0].LastTransaction; var DaysSlip = col[0].DaysSlip; ExportFromDate.AddDays(-DaysSlip); } ExportFromDate = DateTime.Now.AddDays(-100); //<<----------------------------- IOrderedQueryable <tblbankkonto> qrybankkonto = from w in m_dbData3060.tblbankkontos where w.bankkontoid == m_bankkontoid && (w.skjul == null || w.skjul == false) && w.dato >= ExportFromDate orderby w.dato select w; int antal = qrybankkonto.Count(); using (StringWriter sr = new StringWriter()) { /* * string ln = @"pid;dato;tekst;beløb"; * sr.WriteLine(ln); * * foreach (var b in qrybankkonto) * { * ln = ""; * ln += @"""" + b.pid.ToString() + @"""" + ";"; * ln += (b.dato == null) ? ";" : @"""" + ((DateTime)b.dato).ToString("dd.MM.yyyy") + @"""" + ";"; * ln += (b.tekst == null) ? ";" : @"""" + b.tekst.Replace(";"," ") + @"""" + ";"; * ln += (b.belob == null) ? ";" : @"""" + ((decimal)(b.belob)).ToString("0.00") + @""""; * sr.WriteLine(ln); * } * * //byte[] attachment = Encoding.Default.GetBytes(sr.ToString()); * byte[] attachment = Encoding.Unicode.GetBytes(sr.ToString()); * VouchersClient vc = new VouchersClient() * { * Text = "MobilePay", * Content = "Bankkontoudtog", * Fileextension = FileextensionsTypes.CSV, * VoucherAttachment = attachment, * }; * var taskInsertVouchers = m_api.Insert(vc); * taskInsertVouchers.Wait(); * var err = taskInsertVouchers.Result; */ var GLDailyJournalLines = InsertGLDailyJournalLines(qrybankkonto); return(GLDailyJournalLines); } }
async Task LoadGrid() { fromDate = txtFromDate.DateTime; toDate = txtToDate.DateTime; budgetGroup = cmbBudgetGroup.Text; grpWeek = chkGroupWeek.IsChecked.Value; grpPrevYear = chkGroupPrevYear.IsChecked.Value; fieldQtyPrev.Visible = grpPrevYear; fieldCostPrev.Visible = grpPrevYear; fieldSalesPrev.Visible = grpPrevYear; fieldQtyActualPrevBudDiff.Visible = grpPrevYear; fieldSalesActualPrevBudgetDiff.Visible = grpPrevYear; fieldCostActualPrevBudgetDiff.Visible = grpPrevYear; if (string.IsNullOrEmpty(budgetGroup)) { var msgText = FieldCannotBeEmpty(Uniconta.ClientTools.Localization.lookup("BudgetGroup")); UnicontaMessageBox.Show(msgText, Uniconta.ClientTools.Localization.lookup("Warning"), MessageBoxButton.OK); return; } if (fromDate == DateTime.MinValue) { var msgText = FieldCannotBeEmpty(Uniconta.ClientTools.Localization.lookup("FromDate")); UnicontaMessageBox.Show(msgText, Uniconta.ClientTools.Localization.lookup("Warning"), MessageBoxButton.OK); return; } if (toDate == DateTime.MinValue) { var msgText = FieldCannotBeEmpty(Uniconta.ClientTools.Localization.lookup("ToDate")); UnicontaMessageBox.Show(msgText, Uniconta.ClientTools.Localization.lookup("Warning"), MessageBoxButton.OK); return; } busyIndicator.IsBusy = true; List <PropValuePair> filter = new List <PropValuePair>(); if (fromDate != DateTime.MinValue) { var propValuePairFolder = PropValuePair.GenereteParameter("FromDate", typeof(string), Convert.ToString(fromDate.Ticks)); filter.Add(propValuePairFolder); } if (toDate != DateTime.MinValue) { var propValuePairFolder = PropValuePair.GenereteParameter("ToDate", typeof(string), Convert.ToString(toDate.Ticks)); filter.Add(propValuePairFolder); } if (budgetGroup != null) { var propValuePairFolder = PropValuePair.GenereteParameter("BudgetGroup", typeof(string), budgetGroup); filter.Add(propValuePairFolder); } var api = this.api; var CompanyId = api.CompanyId; var transTask = api.Query(new ProjectTransPivotClient(), new List <UnicontaBaseEntity>() { master }, filter); var trans = await transTask; if (trans == null) { return; } var len = trans.Length; var sort = new ProjectTransBudgetPivotSort(); Array.Sort(trans, sort); List <ProjectTransPivotClient> extras = null; if (showBudget) { var budget = await api.Query(new ProjectBudgetPivotClient(), new List <UnicontaBaseEntity>() { master }, filter); var key = new ProjectTransPivotClient(); foreach (var bc in budget) { key._Project = bc._Project; key._Date = bc._Date; key._PrCategory = bc._PrCategory; key._PayrollCategory = bc._PayrollCategory; key._Employee = bc.Employee; var idx = Array.BinarySearch(trans, key, sort); if (idx >= 0 && idx < len) { var t = trans[idx]; t._BudgetSales += bc._Sales; t._BudgetCost += bc._Cost; t._BudgetQty += bc._Qty; } else { var prTrans = new ProjectTransPivotClient() { _CompanyId = CompanyId, _BudgetSales = bc._Sales, _BudgetCost = bc._Cost, _BudgetQty = bc._Qty, _PrCategory = bc._PrCategory, _Project = bc._Project, _Date = bc._Date, _Employee = bc._Employee, _PayrollCategory = bc._PayrollCategory }; if (extras == null) { extras = new List <ProjectTransPivotClient>(); } extras.Add(prTrans); } } } if (grpPrevYear) { foreach (var p in filter) { if (p.Prop == "FromDate") { p.Arg = Convert.ToString(fromDate.AddYears(-1).Ticks); } if (p.Prop == "ToDate") { p.Arg = Convert.ToString(toDate.AddYears(-1).Ticks); } } var transTaskPrev = api.Query(new ProjectTransPivotClient(), new List <UnicontaBaseEntity>() { master }, filter); var transPrev = await transTaskPrev; foreach (var y in transPrev) { var prTrans = new ProjectTransPivotClient() { _CompanyId = CompanyId, _SalesPrev = y._Sales, _CostPrev = y._Cost, _QtyPrev = y._Qty, _PrCategory = y._PrCategory, _Project = y._Project, _Date = y._Date, _Employee = y._Employee, _PayrollCategory = y._PayrollCategory }; if (extras == null) { extras = new List <ProjectTransPivotClient>(); } extras.Add(prTrans); } } if (extras != null) { Array.Resize(ref trans, len + extras.Count); foreach (var sum in extras) { trans[len++] = sum; } Array.Sort(trans, sort); extras = null; } int start = 0; while (start < len) { int end; string ProjectNumber; if (master == null) { ProjectNumber = trans[start]._Project; for (end = start; (end < len && trans[end]._Project == ProjectNumber); end++) { ; } } else { ProjectNumber = ((Uniconta.DataModel.Project)master)._Number; end = len; } start = end; } #region Norm Calendar CalenderNormLst[] normLst = null; var pairCalendarLine = new PropValuePair[] { PropValuePair.GenereteWhereElements(nameof(TMEmpCalendarLineClient.Date), typeof(DateTime), String.Format("{0:d}..{1:d}", fromDate, toDate)) }; var tmEmpCalenderLineLst = await api.Query <TMEmpCalendarLineClient>(pairCalendarLine); if (tmEmpCalenderLineLst.Length > 0) { if (grpWeek) { var grpCalendarLst = tmEmpCalenderLineLst.GroupBy(x => new { x.Calendar, PeriodFirstDate = x.WeekMonday }).Select(x => new { x.Key, Hours = x.Sum(y => y.Hours) }); foreach (var rec in grpCalendarLst) { var normTrans = new CalenderNormLst() { CalendarId = rec.Key.Calendar, Date = rec.Key.PeriodFirstDate, NormQty = rec.Hours }; if (normLst == null) { normLst = new CalenderNormLst[] { normTrans } } ; else { Array.Resize(ref normLst, normLst.Length + 1); normLst[normLst.Length - 1] = normTrans; } } } else { var grpCalendarLst = tmEmpCalenderLineLst.GroupBy(x => new { x.Calendar, x.FirstDayOfMonth }).Select(x => new { Key = x.Key, Hours = x.Sum(y => y.Hours) }).ToList(); foreach (var rec in grpCalendarLst) { var normTrans = new CalenderNormLst() { CalendarId = rec.Key.Calendar, Date = rec.Key.FirstDayOfMonth, NormQty = rec.Hours }; if (normLst == null) { normLst = new CalenderNormLst[] { normTrans } } ; else { Array.Resize(ref normLst, normLst.Length + 1); normLst[normLst.Length - 1] = normTrans; } } } var normCalSort = new SortNormCalendar(); Array.Sort(normLst, normCalSort); var lstCalendarSetup = await api.Query <TMEmpCalendarSetupClient>(); var calSetupSort = new SortCalendarSetup(); Array.Sort(lstCalendarSetup, calSetupSort); var calenders = new List <TMEmpCalendarSetupClient>(10); var searchCalSetup = new TMEmpCalendarSetupClient(); var empNormLst = new List <ProjectTransPivotClient>(); var searchNorm = new CalenderNormLst(); foreach (var empl in employeeCache) { var curEmployee = empl._Number; calenders.Clear(); searchCalSetup.Employee = curEmployee; var posCalSetup = Array.BinarySearch(lstCalendarSetup, searchCalSetup, calSetupSort); if (posCalSetup < 0) { posCalSetup = ~posCalSetup; } while (posCalSetup < lstCalendarSetup.Length) { var s = lstCalendarSetup[posCalSetup++]; if (s.Employee != curEmployee) { break; } calenders.Add(s); } if (calenders.Count == 0) { continue; } if (grpWeek) { foreach (var rec in calenders.OrderBy(s => s.ValidFrom)) { var newStartDate = rec._ValidFrom != DateTime.MinValue && rec._ValidFrom > fromDate ? rec._ValidFrom : fromDate; var empStartDate = empl._Hired == DateTime.MinValue ? newStartDate : empl._Hired > newStartDate ? empl._Hired : newStartDate; var newEndDate = rec._ValidTo != DateTime.MinValue && rec._ValidTo < toDate ? rec._ValidTo : toDate; var empEndDate = empl._Terminated == DateTime.MinValue ? newEndDate : empl._Terminated < newEndDate ? empl._Terminated : newEndDate; var empFirstDayOfWk = FirstDayOfWeek(empStartDate); searchNorm.CalendarId = rec._Calendar; searchNorm.Date = empFirstDayOfWk; var pos = Array.BinarySearch(normLst, searchNorm, normCalSort); if (pos < 0) { pos = ~pos; } while (pos < normLst.Length) { var s = normLst[pos++]; if (s.CalendarId != rec._Calendar || s.Date > empEndDate) { break; } if (s.Date >= empStartDate && s.Date <= empEndDate) { var newTrans = new ProjectTransPivotClient() { _CompanyId = CompanyId, _Employee = curEmployee, _Date = s.Date, _NormQty = s.NormQty }; empNormLst.Add(newTrans); } } } } else { foreach (var rec in calenders.OrderBy(s => s.ValidFrom)) { var newStartDate = rec._ValidFrom != DateTime.MinValue && rec._ValidFrom > fromDate ? rec._ValidFrom : fromDate; var empStartDate = empl._Hired == DateTime.MinValue ? newStartDate : empl._Hired > newStartDate ? empl._Hired : newStartDate; var newEndDate = rec._ValidTo != DateTime.MinValue && rec._ValidTo < toDate ? rec._ValidTo : toDate; var empEndDate = empl._Terminated == DateTime.MinValue ? newEndDate : empl._Terminated < newEndDate ? empl._Terminated : newEndDate; var empFirstDayOfMonth = FirstDayOfMonth(empStartDate); var empLastDayOfMonth = LastDayOfMonth(empEndDate); int empFirstMth = 0, empLastMth = 0; if (empFirstDayOfMonth != empStartDate) { empFirstMth = empStartDate.Month; } if (empLastDayOfMonth != empEndDate) { empLastMth = empEndDate.Month; } searchNorm.CalendarId = rec._Calendar; searchNorm.Date = empFirstDayOfMonth; var pos = Array.BinarySearch(normLst, searchNorm, normCalSort); if (pos < 0) { pos = ~pos; } while (pos < normLst.Length) { var s = normLst[pos++]; if (s.CalendarId != rec._Calendar || s.Date > empEndDate) { break; } if (empFirstMth != 0 && s.Date.Month == empFirstMth) { var firstDayOfMonth = FirstDayOfMonth(empStartDate); var lastDayOfMonth = firstDayOfMonth.AddMonths(1).AddDays(-1); var hours = tmEmpCalenderLineLst.Where(x => x._Calendar == s.CalendarId && x.Date >= empStartDate && x.Date <= lastDayOfMonth).Sum(y => y._Hours); var newTrans = new ProjectTransPivotClient() { _CompanyId = CompanyId, _Employee = curEmployee, _Date = firstDayOfMonth, _NormQty = hours }; empNormLst.Add(newTrans); } else if (empLastMth != 0 && s.Date.Month == empLastMth) { var firstDayOfMonth = FirstDayOfMonth(empEndDate); var lastDayOfMonth = firstDayOfMonth.AddMonths(1).AddDays(-1); var hours = tmEmpCalenderLineLst.Where(x => x._Calendar == s.CalendarId && x.Date >= firstDayOfMonth && x.Date <= empEndDate).Sum(y => y._Hours); var newTrans = new ProjectTransPivotClient() { _CompanyId = CompanyId, _Employee = curEmployee, _Date = firstDayOfMonth, _NormQty = hours }; empNormLst.Add(newTrans); } else if (s.Date >= empStartDate && s.Date <= empEndDate) { var newTrans = new ProjectTransPivotClient() { _CompanyId = CompanyId, _Employee = curEmployee, _Date = s.Date, _NormQty = s.NormQty }; empNormLst.Add(newTrans); } } } } } if (empNormLst.Count > 0) { Array.Resize(ref trans, len + empNormLst.Count); foreach (var norm in empNormLst) { trans[len++] = norm; } Array.Sort(trans, sort); } } #endregion Norm Calendar pivotDgProjectPlanning.DataSource = trans as IList; if (!isPivotIsVisible) { pivotDgProjectPlanning.EndUpdate(); pivotDgProjectPlanning.Visibility = Visibility.Visible; isPivotIsVisible = true; } pivotDgProjectPlanning.RefreshData(); busyIndicator.IsBusy = false; }
private void OpretNyeKonti_Click(object sender, EventArgs e) { var api = UCInitializer.GetBaseAPI; foreach (var recNyKontoplan in this.karNyKontoplanBindingSource.DataSource as KarNyKontoplan) { recKontoplan recKontoplan = null; if (recNyKontoplan.SkalOprettes) { try { recKontoplan = (from x in Program.karKontoplan where x.Kontonr == recNyKontoplan.Kontonr select x).First(); var crit = new List <PropValuePair>(); var pair = PropValuePair.GenereteWhereElements("Account", typeof(String), recNyKontoplan.NytKontonr); crit.Add(pair); var taskQueryGLAccount = api.Query <GLAccountClient>(null, crit); taskQueryGLAccount.Wait(); var col = taskQueryGLAccount.Result; if (col.Count() == 0) { GLAccountClient recGLAccount = new GLAccountClient() { Account = recNyKontoplan.NytKontonr, Name = recNyKontoplan.Kontonavn, }; if (recKontoplan.Type == "Drift") { if (recKontoplan.DK == "1") { recGLAccount.AccountTypeEnum = Uniconta.DataModel.GLAccountTypes.Revenue; } else { recGLAccount.AccountTypeEnum = Uniconta.DataModel.GLAccountTypes.Expense; } } else { if (recKontoplan.DK == "0") { recGLAccount.AccountTypeEnum = Uniconta.DataModel.GLAccountTypes.Asset; } else { recGLAccount.AccountTypeEnum = Uniconta.DataModel.GLAccountTypes.Liability; } } var taskInsertGLAccount = api.Insert(recGLAccount); taskInsertGLAccount.Wait(); var err = taskInsertGLAccount.Result; } } catch { var ss = 1; } } } }
public void open() { var fileinfo = new FileInfo(m_path); if (!fileinfo.Exists) { return; } var api = UCInitializer.GetBaseAPI; recNyKontoplan rec; FileStream ts = new FileStream(m_path, FileMode.Open, FileAccess.Read, FileShare.None); string ln = null; Regex regexKontoplan = new Regex(@"""(.*?)"";|([^;]*);|(.*)$"); using (StreamReader sr = new StreamReader(ts, Encoding.Default)) { while ((ln = sr.ReadLine()) != null) { int i = 0; int iMax = 4; string[] value = new string[iMax]; foreach (Match m in regexKontoplan.Matches(ln)) { for (int j = 1; j <= 3; j++) { if (m.Groups[j].Success) { if (i < iMax) { value[i++] = m.Groups[j].ToString(); break; } } } } Boolean wSkalOprettes = false; if (value[1] != "x") { var crit = new List <PropValuePair>(); var pair = PropValuePair.GenereteWhereElements("Account", typeof(String), value[1]); crit.Add(pair); var task = api.Query <GLAccountClient>(null, crit); task.Wait(); var col = task.Result; if (col.Count() == 0) { wSkalOprettes = true; } } rec = new recNyKontoplan { Kontonr = Microsoft.VisualBasic.Information.IsNumeric(value[0]) ? int.Parse(value[0]) : (int?)null, NytKontonr = value[1], Kontonavn = value[2], SkalOprettes = wSkalOprettes }; this.Add(rec); } } }
static void Main(string[] args) { AskCredentials(out username, out password); #region Create session and log in // Create connection var connection = new UnicontaConnection(APITarget.Live); // Create session var session = new Session(connection); // Login user. The guid required here is the partner API key which you will get when you ask for it as a partner. var apiKey = new Guid("00000000-0000-0000-0000-000000000000"); if (apiKey.Equals(Guid.Empty)) { Console.WriteLine("You need to set the API key"); return; } var logged = session.LoginAsync(username, password, Uniconta.Common.User.LoginType.API, apiKey).Result; if (logged != Uniconta.Common.ErrorCodes.Succes) { Console.WriteLine("Login failed"); return; } #endregion #region Selecting a company for the session // Select the company for session, otherwise it will be default company in Uniconta var companies = session.GetCompanies().Result; var company = session.GetCompany(companies[0].CompanyId).Result; // Getting specific company var defaultCompany = session.User._DefaultCompany; // returns an ID of the company session.OpenCompany(defaultCompany, true); // true sets the default company for the session, not as a default company for the user #endregion #region API examples // Set the API you want to use. Query, Crud and other APIs with different functionality var api = new CrudAPI(session, company); var qapi = new QueryAPI(session, company); #endregion #region Create records // Insert example CrmProspectClient prospect = new CrmProspectClient(); prospect.Name = "UC corp."; prospect.CompanyRegNo = "12315151"; prospect.Address1 = "Tree 1, Branch 2"; // Single record insert var error = api.Insert(prospect).Result; Thread.Sleep(500); // This is just to make sure that insert is done before I request data var tempProspects = api.Query <CrmProspectClient>().Result; // This is without any filters and gets all the entities // This is local LINQ query, instead of using for loop or other loops to find correct entity var master = tempProspects.Where(pr => pr.Name == "UC corp.").FirstOrDefault(); var contacts = new List <ContactClient>(); ContactClient contact = new ContactClient(); contact.Name = "Jane Doe"; contact.Email = "*****@*****.**"; contact.SetMaster(master); contacts.Add(contact); contact = new ContactClient(); contact.Name = "John Doe"; contact.Email = "*****@*****.**"; contact.SetMaster(master); contacts.Add(contact); // Bulk insert, warning, dont insert too many records. Try to do it in batches. error = api.Insert(contacts).Result; #endregion #region Read records // Plain no filter query, gets all the records on this entity in session company. var debtorOrders = api.Query <DebtorOrderClient>().Result; // Just to show, what we got foreach (var creditor in debtorOrders) { Console.WriteLine("Order: " + creditor.OrderNumber + " - " + creditor.Account); } // Querying rows that are all related to master record. var orderLines = api.Query <DebtorOrderLineClient>(debtorOrders[0]).Result; // Setting up a filter DateTime fromDate = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1); DateTime toDate = DateTime.Today; var filter = new List <PropValuePair> { PropValuePair.GenereteWhereElements("Date", typeof(DateTime), $"{fromDate.ToString("d/M-yyyy", CultureInfo.InvariantCulture)}..{toDate.ToString("d/M-yyyy", CultureInfo.InvariantCulture)}"), PropValuePair.GenereteOrderByElement("Account", true) // This is sorting filter. }; var invoices = api.Query <DebtorInvoiceClient>(filter).Result; // Creating SQL filter filter = new List <PropValuePair>(); filter.Add(PropValuePair.GenereteWhere("Account <= '1234' and Name like 'Something'")); var debtorClients = api.Query <DebtorClient>(filter).Result; // Pagination filter var page = 0; var pageSize = 10; filter = new List <PropValuePair>(); filter.Add(PropValuePair.GenereteOrderByElement("RowId", false)); filter.Add(PropValuePair.GenereteSkipN(pageSize * page)); filter.Add(PropValuePair.GenereteTakeN(pageSize * (page + 1))); debtorClients = api.Query <DebtorClient>(filter).Result; #endregion #region Update records // Update example var prospects = api.Query <CrmProspect>().Result; prospects[0]._Address1 = "Tree 2, Branch 3"; error = api.Update(prospects[0]).Result; // Update example using streamingmanager to make sure only fields you updated will be updated prospects = api.Query <CrmProspect>().Result; var updProspect = prospects[0]; var originalProspect = StreamingManager.Clone(updProspect); updProspect._Address1 = "Tree 2, Branch 3"; error = api.Update(originalProspect, updProspect).Result; // And again, you can update in bulk. #endregion #region Delete records // Delete näide prospects = api.Query <CrmProspect>().Result; error = api.Delete(prospects[0]).Result; #endregion #region Additional possibilities // MultiCrud enables you to insert, update and delete at the same time // api.MultiCrud() // NoResponse if no response is required with these operations // api.InsertNoResponse(); // api.UpdateNoResponse(); // api.DeleteNoResponse(); // Cache SQLCache cache = api.CompanyEntity.GetCache(typeof(InvItemClient)); if (cache == null) { cache = api.CompanyEntity.LoadCache(typeof(InvItemClient), api).Result; } var item = cache.Get("1001"); // Gets all records from cache var invEntities = cache.GetRecords as InvItemClient[]; // Get specific items var specItems = invEntities.Where(i => i.KeyName.Contains("old") && i.Available > 3); #endregion #region UserDocuments // Insert UserDocuments var file = File.ReadAllBytes(@"PATH TO FILE"); UserDocsClient newUserDoc = new UserDocsClient { Created = DateTime.Now, DocumentType = FileextensionsTypes.DOCX, Text = "My file name for UC", _Data = file, }; newUserDoc.SetMaster(prospect); ErrorCodes errorCode = api.Insert(newUserDoc).Result; // Read UserDocuments var prospectDocuments = api.Query <UserDocsClient>(prospect).Result; if (prospectDocuments.Length != 0) { var readResult = api.Read(prospectDocuments[0]).Result; var fileBytes = prospectDocuments[0]._Data; if (prospectDocuments[0].DocumentType == FileextensionsTypes.DOCX) { File.WriteAllBytes(@"PATH TO FILE", fileBytes); } } #endregion /*Other APIs: * * GeneralLedger: * • ReportAPI * • BankStatementAPI * • DocumentAPI * • FinancialYearAPI * • PeriodTotalAPI * • PostingAPI * • StandardGLAccountAPI * DebtorCreditor: * • DebtorOrderAPI * • InvoiceAPI * • ReportAPI * • TransactionAPI * Inventory: * • PostingAPI * • ReportAPI * System: * • CompanyAPI * • NumberSerieAPI * • CompanyAccessAPI * • UserAPI * */ // Always try to close your session when done. session.LogOut(); }
private void karKartotekBindingNavigatorSaveItem_Click(object sender, EventArgs e) { var api = UCInitializer.GetBaseAPI; //var task = api.Query<CreditorClient>(); //task.Wait(); //var col1 = task.Result; foreach (var recKartotek in this.karKartotekBindingSource.DataSource as KarKartotek) { if (recKartotek.DK == "1") //Kreditor { var crit = new List <PropValuePair>(); var pair = PropValuePair.GenereteWhereElements("Account", typeof(String), recKartotek.Kontonr.ToString()); crit.Add(pair); var taskQueryCreditor = api.Query <CreditorClient>(crit); taskQueryCreditor.Wait(); var col = taskQueryCreditor.Result; if (col.Count() == 0) { CreditorClient recCreditor = new CreditorClient() { Account = recKartotek.Kontonr.ToString(), Name = recKartotek.Kontonavn, Address1 = recKartotek.Adresse, ZipCode = recKartotek.Postnr, City = recKartotek.Bynavn, ContactEmail = recKartotek.Email, CompanyRegNo = recKartotek.Cvrnr, PaymentMethod = "Kreditors bankkonto", //PaymentId = recKartotek.bankkonto, }; var taskInsertCreditor = api.Insert(recCreditor); taskInsertCreditor.Wait(); var err = taskInsertCreditor.Result; } } else //Debitor { var crit = new List <PropValuePair>(); var pair = PropValuePair.GenereteWhereElements("Account", typeof(String), recKartotek.Kontonr.ToString()); crit.Add(pair); var taskQueryDebtor = api.Query <DebtorClient>(crit); taskQueryDebtor.Wait(); var col = taskQueryDebtor.Result; if (col.Count() == 0) { DebtorClient recDebtor = new DebtorClient() { Account = recKartotek.Kontonr.ToString(), Name = recKartotek.Kontonavn, Address1 = recKartotek.Adresse, ZipCode = recKartotek.Postnr, City = recKartotek.Bynavn, ContactEmail = recKartotek.Email, CompanyRegNo = recKartotek.Cvrnr, }; var taskInsertDebtor = api.Insert(recDebtor); taskInsertDebtor.Wait(); var err = taskInsertDebtor.Result; } } } }
void localMenu_OnItemClicked(string ActionType) { var fromDate = txtDateFrm.DateTime; var toDate = txtDateTo.DateTime; var lin = dgVatReport.SelectedItem as VatReportLine; object[] param; switch (ActionType) { case "VatReportSpain": { List <VatReportLine> lst = (List <VatReportLine>)dgVatReport.ItemsSource; if (lst == null) { return; } var array = UnicontaClient.Pages.GL.Reports.VatSpain.calc(lst.ToArray()); param = new object[] { api, array }; AddDockItem(TabControls.VatReportSpain, param, "Modelo 303", null, closeIfOpened: true); break; } case "VatReportNorway": { if (vatReportSum == null) { return; } param = new object[] { vatReportSum, fromDate, toDate }; AddDockItem(TabControls.VatReportNorway, param, "Mva skattemeldingen", null, closeIfOpened: true); break; } case "VatReportDenmark": { if (vatReportSum == null) { return; } param = new object[] { api, this.vatReportSum, fromDate, toDate }; AddDockItem(TabControls.VatReportDenmark, param, "Momsopgørelse", null, closeIfOpened: true); break; } case "VatReportHolland": { if (vatReportSum == null) { return; } param = new object[] { vatReportSum, fromDate, toDate }; AddDockItem(TabControls.VatReportHolland, param, "BTW Aangifte", null, closeIfOpened: true); break; } case "VatReportEstonia": { if (vatReportSum == null) { return; } param = new object[] { vatReportSum, fromDate, toDate }; AddDockItem(TabControls.VatReportEstonia, param, "KM avaldus", null, closeIfOpened: true); break; } case "VatReportUnitedKingdom": { if (vatReportSum == null) { return; } param = new object[] { vatReportSum, fromDate, toDate }; AddDockItem(TabControls.VatReportUnitedKingdom, param, "VAT statement", null, closeIfOpened: true); break; } case "Transactions": if (lin?.Account != null) { var dt = PropValuePair.GenereteWhereElements("Date", fromDate, CompareOperator.GreaterThanOrEqual); dt.OrList[0].SecundaryValue = NumberConvert.ToString(toDate.Ticks); var filter = new PropValuePair[] { dt, PropValuePair.GenereteWhereElements("Account", lin.AccountNumber, CompareOperator.Equal), PropValuePair.GenereteWhereElements("Vat", lin.Vat != null ? lin.Vat._Vat : "null", CompareOperator.Equal) }; AddDockItem(TabControls.AccountsTransaction, new object[] { api, filter }, string.Format("{0}: {1}", Uniconta.ClientTools.Localization.lookup("Transactions"), lin.AccountNumber)); } break; case "VatReportIceland": { if (vatReportSum == null) { return; } param = new object[] { vatReportSum, fromDate, toDate }; AddDockItem(TabControls.VatReportIceland, param, "VAT statement", null, closeIfOpened: true); break; } default: break; } gridRibbon_BaseActions(ActionType); }
public async void GetVatOSS(DateTime fromDate, DateTime toDate) { SetDateTime(txtDateFrm, txtDateTo); busyIndicator.IsBusy = true; List <PropValuePair> propValPair = new List <PropValuePair>(); if (fromDate != DateTime.MinValue || toDate != DateTime.MinValue) { string filter; if (fromDate != DateTime.MinValue) { filter = String.Format("{0:d}..", fromDate); } else { filter = ".."; } if (toDate != DateTime.MinValue) { filter += String.Format("{0:d}", toDate); } var prop = PropValuePair.GenereteWhereElements("Date", typeof(DateTime), filter); propValPair.Add(prop); } if (glVatCache == null) { glVatCache = await api.LoadCache <Uniconta.DataModel.GLVat>(); } var vatEUList = glVatCache.Where(s => s._TypeSales == CreateVatOSSFile.VATTYPE_MOSS).Select(x => x._Vat).Distinct(); if (vatEUList != null && vatEUList.Count() > 0) { var strLst = string.Join(";", vatEUList); propValPair.Add(PropValuePair.GenereteWhereElements(nameof(DebtorInvoiceLines.Vat), typeof(string), strLst)); } var listOfDebInvLines = await api.Query <DebtorInvoiceLines>(propValPair); List <VatOSSTable> vatOSSlst = new List <VatOSSTable>(); if (listOfDebInvLines != null && listOfDebInvLines.Length != 0) { vatOSSlst = UpdateValues(listOfDebInvLines); busyIndicator.IsBusy = false; } else { var vatOSS = new VatOSSTable(); vatOSS._CompanyId = api.CompanyId; vatOSS._Date = DefaultFromDate; vatOSS.MOSSType = vatOSSReportType == 0 ? CreateVatOSSFile.MOSSTYPE_008 : vatOSSReportType == 1 ? CreateVatOSSFile.MOSSTYPE_021 : CreateVatOSSFile.MOSSTYPE_031; vatOSS._MOSSTypeName = GetMOSSTypeName(vatOSS.MOSSType); vatOSS.Compressed = true; vatOSSlst.Add(vatOSS); busyIndicator.IsBusy = false; compressed = true; } var sort = new VatOSSTableTypeSort(); vatOSSlst.Sort(sort); dgVatOSSGrid.ItemsSource = vatOSSlst; dgVatOSSGrid.Visibility = Visibility.Visible; }
public async void GetInvoiceLinesToIntraStat(DateTime fromDate, DateTime toDate, bool includeImport, bool includeExport) { var prop = PropValuePair.GenereteWhereElements("Item", typeof(string), "!null"); List <PropValuePair> propValPair = new List <PropValuePair>() { prop }; if (fromDate != DateTime.MinValue || toDate != DateTime.MinValue) { string filter; if (fromDate != DateTime.MinValue) { filter = String.Format("{0:d}..", fromDate); } else { filter = ".."; } if (toDate != DateTime.MinValue) { filter += String.Format("{0:d}", toDate); } prop = PropValuePair.GenereteWhereElements("Date", typeof(DateTime), filter); propValPair.Add(prop); } var intraList = new List <IntrastatClient>(); if (includeImport) { prop = PropValuePair.GenereteWhereElements("MovementType", typeof(int), "2"); propValPair.Insert(0, prop); var listIntraStatCred = await api.Query <IntrastatClient>(propValPair); var listCred = UpdateValues(listIntraStatCred, ImportOrExportIntrastat.Import, 1); propValPair.RemoveAt(0); // remove it so we can reuse the list if (listCred != null || listCred?.Count > 0) { intraList.AddRange(listCred); } } if (includeExport) { prop = PropValuePair.GenereteWhereElements("MovementType", typeof(int), "1"); propValPair.Add(prop); var listIntraStatDeb = await api.Query <IntrastatClient>(propValPair); var listDeb = UpdateValues(listIntraStatDeb, ImportOrExportIntrastat.Export, -1); if (listDeb != null || listDeb?.Count > 0) { intraList.AddRange(listDeb); } } if (intraList.Count <= 0) { UnicontaMessageBox.Show(Localization.lookup("zeroRecords"), Uniconta.ClientTools.Localization.lookup("Warning")); } dgIntraStatGrid.ItemsSource = intraList; dgIntraStatGrid.Visibility = Visibility.Visible; }
async public void InsertGLDailyJournalLines() { var api = UCInitializer.GetBaseAPI; //var col3 = await api.Query<NumberSerieClient>(); var crit = new List <PropValuePair>(); var pair = PropValuePair.GenereteWhereElements("KeyStr", typeof(String), "Dag"); crit.Add(pair); var col = await api.Query <GLDailyJournalClient>(null, crit); var rec_Master = col.FirstOrDefault(); var qryKladder = from k in Program.karKladder //where k.Bilag > 183 // <----------------------- orderby k.Bilag, k.Id select k; int antal = qryKladder.Count(); foreach (var k in qryKladder) { //if (k.Bilag > 3) // break; int refbilag = await InsertVouchersClients(k); GLDailyJournalLineClient jl = new GLDailyJournalLineClient() { Date = (DateTime)k.Dato, Voucher = (k.Bilag != null) ? (int)k.Bilag : 0, Text = k.Tekst, DocumentRef = refbilag, Vat = MomsKodeKonvertering(k.Momskode) }; if (!String.IsNullOrWhiteSpace(k.Afstemningskonto)) //Afstemningskonto er udfyldt { if (k.Belob > 0) { jl.Debit = (double)k.Belob; } else { jl.Credit = -(double)k.Belob; } } else //Afstemningskonto er ikke udfyldt { if (k.Belob > 0) { jl.Credit = (double)k.Belob; } else { jl.Debit = -(double)k.Belob; } } if (KarNyKontoplan.NytKontonr(k.Afstemningskonto) != string.Empty) { jl.Account = KarNyKontoplan.NytKontonr(k.Afstemningskonto); if (KarNyKontoplan.NytKontonr(k.Konto) != string.Empty) { jl.OffsetAccount = KarNyKontoplan.NytKontonr(k.Konto); } } else { if (KarNyKontoplan.NytKontonr(k.Konto) != string.Empty) { jl.Account = KarNyKontoplan.NytKontonr(k.Konto); } } jl.SetMaster(rec_Master); var err = await api.Insert(jl); if (err != ErrorCodes.Succes) { int xx = 1; } } }
async void LoadGrid() { budgetGroup = cmbBudgetGroup.Text; workSpace = cmbWorkSpace.Text; if (string.IsNullOrEmpty(budgetGroup)) { var msgText = string.Concat(Uniconta.ClientTools.Localization.lookup("FieldCannotBeEmpty"), " (", Uniconta.ClientTools.Localization.lookup("Field"), ": ", Uniconta.ClientTools.Localization.lookup("BudgetGroup"), ")"); UnicontaMessageBox.Show(msgText, Uniconta.ClientTools.Localization.lookup("Warning"), MessageBoxButton.OK); return; } busyIndicator.IsBusy = true; if (projects == null) { projects = await api.LoadCache <Uniconta.DataModel.Project>(); } if (debtors == null) { debtors = await api.LoadCache <Uniconta.DataModel.Debtor>(); } var projArr = projects.ToArray(); var lst = new List <TMPlanningCheckLocal>(1000); var search = new Uniconta.DataModel.Project(); var sort = new SortProjectDebtor(); int pos; Array.Sort(projArr, sort); foreach (var rec in debtors) { if (rec._Blocked) { continue; } search._DCAccount = rec._Account; pos = Array.BinarySearch(projArr, search, sort); if (pos < 0) { pos = ~pos; } bool found = false; while (pos < projArr.Length) { var s = projArr[pos++]; if (s._DCAccount != rec._Account) { break; } if (s._Blocked || s._Phase == ProjectPhase.Completed || s._Phase == ProjectPhase.ReportedAsFinished || s._Phase == ProjectPhase.Paused) { continue; } found = true; break; } if (!found) { var cur = new TMPlanningCheckLocal() { _CompanyId = api.CompanyId, _Debtor = rec._Account, _ErrorInfo = string.Concat("1. ", Uniconta.ClientTools.Localization.lookup("DebtorNoActiveProjects")) }; lst.Add(cur); } } List <PropValuePair> pairBudget = new List <PropValuePair>(); pairBudget.Add(PropValuePair.GenereteWhereElements(nameof(ProjectBudget._Group), typeof(string), budgetGroup)); var projBudgetArr = await api.Query <ProjectBudget>(pairBudget); var master = budgetGrpCache.Get(budgetGroup) as ProjectBudgetGroup; var budgetLineArr = await api.Query <ProjectBudgetLine>(master); List <PropValuePair> pairTask = new List <PropValuePair>(); pairTask.Add(PropValuePair.GenereteWhereElements(nameof(ProjectTaskClient.WorkSpace), typeof(string), workSpace)); var projTaskArr = await api.Query <ProjectTaskClient>(pairTask); var searchBud = new ProjectBudget(); var sortBud = new SortProjectBudget(); Array.Sort(projBudgetArr, sortBud); var searchBudLine = new ProjectBudgetLine(); var sortBudLine = new SortProjectBudgetLine(); int posBudLine; Array.Sort(budgetLineArr, sortBudLine); var searchTask = new ProjectTask(); var sortTask = new SortProjectTask(); int posTask; Array.Sort(projTaskArr, sortTask); foreach (var rec in projects) { if (rec._Blocked || rec._Phase == ProjectPhase.Completed || rec._Phase == ProjectPhase.ReportedAsFinished || rec._Phase == ProjectPhase.Paused) { continue; } searchBud._Project = rec._Number; pos = Array.BinarySearch(projBudgetArr, searchBud, sortBud); if (pos < 0) { pos = ~pos; } bool found = false; while (pos < projBudgetArr.Length) { var s = projBudgetArr[pos++]; if (s._Project != rec._Number) { break; } #region BudgetLine searchBudLine._Project = rec._Number; posBudLine = Array.BinarySearch(budgetLineArr, searchBudLine, sortBudLine); if (posBudLine < 0) { posBudLine = ~posBudLine; } bool foundLine = false; while (posBudLine < budgetLineArr.Length) { var sLine = budgetLineArr[posBudLine++]; if (sLine._Project != rec._Number) { break; } foundLine = true; break; } if (!foundLine) { var cur = new TMPlanningCheckLocal() { _CompanyId = api.CompanyId, _Project = rec._Number, _ErrorInfo = string.Concat("2. ", Uniconta.ClientTools.Localization.lookup("ProjectMissingBudget")) }; lst.Add(cur); } #endregion found = true; } if (api.CompanyEntity.ProjectTask && workSpace != null) { searchTask._Project = rec._Number; posTask = Array.BinarySearch(projTaskArr, searchTask, sortTask); if (posTask < 0) { posTask = ~posTask; } bool foundLine = false; while (posTask < projTaskArr.Length) { var projTask = projTaskArr[posTask++]; if (projTask._Project != rec._Number) { break; } foundLine = true; break; } if (!foundLine) { var cur = new TMPlanningCheckLocal() { _CompanyId = api.CompanyId, _Project = rec._Number, _ErrorInfo = string.Concat("3. ", Uniconta.ClientTools.Localization.lookup("ProjectNoActiveTasks")) }; lst.Add(cur); } } } dgTMPlanningCheckGrid.ItemsSource = lst; if (dgTMPlanningCheckGrid.tableView != null) { dgTMPlanningCheckGrid.ShowTotalSummary(); dgTMPlanningCheckGrid.GroupBy("ErrorInfo"); dgTMPlanningCheckGrid.Visibility = Visibility.Visible; } busyIndicator.IsBusy = false; }
public void InsertSalgsfakturaer() { int?lastFakid = null; DebtorOrderClient recOrder = null; var rec_regnskab = Program.qryAktivRegnskab(); var qrySFak = from sfv in Program.karFakturavarer_s join sf in Program.karFakturaer_s on new { fakid = sfv.Fakid } equals new { fakid = sf.fakid } // where sf.faknr != 0 && sf.faktype == 0 where sf.faknr > 28 && sf.faktype == 0 orderby sfv.Fakid, sfv.Line select new { Regnskabid = rec_regnskab.Rid, Sk = "S", Fakid = sfv.Fakid, Faknr = sf.faknr, Dato = sf.dato, forfdato = sf.forfdato, debitornr = sf.debitornr, Faklinnr = sfv.Line, Varenr = sfv.Varenr, Tekst = sfv.VareTekst, Konto = sfv.Bogfkonto, Momskode = KarKontoplan.getMomskode(sfv.Bogfkonto), Antal = sfv.Antal, Enhed = sfv.Enhed, Pris = sfv.Pris, Rabat = sfv.Rabat, Moms = sfv.Moms, Nettobelob = sfv.Nettobelob, Bruttobelob = sfv.Bruttobelob, }; int antal = qrySFak.Count(); var api = UCInitializer.GetBaseAPI; //var col3 = await api.Query<DebtorOrderClient>(); //var col4 = await api.Query<DebtorOrderLineClient>(); foreach (var s in qrySFak) { if ((!(s.Fakid == 0)) && (lastFakid != s.Fakid)) { try { var crit = new List <PropValuePair>(); var pair = PropValuePair.GenereteWhereElements("OrderNumber", typeof(int), s.Fakid.ToString()); crit.Add(pair); var taskDebtorOrder = api.Query <DebtorOrderClient>(null, crit); taskDebtorOrder.Wait(); var col = taskDebtorOrder.Result; if (col.Count() == 0) { recOrder = new DebtorOrderClient() { OrderNumber = s.Fakid, Account = s.debitornr.ToString(), InvoiceDate = s.Dato, DeliveryDate = s.Dato, }; var taskInsertDebtorOrder = api.Insert(recOrder); taskInsertDebtorOrder.Wait(); var err = taskInsertDebtorOrder.Result; } else { recOrder = col[0]; } } catch { } } DebtorOrderLineClient recOrderLine = new DebtorOrderLineClient() { Text = s.Tekst, Qty = (double)s.Antal, Price = (double)s.Pris, PostingAccount = KarNyKontoplan.NytKontonr(s.Konto), Vat = MomsKodeKonvertering(s.Momskode) }; recOrderLine.SetMaster(recOrder); var taskInsertDebtorOrderLine = api.Insert(recOrderLine); taskInsertDebtorOrderLine.Wait(); var err1 = taskInsertDebtorOrderLine.Result; } }
public void InsertFinansJournal(MimeMessage message, int DocumentRef, clsParam objParam) { var From = message.From.ToString(); From = ExtractEmails(From); var Date = message.Date.DateTime; var Subject = objParam.Tekst; if (string.IsNullOrEmpty(Subject)) { Subject = message.Subject; } string wAccount = null; if (!string.IsNullOrEmpty(objParam.Konto)) { try { wAccount = (from c in this.m_GLAccounts where c.Account == objParam.Konto select c.Account).First(); } catch (Exception) { wAccount = "9900"; //Fejlkonto } } string wOffsetAccount = null; if (!string.IsNullOrEmpty(objParam.Modkonto)) { try { wOffsetAccount = (from c in this.m_GLAccounts where c.Account == objParam.Modkonto select c.Account).First(); } catch (Exception) { wOffsetAccount = "9900"; //Fejlkonto } } var crit = new List <PropValuePair>(); var pair = PropValuePair.GenereteWhereElements("KeyStr", typeof(String), "Dag"); crit.Add(pair); var task = m_api.Query <GLDailyJournalClient>(crit); task.Wait(); var col = task.Result; var rec_Master = col.FirstOrDefault(); GLDailyJournalLineClient jl = new GLDailyJournalLineClient() { Date = Date, Text = Subject, DocumentRef = DocumentRef, AccountType = objParam.Kontotype, Account = wAccount, OffsetAccountType = objParam.Modkontotype, OffsetAccount = wOffsetAccount, Debit = objParam.Debit, Credit = objParam.Kredit, }; jl.SetMaster(rec_Master); var task2 = m_api.Insert(jl); task2.Wait(); var err = task2.Result; if (err != ErrorCodes.Succes) { //int xx = 1; } }
public void InsertKøbsfakturaer() { int?lastFakid = null; CreditorOrderClient recOrder = null; var rec_regnskab = Program.qryAktivRegnskab(); var qryKFak = from kfv in Program.karFakturavarer_k join kf in Program.karFakturaer_k on new { fakid = kfv.Fakid } equals new { fakid = kf.fakid } where kf.faknr != 0 && (kf.faktype == 2 || kf.faktype == 3) orderby kfv.Fakid, kfv.Line select new { Regnskabid = rec_regnskab.Rid, Sk = "K", Fakid = kfv.Fakid, Faknr = kf.faknr, Dato = kf.dato, kreditornr = kf.kreditornr, Faklinnr = kfv.Line, Varenr = kfv.Varenr, Tekst = kfv.VareTekst, Konto = kfv.Bogfkonto, Momskode = KarKontoplan.getMomskode(kfv.Bogfkonto), Faktype = kf.faktype, Antal = kfv.Antal, Enhed = kfv.Enhed, Pris = kfv.Pris, Rabat = kfv.Rabat, Moms = kfv.Moms, Nettobelob = kfv.Nettobelob, Bruttobelob = kfv.Bruttobelob, }; int antal = qryKFak.Count(); var api = UCInitializer.GetBaseAPI; //var col3 = await api.Query<CreditorOrderClient>(); //var col4 = await api.Query<CreditorOrderLineClient>(); foreach (var k in qryKFak) { if ((!(k.Fakid == 0)) && (lastFakid != k.Fakid)) { try { var crit = new List <PropValuePair>(); var pair = PropValuePair.GenereteWhereElements("OrderNumber", typeof(int), k.Fakid.ToString()); crit.Add(pair); var taskCreditorOrder = api.Query <CreditorOrderClient>(null, crit); taskCreditorOrder.Wait(); var col = taskCreditorOrder.Result; if (col.Count() == 0) { recOrder = new CreditorOrderClient() { OrderNumber = k.Fakid, Account = k.kreditornr.ToString(), InvoiceDate = k.Dato, DeliveryDate = k.Dato, }; var taskInsertCreditorOrder = api.Insert(recOrder); taskInsertCreditorOrder.Wait(); var err = taskInsertCreditorOrder.Result; } else { recOrder = col[0]; } } catch { } } double wAntal = 0; if (k.Faktype == 2) //Købsfaktura { if (k.Antal != null) { wAntal = (double)k.Antal; } else { wAntal = 1; } } else if (k.Faktype == 3) //Købskreditnota { if (k.Antal != null) { wAntal = -(double)k.Antal; } else { wAntal = -11; } } CreditorOrderLineClient recOrderLine = new CreditorOrderLineClient() { Text = k.Tekst, Qty = k.Antal != null ? (double)k.Antal : 1, Price = (double)k.Pris, PostingAccount = KarNyKontoplan.NytKontonr(k.Konto), Vat = MomsKodeKonvertering(k.Momskode) }; recOrderLine.SetMaster(recOrder); var taskInsertCreditorOrderLine = api.Insert(recOrderLine); taskInsertCreditorOrderLine.Wait(); var err1 = taskInsertCreditorOrderLine.Result; } }
async void calc(DateTime fromDate, DateTime toDate) { SetBusy(); var employee = (master as Uniconta.DataModel.Employee)?._Number; var propValuePairList = new List <PropValuePair>() { PropValuePair.GenereteWhereElements("Date", typeof(DateTime), string.Concat(fromDate.ToShortDateString(), "..", toDate.ToShortDateString())), PropValuePair.GenereteWhereElements("Deleted", typeof(int), "0") }; var invoiceHeaders = await api.Query <DebtorInvoiceClient>(dgEmployeeCommissionGrid.masterRecords, propValuePairList); var commlstLine = new List <EmployeeCommissionClient>(1000); var commlstHead = new List <EmployeeCommissionClient>(1000); var commAll = (IEnumerable <EmployeeCommissionClient>)dgEmployeeCommissionGrid.ItemsSource; foreach (var rec in commAll) { if (rec._Disabled) { continue; } if (rec._FromDate != DateTime.MinValue && rec._FromDate > toDate) { continue; } if (rec._ToDate != DateTime.MinValue && rec._ToDate < fromDate) { continue; } if (employee != null && employee != rec._Employee) { continue; } if (rec._PerLine) { commlstLine.Add(rec); } else { commlstHead.Add(rec); } } var sort = new EmployeeCommissionClientSort(); commlstLine.Sort(sort); commlstHead.Sort(sort); var debtors = api.GetCache(typeof(Uniconta.DataModel.Debtor)); var calComs = new List <CalCommissionClient>(); if (commlstLine.Count > 0) { propValuePairList.RemoveAt(1); // remove Deleted propValuePairList.Add(PropValuePair.GenereteWhereElements("MovementType", typeof(int), "1")); var trans = await api.Query <InvTransClient>(dgEmployeeCommissionGrid.masterRecords, propValuePairList); //sandt if (trans != null) { var invItems = api.GetCache(typeof(Uniconta.DataModel.InvItem)); // lets sort invoices so we can find employee on invoice header var invSort = new InvoiceSort(); DebtorInvoiceClient invKey = null; if (invoiceHeaders != null && invoiceHeaders.Length > 0) { Array.Sort(invoiceHeaders, invSort); invKey = new DebtorInvoiceClient(); } foreach (var tran in trans) { var item = tran._Item; var acc = tran._DCAccount; var emp = tran._Employee; string debGroup = null; string itemGroup = null; if (item != null) { var inv = (InvItem)invItems.Get(item); itemGroup = inv?._Group; } if (acc != null) { var deb = (Debtor)debtors.Get(acc); debGroup = deb?._Group; } if (emp == null && invKey != null) { invKey._InvoiceNumber = tran._InvoiceNumber; invKey._DCAccount = tran._DCAccount; invKey._Date = tran._Date; var pos = Array.BinarySearch(invoiceHeaders, invKey, invSort); if (pos >= 0 && pos < invoiceHeaders.Length) { var rec = invoiceHeaders[pos]; emp = tran._Employee = rec._Employee; } } foreach (var c in commlstLine) { var cmp = string.Compare(c._Employee, emp); if (cmp > 0) { break; } if (cmp == 0 && CompareKey(c._Item, item) && CompareKey(c._Account, acc) && CompareKey(c._ItemGroup, itemGroup) && CompareKey(c._DebGroup, debGroup)) { var calculatedCommission = CalculateCommissionInvTran(tran, c); if (calculatedCommission == null || calculatedCommission._Commission == 0) { continue; } calComs.Add(calculatedCommission); if (!c._KeepLooking) { break; } } } } } } if (commlstHead.Count > 0 && invoiceHeaders != null) { foreach (var it in invoiceHeaders) { string debGroup = null; var emp = it._Employee; var acc = it._DCAccount; if (acc != null) { var deb = (Debtor)debtors.Get(acc); debGroup = deb?._Group; } foreach (var c in commlstHead) { var cmp = string.Compare(c._Employee, emp); if (cmp > 0) { break; } if (cmp == 0 && CompareKey(c._Account, acc) && CompareKey(c._DebGroup, debGroup)) { var calculatedCommission = CalculateCommissionDebInvoice(it, c); if (calculatedCommission == null || calculatedCommission._Commission == 0) { continue; } calComs.Add(calculatedCommission); if (!c._KeepLooking) { break; } } } } } ClearBusy(); if (calComs.Count == 0) { UnicontaMessageBox.Show(Localization.lookup("NoRecordExport"), Uniconta.ClientTools.Localization.lookup("Message")); return; } var arr = calComs.ToArray(); Array.Sort(arr, new CalCommissionClientSort()); AddDockItem(TabControls.CalculatedCommissionPage, new object[] { arr }, Uniconta.ClientTools.Localization.lookup("CalculateCommission"), null, true); }
async public void InsertGLDailyJournalLinesYearEnd() { var api = UCInitializer.GetBaseAPI; var col3 = await api.Query <NumberSerieClient>(); var crit = new List <PropValuePair>(); var pair = PropValuePair.GenereteWhereElements("KeyStr", typeof(String), "Dag"); crit.Add(pair); var col = await api.Query <GLDailyJournalClient>(null, crit); var rec_Master = col.FirstOrDefault(); var qryPosteringer = from p in Program.karPosteringer where p.Bilag == 0 && (p.Tekst.StartsWith("ÅP:") || p.Tekst.StartsWith("EP:")) orderby p.Konto, p.Nr select p; int antal = qryPosteringer.Count(); DateTime Dato_last = DateTime.Today; int Konto_last = 0; decimal Nettobeløb_sum = 0; foreach (var p in qryPosteringer) { if (p.Konto != Konto_last) { if (Konto_last != 0) { GLDailyJournalLineClient jl = new GLDailyJournalLineClient() { Date = Dato_last, Voucher = 9999, Text = "Primo SummaSummarum", Account = KarNyKontoplan.NytKontonr(Konto_last) }; if (Nettobeløb_sum > 0) { jl.Debit = (double)Nettobeløb_sum; } else { jl.Credit = -(double)Nettobeløb_sum; } jl.SetMaster(rec_Master); var err = await api.Insert(jl); } Nettobeløb_sum = 0; } Dato_last = p.Dato; Konto_last = p.Konto; Nettobeløb_sum += p.Nettobeløb; } if (antal > 0) { GLDailyJournalLineClient jl = new GLDailyJournalLineClient() { Date = Dato_last, Voucher = 9999, Text = "Primo SummaSummarum", Account = KarNyKontoplan.NytKontonr(Konto_last) }; if (Nettobeløb_sum > 0) { jl.Debit = (double)Nettobeløb_sum; } else { jl.Credit = -(double)Nettobeløb_sum; } jl.SetMaster(rec_Master); var err = await api.Insert(jl); } }
public static async void GenerateOIOXml(CrudAPI api, DCInvoice inv) { var Comp = api.CompanyEntity; var InvCache = api.GetCache(typeof(Uniconta.DataModel.InvItem)) ?? await api.LoadCache(typeof(Uniconta.DataModel.InvItem)); var VatCache = api.GetCache(typeof(Uniconta.DataModel.GLVat)) ?? await api.LoadCache(typeof(Uniconta.DataModel.GLVat)); //SystemInfo.Visible = true; int countErr = 0; SaveFileDialog saveDialog = null; Uniconta.API.DebtorCreditor.InvoiceAPI Invapi = new Uniconta.API.DebtorCreditor.InvoiceAPI(api); var listPropval = new List <PropValuePair>() { PropValuePair.GenereteWhereElements("InvoiceNumber", inv._InvoiceNumber, CompareOperator.Equal), PropValuePair.GenereteWhereElements("DCAccount", inv._DCAccount, CompareOperator.Equal), PropValuePair.GenereteWhereElements("Date", inv._Date, CompareOperator.Equal) }; var FindDebInvLocal = await api.Query <DebtorInvoiceClient>(listPropval); var invClient = FindDebInvLocal.FirstOrDefault(); if (invClient == null) { return; } var Debcache = Comp.GetCache(typeof(Debtor)) ?? await api.LoadCache(typeof(Debtor)); var debtor = (Debtor)Debcache.Get(invClient._DCAccount); if (debtor == null || !debtor._InvoiceInXML || invClient.SendTimeOIO != DateTime.MinValue) { if (!debtor._InvoiceInXML) { UnicontaMessageBox.Show("Faktura i OIOUBL er ikke sat til denne debitor", Uniconta.ClientTools.Localization.lookup("Warning")); } return; } var InvTransInvoiceLines = (DebtorInvoiceLines[])await Invapi.GetInvoiceLines(invClient, new DebtorInvoiceLines()); //TODO:Test fakturering var layoutGroupCache = api.GetCache(typeof(DebtorLayoutGroup)) ?? await api.LoadCache(typeof(DebtorLayoutGroup)); Contact contactPerson = null; if (invClient._ContactRef != 0) { var Contacts = api.GetCache(typeof(Uniconta.DataModel.Contact)) ?? await api.LoadCache(typeof(Uniconta.DataModel.Contact)); foreach (var contact in (Uniconta.DataModel.Contact[])Contacts.GetRecords) { if (contact.RowId == invClient._ContactRef) { contactPerson = contact; break; } } } DebtorOrders.SetDeliveryAdress(invClient, debtor, api); Debtor deliveryAccount; if (invClient._DeliveryAccount != null) { deliveryAccount = (Debtor)Debcache.Get(invClient._DeliveryAccount); } else { deliveryAccount = null; } WorkInstallation workInstallation = null; if (invClient._Installation != null) { var workInstallCache = api.GetCache(typeof(Uniconta.DataModel.WorkInstallation)) ?? await api.LoadCache(typeof(Uniconta.DataModel.WorkInstallation)); workInstallation = (WorkInstallation)workInstallCache.Get(invClient._Installation); } CreationResult result; if (Comp._CountryId == CountryCode.Norway || Comp._CountryId == CountryCode.Netherlands) { result = EHF.GenerateEHFXML(Comp, debtor, deliveryAccount, invClient, InvTransInvoiceLines, InvCache, VatCache, null, contactPerson); } else { var attachments = await FromXSDFile.OIOUBL.ExportImport.Attachments.CollectInvoiceAttachments(invClient, api); result = Uniconta.API.DebtorCreditor.OIOUBL.GenerateOioXML(Comp, debtor, deliveryAccount, invClient, InvTransInvoiceLines, InvCache, VatCache, null, contactPerson, attachments, layoutGroupCache, workInstallation); } bool createXmlFile = true; var errorInfo = ""; if (result.HasErrors) { countErr++; createXmlFile = false; foreach (FromXSDFile.OIOUBL.ExportImport.PrecheckError error in result.PrecheckErrors) { errorInfo += error.ToString() + "\n"; } } if (result.Document != null && createXmlFile) { string invoice = Uniconta.ClientTools.Localization.lookup("Invoice"); saveDialog = Uniconta.ClientTools.Util.UtilDisplay.LoadSaveFileDialog; saveDialog.FileName = string.Format("{0}_{1}", invoice, invClient.InvoiceNumber); saveDialog.Filter = "XML-File | *.xml"; bool?dialogResult = saveDialog.ShowDialog(); if (dialogResult != true) { return; } var filename = saveDialog.FileName; result.Document.Save(filename); await Invapi.MarkSendInvoiceOIO(invClient); invClient.SendTimeOIO = BasePage.GetSystemDefaultDate(); } //invClient.NotifySystemInfoSet(); //if (countErr != 0) // UnicontaMessageBox.Show(Uniconta.ClientTools.Localization.lookup(String.Format("Couldn't create file for {0} invoice(s). Please check System info column.", countErr))); if (countErr != 0 && !string.IsNullOrWhiteSpace(errorInfo)) { UnicontaMessageBox.Show(errorInfo, Uniconta.ClientTools.Localization.lookup("Error")); } }
private void UpdatePrices() { var cwUpdateBjt = new CwCreateUpdateBudget(api, 1); #if !SILVERLIGHT cwUpdateBjt.DialogTableId = 2000000073; #endif cwUpdateBjt.Closed += async delegate { if (cwUpdateBjt.DialogResult == true) { DateTime dFromDateUpd = CwCreateUpdateBudget.FromDate; DateTime dToDateUpd = CwCreateUpdateBudget.ToDate; string dEmplNumber = CwCreateUpdateBudget.Employee; string dProject = CwCreateUpdateBudget.Project; string dBudgetGroup = CwCreateUpdateBudget.Group; string dBudgetComment = CwCreateUpdateBudget.Comment; if (string.IsNullOrEmpty(CwCreateUpdateBudget.Group)) { UnicontaMessageBox.Show(FieldCannotBeEmpty(Uniconta.ClientTools.Localization.lookup("BudgetGroup")), Uniconta.ClientTools.Localization.lookup("Warning"), MessageBoxButton.OK); return; } if (dFromDateUpd == DateTime.MinValue) { UnicontaMessageBox.Show(FieldCannotBeEmpty(Uniconta.ClientTools.Localization.lookup("FromDate")), Uniconta.ClientTools.Localization.lookup("Warning"), MessageBoxButton.OK); return; } if (dToDateUpd == DateTime.MinValue) { UnicontaMessageBox.Show(FieldCannotBeEmpty(Uniconta.ClientTools.Localization.lookup("ToDate")), Uniconta.ClientTools.Localization.lookup("Warning"), MessageBoxButton.OK); return; } var master = new List <UnicontaBaseEntity>(); ProjectBudgetGroup budgetGrp = budgetGroupCache.Get(dBudgetGroup); if (budgetGrp._Blocked) { UnicontaMessageBox.Show(string.Format(Uniconta.ClientTools.Localization.lookup("IsBlockedOBJ"), Uniconta.ClientTools.Localization.lookup("Budget")), Uniconta.ClientTools.Localization.lookup("Warning"), MessageBoxButton.OK); return; } master.Add(budgetGrp); busyIndicator.IsBusy = true; if (!string.IsNullOrEmpty(dEmplNumber)) { master.Add(employeeCache.Get(dEmplNumber)); } if (!string.IsNullOrEmpty(dProject)) { master.Add(projectCache.Get(dProject)); } List <PropValuePair> pairTrans = new List <PropValuePair>(); pairTrans.Add(PropValuePair.GenereteWhereElements(nameof(ProjectBudgetLineClient._Date), typeof(DateTime), String.Format("{0:d}..{1:d}", dFromDateUpd, dToDateUpd))); var empPriceLst = await api.Query <EmpPayrollCategoryEmployeeClient>(master, pairTrans); var budgetLineLst = await api.Query <ProjectBudgetLineClient>(master, pairTrans); var cntUpdate = 0; var tmHelper = new TMJournalLineHelper(api); foreach (var rec in budgetLineLst) { cntUpdate++; var prices = tmHelper.GetEmplPrice(empPriceLst, payrollCache, projGroupCache, employeeCache?.Get(rec._Employee), projectCache.Get(rec._Project), rec._Date, rec._PayrollCategory); rec._CostPrice = prices.Item1; rec._SalesPrice = prices.Item2; rec._Text = string.Concat("(", TMJournalLineHelper.GetTimeStamp(), ") ", Uniconta.ClientTools.Localization.lookup("PriceUpdate")); } ErrorCodes res; if (cntUpdate > 0) { res = await api.Update(budgetLineLst); } busyIndicator.IsBusy = false; UnicontaMessageBox.Show(string.Format(Uniconta.ClientTools.Localization.lookup("RecordsUpdated"), cntUpdate, string.Empty), Uniconta.ClientTools.Localization.lookup("Information"), MessageBoxButton.OK); } }; cwUpdateBjt.Show(); }