/// <summary> /// Sets a cell value with a date /// </summary> /// <param name="spreadsheet">Spreadsheet to use</param> /// <param name="worksheet">Worksheet to use</param> /// <param name="columnIndex">Index of the column</param> /// <param name="rowIndex">Index of the row</param> /// <param name="datetimeValue">DateTime value</param> /// <param name="styleIndex">Style to use</param> /// <param name="save">Save the worksheet</param> /// <returns>True if succesful</returns> public static bool SetCellValue(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet, uint columnIndex, uint rowIndex, System.DateTime datetimeValue, uint?styleIndex, bool save = true) { #if EN_US_CULTURE string columnValue = datetimeValue.ToOADate().ToString(); #else string columnValue = datetimeValue.ToOADate().ToString().Replace(",", "."); #endif return(SetCellValue(spreadsheet, worksheet, columnIndex, rowIndex, DocumentFormat.OpenXml.Spreadsheet.CellValues.Date, columnValue, styleIndex, save)); }
static void formatXaxis(ref GraphPane gPane) { DateTime minX = new DateTime(DateTime.Now.Year, 1, 1); DateTime maxX = new DateTime(DateTime.Now.Year, 12,31); double rangeX; rangeX = maxX.ToOADate() - minX.ToOADate(); gPane.Legend.Position = LegendPos.InsideTopRight; gPane.XAxis.Scale.Min = minX.ToOADate() - (.025 * rangeX); gPane.XAxis.Scale.Max = maxX.ToOADate() + (.025 * rangeX); gPane.XAxis.Scale.MajorUnit = ZedGraph.DateUnit.Month; gPane.XAxis.Scale.MinorUnit = ZedGraph.DateUnit.Day; gPane.XAxis.Scale.Format = "MMMM"; }
public string SQLDate(System.DateTime D) { string result = null; int Y = 0; if ((D != null) && (Convert.ToDouble(D.ToOADate()) > 0)) { result = "convert(datetime," + (Convert.ToDouble(D.ToOADate()) - 2) + ")"; } else { result = "NULL"; } return(result); }
public static double CalculatePTYield(DateTime settle, DateTime maturity, decimal price, decimal coupon) { var singleton = FinCADWrapper.Instance(); lock (singleton.GetLock()) { using (SLog.NDC("aaBond_PT_y")) { var ret = new double[] { double.NaN }; FinCADHelpers.LogFinCADError( FincadFunctions.aaBond_PT_intl_y( d_s: settle.ToOADate(), d_m: maturity.ToOADate(), d_dated: 0, d_f_cpn: 0, cpn: (double) coupon*0.01, princ: 100, price: (double) price, freq: 1, // annual acc: 2, // accrual method is act/act (isda) stat: new int[] {1}, // return the yield return_stat: ref ret) ); return FinCADHelpers.FinCADSingleResult(ret); } } }
/// The AddNewPoint function is called for each series in the chart when /// new points need to be added. The new point will be placed at specified /// X axis (Date/Time) position with a Y value in a range +/- 1 from the previous /// data point's Y value, and not smaller than zero. public void AddNewPoint(DateTime timeStamp, System.Windows.Forms.DataVisualization.Charting.Series ptSeries, int min, int max) { double newVal = 0; if (ptSeries.Points.Count > 0) { newVal = ptSeries.Points[ptSeries.Points.Count - 1].YValues[0] + ((rand.NextDouble() * 2) - 1); } if (newVal < 0) newVal = 0; // Add new data point to its series. ptSeries.Points.AddXY(timeStamp.ToOADate(), rand.Next(min, max)); // remove all points from the source series older than 1.5 minutes. double removeBefore = timeStamp.AddSeconds((double)(30) * (-1)).ToOADate(); //remove oldest values to maintain a constant number of data points while (ptSeries.Points[0].XValue < removeBefore) { ptSeries.Points.RemoveAt(0); } chart1.ChartAreas[0].AxisX.Minimum = ptSeries.Points[0].XValue; chart1.ChartAreas[0].AxisX.Maximum = DateTime.FromOADate(ptSeries.Points[0].XValue).AddSeconds(30).ToOADate(); chart1.ChartAreas[1].AxisX.Minimum = ptSeries.Points[0].XValue; chart1.ChartAreas[1].AxisX.Maximum = DateTime.FromOADate(ptSeries.Points[0].XValue).AddSeconds(30).ToOADate(); chart1.ChartAreas[2].AxisX.Minimum = ptSeries.Points[0].XValue; chart1.ChartAreas[2].AxisX.Maximum = DateTime.FromOADate(ptSeries.Points[0].XValue).AddSeconds(30).ToOADate(); chart1.Invalidate(); }
public static void SetCellDateTime(WorksheetPart worksheetPart, DateTime value, string columnName, uint rowIndex) { Cell cell = GetCell(worksheetPart.Worksheet, columnName, rowIndex); cell.CellValue = new CellValue(value.ToOADate().ToString("0.000#####")); cell.DataType = new EnumValue<CellValues>(CellValues.Number); }
public DateCell(string header, DateTime dateTime, int index) { this.DataType = CellValues.Date; this.CellReference = header + index; this.StyleIndex = 1; this.CellValue = new CellValue { Text = dateTime.ToOADate().ToString() }; ; }
// Use this for initialization void Start() { System.DateTime x = System.DateTime.UtcNow; JulianDay = (decimal)x.ToOADate() + 2415018.5m; //JulianDay = 2458910; SinceJ2000 = JulianDay - J2000; }
private static DataPoint CreateDataPoint(double x, string axisLabel, DateTime start, DateTime end, string text, Color color) { var point = new DataPoint(x, new double[] { start.ToOADate(), end.ToOADate() }); point.Color = color; point.Label = text; point.AxisLabel = axisLabel; return point; }
public static void SetCellDate(WorksheetPart worksheetPart, DateTime value, string columnName, DocumentFormat.OpenXml.Spreadsheet.Row row) { Cell cell = GetCell(worksheetPart.Worksheet, columnName, row); cell.CellValue = new CellValue(value.ToOADate().ToString("0")); cell.DataType = new EnumValue<CellValues>(CellValues.Number); }
public DateCell(string id, DateTime dateTime, uint index) { this.DataType = CellValues.Date; this.CellReference = id + index; //this.StyleIndex = 10; this.CellValue = new CellValue { Text = dateTime.ToOADate().ToString() }; ; }
public static double CalculateBEPrice(DateTime settle, DateTime maturity, decimal yield, decimal coupon) { var singleton = FinCADWrapper.Instance(); lock (singleton.GetLock()) { using (SLog.NDC("aaBond_BE_p")) { var ret = new double[] { double.NaN }; FinCADHelpers.LogFinCADError( FincadFunctions.aaBond_BE_p( d_s: settle.ToOADate(), d_m: maturity.ToOADate(), cpn: (double)coupon * 0.01, princ_m: 100, yield: (double)yield, stat: new int[] { 1 }, d_dated: 0, d_f_cpn: 0, return_stat: ref ret) ); return FinCADHelpers.FinCADSingleResult(ret); } } }
public Graph(System.Windows.Forms.CheckedListBox.CheckedItemCollection data, DateTime minDate, DateTime maxDate, ISaleService iSaleService, int mode, IReportService iReportService, ITimeSettingService iTimeSettingService, IProgramService iProgramService) { InitializeComponent(); fillColor(colorList); _data = data; _minDate = minDate; _maxDate = maxDate; _iSaleService = iSaleService; _iReportService = iReportService; _iTimeSettingService = iTimeSettingService; _iProgramService = iProgramService; this.chart1.ChartAreas[0].AxisX.MajorGrid.LineDashStyle = System.Windows.Forms.DataVisualization.Charting.ChartDashStyle.Dash; chart1.ChartAreas.Add("area"); chart1.ChartAreas[0].AxisX.LabelStyle.Format = "dd-MM"; chart1.ChartAreas["area"].AxisX.Interval = 1; chart1.ChartAreas["area"].AxisX.IntervalType = DateTimeIntervalType.Days; chart1.ChartAreas["area"].AxisX.IntervalOffset = 1; chart1.ChartAreas["area"].AxisX.Minimum = minDate.ToOADate(); chart1.ChartAreas["area"].AxisX.Maximum = maxDate.ToOADate(); if (mode == 1) { drawQuantity(); label1.Text = "Quantity"; } else { drawEfficiency(); label1.Text = "Efficiency"; } }
public void DateFunctionShouldReturnACorrectDate() { var expectedDate = new DateTime(2012, 4, 3); var func = new Date(); var args = FunctionsHelper.CreateArgs(2012, 4, 3); var result = func.Execute(args, _parsingContext); Assert.AreEqual(expectedDate.ToOADate(), result.Result); }
public void DateFunctionShouldMonthFromPrevYearIfMonthIsNegative() { var expectedDate = new DateTime(2011, 11, 3); var func = new Date(); var args = FunctionsHelper.CreateArgs(2012, -1, 3); var result = func.Execute(args, _parsingContext); Assert.AreEqual(expectedDate.ToOADate(), result.Result); }
public void DayShouldReturnDayInMonth() { var date = new DateTime(2012, 3, 12); var func = new Day(); var args = FunctionsHelper.CreateArgs(date.ToOADate()); var result = func.Execute(args, _parsingContext); Assert.AreEqual(12, result.Result); }
public override CompileResult Execute(IEnumerable<FunctionArgument> arguments, ParsingContext context) { ValidateArguments(arguments, 2); var date = System.DateTime.FromOADate(ArgToDecimal(arguments, 0)); var monthsToAdd = ArgToInt(arguments, 1); var resultDate = new System.DateTime(date.Year, date.Month, 1).AddMonths(monthsToAdd + 1).AddDays(-1); return CreateResult(resultDate.ToOADate(), DataType.Date); }
public void DateStringCompileResult() { var expected = new DateTime(2013, 1, 15); string dateString = expected.ToString("d"); CompileResult result = new CompileResult(dateString, DataType.String); Assert.IsFalse(result.IsNumeric); Assert.IsTrue(result.IsDateString); Assert.AreEqual(expected.ToOADate(), result.ResultNumeric); }
/// <summary> /// Convert a Datetime to Unix Timestamp /// </summary> public static double DateTimeToUnixTimeStamp(DateTime time) { double timestamp = 0; try { timestamp = (time - new DateTime(1970, 1, 1, 0, 0, 0, 0)).TotalSeconds; } catch (Exception err) { Log.Error("Time.DateTimeToUnixTimeStamp(): " + time.ToOADate().ToString() + err.Message); } return timestamp; }
public override CompileResult Execute(IEnumerable <FunctionArgument> arguments, ParsingContext context) { ValidateArguments(arguments, 2); var date = DateTimeExtensions.FromOADate(ArgToDecimal(arguments, 0)); var monthsToAdd = ArgToInt(arguments, 1); var resultDate = new System.DateTime(date.Year, date.Month, 1).AddMonths(monthsToAdd + 1).AddDays(-1); return(CreateResult(resultDate.ToOADate(), DataType.Date)); }
public double GetYearFrac(System.DateTime date1, System.DateTime date2, DayCountBasis basis) { var func = new Yearfrac(); var args = new List <FunctionArgument> { new FunctionArgument(date1.ToOADate()), new FunctionArgument(date2.ToOADate()), new FunctionArgument((int)basis) }; var result = func.Execute(args, _context); return(result.ResultNumeric); }
public override CompileResult Execute(IEnumerable<FunctionArgument> arguments, ParsingContext context) { ValidateArguments(arguments, 3); var year = ArgToInt(arguments, 0); var month = ArgToInt(arguments, 1); var day = ArgToInt(arguments, 2); var date = new System.DateTime(year, 1, 1); month -= 1; date = date.AddMonths(month); date = date.AddDays((double)(day - 1)); return CreateResult(date.ToOADate(), DataType.Date); }
public override CompileResult Execute(IEnumerable <FunctionArgument> arguments, ParsingContext context) { ValidateArguments(arguments, 3); var year = ArgToInt(arguments, 0); var month = ArgToInt(arguments, 1); var day = ArgToInt(arguments, 2); var date = new System.DateTime(year, 1, 1); month -= 1; date = date.AddMonths(month); date = date.AddDays((double)(day - 1)); return(CreateResult(date.ToOADate(), DataType.Date)); }
public static double CalculateGBP6mLiborSwapRate(double[,] dfCurve, DateTime spot, DateTime value, DateTime maturity, string holidayCenter) { var singleton = FinCADWrapper.Instance(); lock (singleton.GetLock()) { using (SLog.NDC("aaParSwap")) { double ret = double.NaN; FinCADHelpers.LogFinCADError(FincadFunctions.aaParSwap(value.ToOADate(), maturity.ToOADate(), spot.ToOADate(), 0, 0, 2, 4, 4, aaHolidaysWrapper.Holidays(holidayCenter), 4, 2, dfCurve, ref ret)); return ret; } } }
public static DateTime RollDate(DateTime date, int days, string calendar) { var singleton = FinCADWrapper.Instance(); lock (singleton.GetLock()) { using (SLog.NDC("aaDate_Settle")) { double ret = double.NaN; FinCADHelpers.LogFinCADError(FincadFunctions.aaDate_Settle(date.ToOADate(), days, aaHolidaysWrapper.Holidays(calendar), ref ret)); return DateTime.FromOADate(ret); } } }
public static double CalculateDEPrice(DateTime settle, DateTime maturity, decimal yield, decimal coupon) { var singleton = FinCADWrapper.Instance(); lock (singleton.GetLock()) { using (SLog.NDC("aaBond_DE_p")) { var ret = new double[] { double.NaN }; FinCADHelpers.LogFinCADError(FincadFunctions.aaBond_DE_p(settle.ToOADate(), maturity.ToOADate(), 0, 0, (double)coupon * 0.01, 100, (double)yield, 1, new int[] { 1 }, ref ret)); return FinCADHelpers.FinCADSingleResult(ret); } } }
public static double CalculateFRYield(DateTime settle, DateTime maturity, decimal price, decimal coupon) { var singleton = FinCADWrapper.Instance(); lock (singleton.GetLock()) { using (SLog.NDC("aaBond_FR_yields")) { var ret = new double[] { double.NaN }; FinCADHelpers.LogFinCADError(FincadFunctions.aaBond_FR_yields(1 /*?*/, settle.ToOADate(), maturity.ToOADate(), (double)coupon * 0.01, (double)price, aaHolidaysWrapper.Holidays("TARGET"), new int[] { 1 }, ref ret)); return FinCADHelpers.FinCADSingleResult(ret); } } }
public static double CalculateImpliedVolatility(double bpv_, double underlyingPx_, double strike_, DateTime expiry_, DateTime valueDate_, double optionPrice_, double rate_, OptionType optionType_, AccrualMethod accMethod_ = AccrualMethod.Actual365_Fixed) { var singleton = FinCADWrapper.Instance(); lock (singleton.GetLock()) { using (SLog.NDC("aaBL_ivW")) { double ret = 0d; FinCADHelpers.LogFinCADError(FincadFunctions.aaBL_iv(underlyingPx_, strike_, expiry_.ToOADate(), valueDate_.ToOADate(), optionPrice_, rate_, (int)optionType_, (int)accMethod_, ref ret)); return ret; } } }
public static DateTime RollDate(DateTime date, int units, int method, int convention, string calendar) { var singleton = FinCADWrapper.Instance(); lock (singleton.GetLock()) { using (SLog.NDC("aaDateAdjust")) { double ret = double.NaN; FinCADHelpers.LogFinCADError(FincadFunctions.aaDateAdjust(date.ToOADate(), units, method, convention, aaHolidaysWrapper.Holidays(calendar), ref ret)); return DateTime.FromOADate(ret); } } }
public static double[,] CalculateCashflowStrip(DateTime settle, DateTime maturity, decimal coupon) { var singleton = FinCADWrapper.Instance(); lock (singleton.GetLock()) { using (SLog.NDC("aaBond_strip_cf")) { var ret = new double[0, 0]; var dfCurve = new double[1, 2] { { 1, 1 } }; FinCADHelpers.LogFinCADError(FincadFunctions.aaBond_strip_cf(settle.ToOADate(), maturity.ToOADate(), 0, 0, 0, (double)coupon * 0.01, 100, 2, 3, new double[0], 1, 0, 1, dfCurve, ref ret)); return ret; } } }
/// <summary> /// Given a year, month, and day, return the Excel OADate representing that date. If the year value is /// between 0 and 1899 inclusive, that value is added to 1900 and uses that sum as the year. If the year value is /// between 1900 and 9999 inclusive, that value is used as the year. If the year value is less than 0, an <see cref="ExcelErrorValue"/> /// is returned. If the month value is less than 1 or greater than 12, that month value is added from the first month of the specified /// year. If the day value is less than 1 or greater than the number of days in the specified month, that number of days is added /// to the first day of the specified month. /// </summary> /// <param name="arguments">The arguments used to calculate the Excel OADate.</param> /// <param name="context">Unused in the method, but necessary to override the method.</param> /// <returns>Returns the Excel OADate of the given date, or an <see cref="ExcelErrorValue"/> if the input is invalid.</returns> public override CompileResult Execute(IEnumerable <FunctionArgument> arguments, ParsingContext context) { if (this.ArgumentsAreValid(arguments, 3, out eErrorType argumentError) == false) { return(new CompileResult(argumentError)); } var isValidYear = this.TryGetArgAsInt(arguments, 0, out int year); var isValidMonth = this.TryGetArgAsInt(arguments, 1, out int month); var isValidDay = this.TryGetArgAsInt(arguments, 2, out int day); if (!isValidYear || !isValidMonth || !isValidDay) { return(new CompileResult(eErrorType.Value)); } if (year < 0 || year > 9999) { return(new CompileResult(eErrorType.Num)); } else if (year < 1900) { year += 1900; } var resultDate = new System.DateTime(year, 1, 1); var dateWithMonthsAdded = resultDate; month--; day--; try { resultDate = resultDate.AddMonths(month); dateWithMonthsAdded = resultDate; resultDate = resultDate.AddDays(day); } catch (ArgumentOutOfRangeException) { return(new CompileResult(eErrorType.Num)); } var resultOADate = resultDate.ToOADate(); if (resultOADate < 1) { return(new CompileResult(eErrorType.Num)); } if (dateWithMonthsAdded.ToOADate() < 61 && resultOADate != 61) { resultOADate--; } return(this.CreateResult(resultOADate, DataType.Date)); }
private ChartArea AddArea( string name, DateTime startdate ) { var area = resultsChart.ChartAreas.Add( name + "ChartArea" ); area.Visible = false; area.AxisX.IntervalType = DateTimeIntervalType.Weeks; area.AxisX.Interval = 1; area.AxisY.IsMarginVisible = true; area.AxisX.Minimum = startdate.ToOADate(); area.AxisX.IsMarginVisible = false; area.AxisX.MajorGrid.LineDashStyle = ChartDashStyle.Dash; area.AxisY.MajorGrid.LineDashStyle = ChartDashStyle.Dash; return area; }
public void SetTimeRange(DateTime From, DateTime To) { State.Wait(); if (DataChart.Series.Count() <= 0) return; bool found = false; for (int index = 0; index < DataChart.Series.Count(); index++ ) { Series tempSeries = Stock[StockFile.STOCKTYPE.OPENING]; //hard copy here for (int i = 0; i < tempSeries.Points.Count(); ) { //The order by default seems to be in reverse, so this should be first if (tempSeries.Points[i].XValue >= To.ToOADate()) { tempSeries.Points.Remove(tempSeries.Points[i]); found = true; } //error checking in a pain, but meh if (i >= tempSeries.Points.Count()) break; if (tempSeries.Points[i].XValue < From.ToOADate()) { tempSeries.Points.Remove(tempSeries.Points[i]); found = true; } if (!found) i++; //basically keep the index unless it is found to not match, because the index for the next should be the same found = false; } DataChart.Series.RemoveAt(index); DataChart.Series.Insert(index, tempSeries); } DataChart.ChartAreas["StockArea"].RecalculateAxesScale(); GC.Collect(); State.Clear(); }
public void AddNewPoint(DateTime timeStamp, Series ptSeries, float nexVal) { ptSeries.Points.AddXY(timeStamp.ToOADate(), nexVal); double removeBefore = timeStamp.AddSeconds((double)(9) * (-1)).ToOADate(); while (ptSeries.Points[0].XValue < removeBefore) { ptSeries.Points.RemoveAt(0); } chart.ChartAreas[0].AxisX.Minimum = ptSeries.Points[0].XValue; chart.ChartAreas[0].AxisX.Maximum = DateTime.FromOADate(ptSeries.Points[0].XValue).AddSeconds(10).ToOADate(); chart.Invalidate(); }
static void WorkDays(DateTime[] holidays, DateTime date) { // Calculates today date DateTime now = DateTime.Parse(DateTime.Now.ToShortDateString()); // Calculates all days int allDays = (int)Math.Abs(now.ToOADate() - date.ToOADate()); // If the date is before today if (now > date) { DateTime temp = now; now = date; date = temp; } // Checks all days int holiday = 0; for (DateTime d = now; d < date; d = d.AddDays(1)) { // Calculates the holidays from the weekends if (d.DayOfWeek.ToString() == "Sunday" || d.DayOfWeek.ToString() == "Saturday") holiday++; else { // Checks for some other holidays foreach (DateTime h in holidays) { if (h.Day == d.Day && h.Month == d.Month) holiday++; } } } // Print the result Console.Write("\nFrom {0} to {1} there are ", now.ToString("dd MMMM yyyy"), date.ToString("dd MMMM yyyy")); // Prints the workdays Console.ForegroundColor = ConsoleColor.Green; Console.Write(allDays - holiday); Console.ResetColor(); Console.Write(" workdays and "); // Prints the holidays Console.ForegroundColor = ConsoleColor.Green; Console.Write(holiday); Console.ResetColor(); Console.WriteLine(" holidays"); }
public string SQLDateTime(System.DateTime DT) { string result = null; int Y = 0; if ((DT != null) && (Convert.ToDouble(DT.ToOADate()) > 0)) { Y = DT.Year; if (Y > 2500) { Y -= 543; } result = "'" + Y + DT.ToString("-MM-dd HH:mm:ss") + "'"; } else { result = "NULL"; } return(result); }
public void AverageArray() { // In the case of arrays, Average DOES NOT parse and include numeric strings, date strings, bools, unparsable strings, etc. Average average = new Average(); var date1 = new DateTime(2013, 1, 5); var date2 = new DateTime(2013, 1, 15); double value = 2000; var result = average.Execute(new FunctionArgument[] { new FunctionArgument(new FunctionArgument[] { new FunctionArgument(1000.ToString("n")), new FunctionArgument(value), new FunctionArgument(6000.ToString("n")), new FunctionArgument(true), new FunctionArgument(date1), new FunctionArgument(date2.ToString("d")), new FunctionArgument("test") }) }, ParsingContext.Create()); Assert.AreEqual((2000 + date1.ToOADate()) / 2, result.Result); }
private void add2chart(string seriesName, string chartName, DateTime time, float value) { Series series = chart.Series.FindByName(seriesName); if (series == null) { series = new Series(seriesName) { ChartArea = chartName, ChartType = SeriesChartType.SplineArea, XValueType = ChartValueType.DateTime }; chart.Series.Add(series); } series.Points.AddXY(time.ToOADate(), (double)value); DateTime now = DateTime.Now; chart.ChartAreas[chartName].AxisX.Maximum = now.ToOADate(); chart.ChartAreas[chartName].AxisX.Minimum = (now.AddSeconds(-10)).ToOADate(); foreach (var p in series.Points.Where(p => p.XValue < now.AddSeconds(-10).ToOADate()).ToArray()) { series.Points.Remove(p); } var maxValue = series.Points.Max(p => p.YValues[0]); if (chart.ChartAreas[chartName].AxisY.Maximum < maxValue) chart.ChartAreas[chartName].AxisY.Maximum = maxValue; }
public static double ToJulianDate(this DateTime date) { return(date.ToOADate() + 2415018.5); }
private double calcJulianDate(System.DateTime date) { return(date.ToOADate() + 2415018.5); }
private void CustomerStatement(ref int id) { ADODB.Recordset rsInterest = default(ADODB.Recordset); ADODB.Recordset rsCustTransCheck = default(ADODB.Recordset); ADODB.Recordset rsTransaction = default(ADODB.Recordset); ADODB.Recordset rsCompany = default(ADODB.Recordset); string lNumber = null; string lAddress = null; ADODB.Recordset rs = new ADODB.Recordset(); string sql = null; //Dim Report As New cryCustomerStatement_AGING 'cryCustomerStatement CrystalDecisions.CrystalReports.Engine.ReportDocument Report = new CrystalDecisions.CrystalReports.Engine.ReportDocument(); Report.Load("cryCustomerStatement_AGING.rpt"); System.DateTime lDate = default(System.DateTime); rs = modRecordSet.getRS(ref "SELECT MonthEnd.MonthEnd_Date From MonthEnd WHERE (((MonthEnd.MonthEndID)=" + gMonth - (this.cmbMonthEnd.SelectedIndex - 1) + "));"); Report.SetParameterValue("txtStatementDate", Strings.Format(rs.Fields("MonthEnd_Date").Value, "dd mmm yyyy")); lDate = rs.Fields("MonthEnd_Date").Value; rs.Close(); rs = modRecordSet.getRS(ref "SELECT * FROM Company"); lDate = System.Date.FromOADate(lDate.ToOADate() + 10); lDate = System.Date.FromOADate(lDate.ToOADate() - DateAndTime.Day(lDate)) + rs.Fields("Company_PaymentDay").Value; if (chkFields.CheckState) { Report.SetParameterValue("txtPaymentDate", " "); Report.SetParameterValue("Text7", " "); } else { Report.SetParameterValue("txtPaymentDate", Strings.Format(lDate, "dd mmm yyyy")); } lAddress = Strings.Replace(rs.Fields("Company_PhysicalAddress").Value, Constants.vbCrLf, ", "); if (Strings.Right(lAddress, 2) == ", ") { lAddress = Strings.Left(lAddress, Strings.Len(lAddress) - 2); } Report.Database.Tables(1).SetDataSource(rs); Report.SetParameterValue("txtAddress", lAddress); lNumber = ""; if (!string.IsNullOrEmpty(rs.Fields("Company_Telephone").Value)) { lNumber = lNumber + "Tel: " + rs.Fields("Company_Telephone").Value; } if (!string.IsNullOrEmpty(rs.Fields("Company_Fax").Value)) { if (!string.IsNullOrEmpty(lNumber)) { lNumber = lNumber + " / "; } lNumber = lNumber + "Fax: " + rs.Fields("Company_Fax").Value; } if (!string.IsNullOrEmpty(rs.Fields("Company_Email").Value)) { if (!string.IsNullOrEmpty(lNumber)) { lNumber = lNumber + " / "; } lNumber = lNumber + "Email: " + rs.Fields("Company_Email").Value; } Report.SetParameterValue("txtNumbers", lNumber); //New banking details if (Information.IsDBNull(rs.Fields("Company_BankName").Value)) { } else { Report.SetParameterValue("txtBankName", rs.Fields("Company_BankName")); } if (Information.IsDBNull(rs.Fields("Company_BranchName").Value)) { } else { Report.SetParameterValue("txtBranchName", rs.Fields("Company_BranchName")); } if (Information.IsDBNull(rs.Fields("Company_BranchCode").Value)) { } else { Report.SetParameterValue("txtBranchCode", rs.Fields("Company_BranchCode")); } if (Information.IsDBNull(rs.Fields("Company_AccountNumber").Value)) { } else { Report.SetParameterValue("txtAccountNumber", rs.Fields("Company_AccountNumber")); } //................... rsCompany = new ADODB.Recordset(); rsCompany.Open("SELECT * FROM Customer Where CustomerID = " + id, cnnDBStatement, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText); Report.SetParameterValue("txtDaysCurr", "R " + Strings.FormatNumber(rsCompany("Customer_Current"), 2)); Report.SetParameterValue("txtDays30", "R " + Strings.FormatNumber(rsCompany("Customer_30Days"), 2)); Report.SetParameterValue("txtDays60", "R " + Strings.FormatNumber(rsCompany("Customer_60Days"), 2)); Report.SetParameterValue("txtDays90", "R " + Strings.FormatNumber(rsCompany("Customer_90Days"), 2)); Report.SetParameterValue("txtDays120", "R " + Strings.FormatNumber(rsCompany("Customer_120Days"), 2)); Report.SetParameterValue("txtDays150", "R " + Strings.FormatNumber(rsCompany("Customer_150Days"), 2)); rsCompany.Close(); rsCompany.Open("SELECT * FROM Customer Where CustomerID = " + id, modRecordSet.cnnDB, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText); Report.Database.Tables(2).SetDataSource(rsCompany); rsTransaction = new ADODB.Recordset(); //rsTransaction.Open "SELECT CustomerTransaction.CustomerTransactionID, CustomerTransaction.CustomerTransaction_CustomerID, CustomerTransaction.CustomerTransaction_TransactionTypeID, CustomerTransaction.CustomerTransaction_DayEndID, CustomerTransaction.CustomerTransaction_MonthEndID, CustomerTransaction.CustomerTransaction_ReferenceID, CustomerTransaction.CustomerTransaction_Date, CustomerTransaction.CustomerTransaction_Description, CustomerTransaction.CustomerTransaction_Amount, CustomerTransaction.CustomerTransaction_Reference, CustomerTransaction.CustomerTransaction_PersonName," & _ //'" TransactionType.TransactionType_Name, IIf([CustomerTransaction_Amount]>0,[CustomerTransaction_Amount],Null) AS debit, IIf([CustomerTransaction_Amount]<0,[CustomerTransaction_Amount],Null) AS credit FROM CustomerTransaction INNER JOIN TransactionType ON CustomerTransaction.CustomerTransaction_TransactionTypeID = TransactionType.TransactionTypeID WHERE (((CustomerTransaction.CustomerTransaction_CustomerID)=" & id & "));", cnnDBStatement, adOpenStatic, adLockReadOnly, adCmdText //changed for OPEN ITEM //rsTransaction.Open "SELECT CustomerTransaction.CustomerTransactionID, CustomerTransaction.CustomerTransaction_CustomerID, CustomerTransaction.CustomerTransaction_TransactionTypeID, CustomerTransaction.CustomerTransaction_DayEndID, CustomerTransaction.CustomerTransaction_MonthEndID, CustomerTransaction.CustomerTransaction_ReferenceID, CustomerTransaction.CustomerTransaction_Date, CustomerTransaction.CustomerTransaction_Description, CustomerTransaction.CustomerTransaction_Amount, CustomerTransaction.CustomerTransaction_Reference, CustomerTransaction.CustomerTransaction_PersonName," & _ //'" TransactionType.TransactionType_Name, IIf([CustomerTransaction_Amount]>0,[CustomerTransaction_Amount],Null) AS debit, IIf([CustomerTransaction_Amount]<0,[CustomerTransaction_Amount],Null) AS credit FROM CustomerTransaction INNER JOIN TransactionType ON CustomerTransaction.CustomerTransaction_TransactionTypeID = TransactionType.TransactionTypeID WHERE (((CustomerTransaction.CustomerTransaction_CustomerID)=" & id & "));", cnnDBStatement, adOpenStatic, adLockReadOnly, adCmdText rsCustTransCheck = new ADODB.Recordset(); rsCustTransCheck.Open("SELECT * FROM CustomerTransaction Where CustomerTransaction_CustomerID = " + id, cnnDBStatement, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText); if (rsCustTransCheck.Fields.Count <= 12) { rsTransaction.Open("SELECT CustomerTransaction.CustomerTransactionID, CustomerTransaction.CustomerTransaction_CustomerID, CustomerTransaction.CustomerTransaction_TransactionTypeID, CustomerTransaction.CustomerTransaction_DayEndID, CustomerTransaction.CustomerTransaction_MonthEndID, CustomerTransaction.CustomerTransaction_ReferenceID, CustomerTransaction.CustomerTransaction_Date, CustomerTransaction.CustomerTransaction_Description, CustomerTransaction.CustomerTransaction_Amount, CustomerTransaction.CustomerTransaction_Reference, CustomerTransaction.CustomerTransaction_PersonName," + " TransactionType.TransactionType_Name, IIf([CustomerTransaction_Amount]>0,[CustomerTransaction_Amount],Null) AS debit, IIf([CustomerTransaction_Amount]<0,[CustomerTransaction_Amount],Null) AS credit, 0, 0, 0 FROM CustomerTransaction INNER JOIN TransactionType ON CustomerTransaction.CustomerTransaction_TransactionTypeID = TransactionType.TransactionTypeID WHERE (((CustomerTransaction.CustomerTransaction_CustomerID)=" + id + "));", cnnDBStatement, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText); } else { rsTransaction.Open("SELECT CustomerTransaction.CustomerTransactionID, CustomerTransaction.CustomerTransaction_CustomerID, CustomerTransaction.CustomerTransaction_TransactionTypeID, CustomerTransaction.CustomerTransaction_DayEndID, CustomerTransaction.CustomerTransaction_MonthEndID, CustomerTransaction.CustomerTransaction_ReferenceID, CustomerTransaction.CustomerTransaction_Date, CustomerTransaction.CustomerTransaction_Description, CustomerTransaction.CustomerTransaction_Amount-IIf(IsNull([CustomerTransaction.CustomerTransaction_Allocated]),0,[CustomerTransaction.CustomerTransaction_Allocated]), CustomerTransaction.CustomerTransaction_Reference & IIf([CustomerTransaction.CustomerTransaction_Allocated]<>[CustomerTransaction_Amount] AND [CustomerTransaction.CustomerTransaction_Allocated]<>0,' (P)',Null), CustomerTransaction.CustomerTransaction_PersonName, TransactionType.TransactionType_Name," + " IIf(([CustomerTransaction_Amount]-IIf(IsNull([CustomerTransaction.CustomerTransaction_Allocated]),0,[CustomerTransaction.CustomerTransaction_Allocated]))>0,([CustomerTransaction_Amount]-IIf(IsNull([CustomerTransaction.CustomerTransaction_Allocated]),0,[CustomerTransaction.CustomerTransaction_Allocated])),Null) AS debit," + " IIf(([CustomerTransaction_Amount]-IIf(IsNull([CustomerTransaction.CustomerTransaction_Allocated]),0,[CustomerTransaction.CustomerTransaction_Allocated]))<0,([CustomerTransaction_Amount]-IIf(IsNull([CustomerTransaction.CustomerTransaction_Allocated]),0,[CustomerTransaction.CustomerTransaction_Allocated])),Null) AS credit, CustomerTransaction.CustomerTransaction_Main, CustomerTransaction.CustomerTransaction_Child, CustomerTransaction.CustomerTransaction_Allocated FROM CustomerTransaction INNER JOIN TransactionType ON CustomerTransaction.CustomerTransaction_TransactionTypeID = TransactionType.TransactionTypeID WHERE (((CustomerTransaction.CustomerTransaction_CustomerID)=" + id + ") AND ((CustomerTransaction.CustomerTransaction_Amount-IIf(IsNull([CustomerTransaction.CustomerTransaction_Allocated]),0,[CustomerTransaction.CustomerTransaction_Allocated]))<>0));", cnnDBStatement, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText); } //Report.Database.Tables(3).SetDataSource rsTransaction, 3 if (rsTransaction.BOF | rsTransaction.EOF) { rsTransaction = new ADODB.Recordset(); rsTransaction.Open("SELECT 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0," + " 0, 0 AS debit, 0 AS credit;", cnnDBStatement, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText); Report.Database.Tables(3).SetDataSource(rsTransaction); //Exit Sub } else { Report.Database.Tables(3).SetDataSource(rsTransaction); } if (rsTransaction.BOF | rsTransaction.EOF) { return; } //Set rsInterest = New Recordset //rsInterest.Open ("SELECT * FROM Interest WHERE (((CustomerID)=" & id & ")) and (Debit>0);"), cnnDBStatement, adOpenStatic, adLockReadOnly, adCmdText rsInterest = modRecordSet.getRS(ref "SELECT * FROM Interest WHERE (((CustomerID)=" + id + ")) and (Debit>0);"); //If rsInterest.BOF Or rsInterest.EOF Then if (rsInterest.RecordCount > 0) { Report.ReportDefinition.ReportObjects("Field20").Top = 280; Report.ReportDefinition.ReportObjects("Field21").Top = 280; Report.ReportDefinition.ReportObjects("Field22").Top = 280; Report.ReportDefinition.ReportObjects("Field23").Top = 280; Report.Database.Tables(4).SetDataSource(rsInterest); } else { //Set rsInterest = New Recordset //rsInterest.Open "SELECT 0 AS CustomerID, 0 AS CDate, 0 AS Description, 0 AS Debit, 0 AS Credit, 0 AS SumIntBal ;", cnnDBStatement, adOpenStatic, adLockReadOnly, adCmdText rsInterest = modRecordSet.getRS(ref "SELECT 0 AS CustomerID, 0 AS CDate, 0 AS Description, 0 AS Debit, 0 AS Credit, 0 AS SumIntBal ;"); Report.ReportDefinition.Sections("Field20").SectionFormat.EnableSuppress = true; Report.ReportDefinition.Sections("Field21").SectionFormat.EnableSuppress = true; Report.ReportDefinition.Sections("Field22").SectionFormat.EnableSuppress = true; Report.ReportDefinition.Sections("Field23").SectionFormat.EnableSuppress = true; Report.Database.Tables(4).SetDataSource(rsInterest); //Exit Sub //Set rsInterest = New Recordset //rsInterest.Open "SELECT * FROM Interest WHERE (((CustomerID)=" & id & "));", cnnDBStatement, adOpenStatic, adLockReadOnly, adCmdText } if (rs.Fields("Company_DebtorPrintBal").Value == true) { CrystalDecisions.CrystalReports.Engine.TextObject tObj = Report.ReportDefinition.ReportObjects("Text5"); tObj.Color = Color.White; } My.MyProject.Forms.frmReportShow.CRViewer1.ReportSource = Report; My.MyProject.Forms.frmReportShow.mReport = Report; My.MyProject.Forms.frmReportShow.sMode = "0"; // frmReportShow.CRViewer1.PrintReport My.MyProject.Forms.frmReportShow.CRViewer1.Refresh(); // Unload frmReportShow System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default; My.MyProject.Forms.frmReportShow.ShowDialog(); }
private void CustomerStatement(ref int id) { ADODB.Recordset rsInterest = default(ADODB.Recordset); ADODB.Recordset rsTransaction = default(ADODB.Recordset); ADODB.Recordset rsCompany = default(ADODB.Recordset); int lNumber = 0; string lAddress = null; ADODB.Recordset rs = new ADODB.Recordset(); string sql = null; //Dim Report As New cryCustomerStatement CrystalDecisions.CrystalReports.Engine.ReportDocument Report = new CrystalDecisions.CrystalReports.Engine.ReportDocument(); Report.Load("cryCustomerStatement.rpt"); System.DateTime lDate = default(System.DateTime); rs = modRecordSet.getRS(ref "SELECT MonthEnd.MonthEnd_Date From MonthEnd WHERE (((MonthEnd.MonthEndID)=" + gMonth + "));"); //rs.Open "SELECT MonthEnd.MonthEnd_Date From MonthEnd WHERE (((MonthEnd.MonthEndID)=" & gMonth & "));", cnnDBStatement, adOpenStatic, adLockReadOnly, adCmdText //Report.txtStatementDate.SetText Format(rs("MonthEnd_Date"), "dd mmm yyyy") Report.SetParameterValue("txtStatementDate", Strings.Format(DateAndTime.Today, "dd mmm yyyy")); lDate = rs.Fields("MonthEnd_Date").Value; rs.Close(); rs = modRecordSet.getRS(ref "SELECT * FROM Company"); lDate = System.Date.FromOADate(lDate.ToOADate() + 10); lDate = DateAndTime.DateSerial(DateAndTime.Year(lDate), DateAndTime.Month(lDate), 1); lDate = System.Date.FromOADate(lDate + rs.Fields("Company_PaymentDay").Value - 1); //Report.txtPaymentDate.SetText Format(lDate, "dd mmm yyyy") lAddress = Strings.Replace(rs.Fields("Company_PhysicalAddress").Value, Constants.vbCrLf, ", "); if (Strings.Right(lAddress, 2) == ", ") { lAddress = Strings.Left(lAddress, Strings.Len(lAddress) - 2); } Report.Database.Tables(1).SetDataSource(rs); Report.SetParameterValue("txtAddress", lAddress); lNumber = ""; if (!string.IsNullOrEmpty(rs.Fields("Company_Telephone").Value)) { lNumber = lNumber + "Tel: " + rs.Fields("Company_Telephone").Value; } if (!string.IsNullOrEmpty(rs.Fields("Company_Fax").Value)) { if (!string.IsNullOrEmpty(lNumber)) { lNumber = lNumber + " / "; } lNumber = lNumber + "Fax: " + rs.Fields("Company_Fax").Value; } if (!string.IsNullOrEmpty(rs.Fields("Company_Email").Value)) { if (!string.IsNullOrEmpty(lNumber)) { lNumber = lNumber + " / "; } lNumber = lNumber + "Email: " + rs.Fields("Company_Email").Value; } Report.SetParameterValue("txtNumbers", lNumber); //New banking details if (Information.IsDBNull(rs.Fields("Company_BankName").Value)) { } else { Report.SetParameterValue("txtBankName", rs.Fields("Company_BankName")); } if (Information.IsDBNull(rs.Fields("Company_BranchName").Value)) { } else { Report.SetParameterValue("txtBranchName", rs.Fields("Company_BranchName")); } if (Information.IsDBNull(rs.Fields("Company_BranchCode").Value)) { } else { Report.SetParameterValue("txtBranchCode", rs.Fields("Company_BranchCode")); } if (Information.IsDBNull(rs.Fields("Company_AccountNumber").Value)) { } else { Report.SetParameterValue("txtAccountNumber", rs.Fields("Company_AccountNumber")); } //................... rsCompany = new ADODB.Recordset(); rsCompany.Open("SELECT * FROM Customer Where CustomerID = " + id, cnnDBStatement, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText); Report.Database.Tables(2).SetDataSource(rsCompany); rsTransaction = new ADODB.Recordset(); //rsTransaction.Open "SELECT CustomerTransaction.*, TransactionType.TransactionType_Name, IIf([CustomerTransaction_Amount]>0,[CustomerTransaction_Amount],Null) AS debit, IIf([CustomerTransaction_Amount]<0,[CustomerTransaction_Amount],Null) AS credit FROM CustomerTransaction INNER JOIN TransactionType ON CustomerTransaction.CustomerTransaction_TransactionTypeID = TransactionType.TransactionTypeID WHERE (((CustomerTransaction.CustomerTransaction_CustomerID)=" & id & "));", cnnDBStatement, adOpenStatic, adLockReadOnly, adCmdText rsTransaction.Open("SELECT CustomerTransaction.CustomerTransactionID, CustomerTransaction.CustomerTransaction_CustomerID, CustomerTransaction.CustomerTransaction_TransactionTypeID, CustomerTransaction.CustomerTransaction_DayEndID, CustomerTransaction.CustomerTransaction_MonthEndID, CustomerTransaction.CustomerTransaction_ReferenceID, CustomerTransaction.CustomerTransaction_Date, CustomerTransaction.CustomerTransaction_Description, CustomerTransaction.CustomerTransaction_Amount, CustomerTransaction.CustomerTransaction_Reference, CustomerTransaction.CustomerTransaction_PersonName," + " TransactionType.TransactionType_Name, IIf([CustomerTransaction_Amount]>0,[CustomerTransaction_Amount],Null) AS debit, IIf([CustomerTransaction_Amount]<0,[CustomerTransaction_Amount],Null) AS credit FROM CustomerTransaction INNER JOIN TransactionType ON CustomerTransaction.CustomerTransaction_TransactionTypeID = TransactionType.TransactionTypeID WHERE (((CustomerTransaction.CustomerTransaction_CustomerID)=" + id + "));", cnnDBStatement, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText); //Report.Database.Tables(3).SetDataSource rsTransaction, 3 if (rsTransaction.BOF | rsTransaction.EOF) { rsTransaction = new ADODB.Recordset(); rsTransaction.Open("SELECT 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0," + " 0, 0 AS debit, 0 AS credit;", cnnDBStatement, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText); Report.Database.Tables(3).SetDataSource(rsTransaction); //Exit Sub } else { Report.Database.Tables(3).SetDataSource(rsTransaction); } if (rsTransaction.BOF | rsTransaction.EOF) { return; } rsInterest = new ADODB.Recordset(); rsInterest.Open("SELECT * FROM Interest WHERE (((CustomerID)=" + id + ")) and (Debit>0);", cnnDBStatement, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText); //If rsInterest.BOF Or rsInterest.EOF Then if (rsInterest.RecordCount > 0) { //Report.Field20.Top = 280 //Report.Field21.Top = 280 //Report.Field22.Top = 280 //Report.Field23.Top = 280 Report.Database.Tables(4).SetDataSource(rsInterest); } else { rsInterest = new ADODB.Recordset(); rsInterest.Open("SELECT 0 AS CustomerID, 0 AS CDate, 0 AS Description, 0 AS Debit, 0 AS Credit, 0 AS SumIntBal ;", cnnDBStatement, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText); //Report.Field20.Suppress = True //Report.Field21.Suppress = True //Report.Field22.Suppress = True //Report.Field23.Suppress = True Report.Database.Tables(4).SetDataSource(rsInterest); //Exit Sub //Set rsInterest = New Recordset //rsInterest.Open "SELECT * FROM Interest WHERE (((CustomerID)=" & id & "));", cnnDBStatement, adOpenStatic, adLockReadOnly, adCmdText } //Report.PrintOut(False, 1) Report.PrintToPrinter(1, false, 0, 0); System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default; }
private void Update() { int count = (int)(System.DateTime.Now.ToOADate() - baseDate.ToOADate()) / 7 + 10 - unnumberedMondays; numberText.text = string.Format("第{0:G}回", count); }
private void Update() { int count = (int)(System.DateTime.Now.ToOADate() - baseDate.ToOADate()) / 7 + 10 - unnumberedMondays; GetComponent <Text>().text = string.Format("{0:G}", count); }