private void PrintVariables(_Worksheet _sheet, int _row, int _counter, Variable variable)
{
var function = Globals.ExcelAddIn.Application.WorksheetFunction;
int numberOfBins = model.useBins && model.bins > 0 ? model.bins : (int)function.RoundUp(Math.Sqrt(function.Count(variable.getRange())), 0);
// Write the needed data to the sheet.
for (int bin = 0; bin < numberOfBins; bin++)
{
var column = 1;
var range = variable.getRange().Address(true, true, true);
_sheet.Cells[_row, column++] = "Bin #" + bin;
_sheet.WriteFunction(_row, column, bin == 0 ? "MIN(" + range + ")" : AddressConverter.CellAddress(_row - 1, column + 1)); column++;
_sheet.WriteFunction(_row, column, AddressConverter.CellAddress(_row, column - 1) + "+" + "ROUND((MAX(" + range + ")-MIN(" + range + "))/" + numberOfBins + ",0)"); column++;
_sheet.Cells[_row, column] = "=(" + AddressConverter.CellAddress(_row, column - 2) + "+" + AddressConverter.CellAddress(_row, column - 1) + ")/2"; column++;
_sheet.WriteFunction(_row, column, "COUNTIF(" + range + ",\"<=\"&" + AddressConverter.CellAddress(_row, column - 2) + ")-COUNTIF(" + range + ",\"<\"&" + AddressConverter.CellAddress(_row, column - 3) + ")"); column++;
_sheet.WriteFunction(_row, column, AddressConverter.CellAddress(_row, column - 1) + "/" + "COUNT(" + range + ")"); column++;
_sheet.WriteFunction(_row, column, AddressConverter.CellAddress(_row, column - 1) + "/" + "ROUND((MAX(" + range + ")-MIN(" + range + "))/" + numberOfBins + ",0)");
_row++;
}
// Create the chart.
var charts = (ChartObjects)_sheet.ChartObjects();
var chartObject = charts.Add(400, 225 * _counter, 100 * numberOfBins, 200);
var chart = chartObject.Chart;
chart.ChartType = XlChartType.xlColumnClustered;
chart.ChartWizard(Title: "Histogram - " + variable.name, HasLegend: false);
var seriesCollection = (SeriesCollection)chart.SeriesCollection();
var series = seriesCollection.Add(_sheet.Range[_sheet.Cells[_row - numberOfBins, 5], _sheet.Cells[_row - 1, 5]]);
series.ChartType = XlChartType.xlColumnClustered;
series.XValues = _sheet.Range[_sheet.Cells[_row - numberOfBins, 4], _sheet.Cells[_row - 1, 4]];
}
public void ConvertWorkerViewModel_WhenValidViewModel_ReturnEntity()
{
var adrConv = new AddressConverter();
var compConv = new CompetenceConverter();
var converter = new WorkerConverter(adrConv,compConv,new ImageConverter());
var viewModel = new WorkerViewModel
{
FirstName = "Filip",
LastName = "Skurniak",
Pesel = "632964328947",
RelianceRating = 10,
WorkerCompetences = new List<CompetenceViewModel>()
{
new CompetenceViewModel
{
Id = 100
}
}
};
var entity = new Worker();
converter.ConvertViewModelToEntity(viewModel, entity);
Assert.AreEqual(entity.FirstName, viewModel.FirstName);
}
public void onClick()
{
float lat = (float)AddressConverter.Locate(inputText.text).Latitude;
float lon = (float)AddressConverter.Locate(inputText.text).Longitude;
CreatePin(lat, lon);
}
void AddressCombobox_GotFocus(object sender, System.Windows.RoutedEventArgs e)
{
if (_dropdown == null)
{
_dropdown = new Popup();
_dropdown.AllowsTransparency = true;
_dropdown.MouseLeave += _dropdown_MouseLeave;
_dropdown.MouseEnter += _dropdown_MouseEnter;
var addrpop = new AddressPopup();
addrpop.Effect = new DropShadowEffect
{
ShadowDepth = 3
};
addrpop.SelectedCallback = (v) =>
{
_dropdown.IsOpen = false;
this.Text = v;
this.ToolTip = AddressConverter.AddressCodeToAddress(v);
};
_dropdown.Child = addrpop;
}
var ele = sender as FrameworkElement;
var pos = ele.PointToScreen(new Point(0, this.ActualHeight));
_dropdown.VerticalOffset = pos.Y;
_dropdown.HorizontalOffset = pos.X;
_dropdown.IsOpen = true;
}
/// <summary>
/// Set the alpha value of the mean confidence interval.
/// </summary>
/// <param name="row">The row in which the mean confidence level is in.</param>
/// <param name="column">The column in which the mean confidence level is in.</param>
public void SetMeanAlpha(int row, int column)
{
MeanAlpha = "1 - " + AddressConverter.CellAddress(row, column);
MeanConfidenceInterval = "CONFIDENCE.T(" + MeanAlpha + "," + StandardDeviation + "," + Count + ")";
MeanLowerLimit = Mean + "-" + MeanConfidenceInterval;
MeanUpperLimit = Mean + "+" + MeanConfidenceInterval;
}
/// <summary>
/// Set the alpha value of the standard deviation confidence interval.
/// </summary>
/// <param name="row">The row in which the standard deviation confidence level is in.</param>
/// <param name="column">The column in which the standard deviation confidence level is in.</param>
public void SetStandardDeviationAlpha(int row, int column)
{
StandardDeviationAlpha = "1 - " + AddressConverter.CellAddress(row, column);
StandardDeviationConfidenceIntervalLowerLimit = "CHISQ.INV(1-(" + StandardDeviationAlpha + ")/2," + DegreesOfFreedom + ")";
StandardDeviationConfidenceIntervalUpperLimit = "CHISQ.INV((" + StandardDeviationAlpha + ")/2," + DegreesOfFreedom + ")";
StandardDeviationLowerLimit = StandardDeviation + "*SQRT((" + DegreesOfFreedom + ")/" + StandardDeviationConfidenceIntervalLowerLimit + ")";
StandardDeviationUpperLimit = StandardDeviation + "*SQRT((" + DegreesOfFreedom + ")/" + StandardDeviationConfidenceIntervalUpperLimit + ")";
}
public void Execute()
{
foreach (var item in _entries)
{
var newaddr = AddressConverter.To2Offset(item.Address);
PCSX2_RAM.WriteInteger(newaddr, item.Value);
}
}
public async Task <AddressViewModel> GetAddressByIdAsync(int id, CancellationToken ct = default(CancellationToken))
{
var addressViewModel = AddressConverter.Convert(await _addressRepository.GetByIdAsync(id, ct));
addressViewModel.UserName =
_userRepository.GetByIdAsync(addressViewModel.UserId, ct).Result.Name +
_userRepository.GetByIdAsync(addressViewModel.UserId, ct).Result.Surname;
return(addressViewModel);
}
public void Can_read_null()
{
AddressConverter converter = new AddressConverter();
JsonReader reader = new JsonTextReader(new StringReader(""));
reader.ReadAsString();
Address result = converter.ReadJson(reader, typeof(Address), null, false, JsonSerializer.CreateDefault());
Assert.AreEqual(null, result);
}
public AddressViewModel GetAddressById(int id)
{
var addressViewModel = AddressConverter.Convert(_addressRepository.GetById(id));
addressViewModel.userName =
_userRepository.GetById(addressViewModel.userId).Name +
_userRepository.GetById(addressViewModel.userId).Surname;
return(addressViewModel);
}
public Address GetAddress(int id)
{
IList <DbParameter> parameters = new List <DbParameter>()
{
new MySqlParameter("@id", id)
};
DataRow row = _dbContext.GetDataRow(Tables.Address.Select, parameters);
return(AddressConverter.RowToAddress(row));
}
private Address GetCurrentAddress()
{
var dao = new AddressDao();
var converter = new AddressConverter();
var address = converter.Convert(dao.GetByDraftee(DrafteeId));
if (address == null)
{
address = new Address();
address.DrafteeId = DrafteeId;
}
return(address);
}
private void Button_Click(object sender, RoutedEventArgs e)
{
try
{
var adress = new IPv4Address(Ipv4TextBox.Text);
IPv6_Addresse.Text = AddressConverter.IPv4toIPv6(adress);
Binary_Addresse_Copy.Text = AddressConverter.IPtoBinary(Ipv4TextBox.Text);
}
catch (Exception ex)
{
TextBlock txtBlock = new TextBlock();
txtBlock.TextWrapping = TextWrapping.Wrap;
IPv6_Addresse.Text = "Bitte geben sie eine ordentliche IPv4 Addresse ein!";
}
}
private void Button_Click(object sender, RoutedEventArgs e)
{
try
{
var adress = new IPv4Address(IPBox.Text);
IPv6Label.Content = AddressConverter.IPv4toIPv6(adress);
AddressConverter.IPtoBinary(IPBox.Text);
}
catch (Exception ex)
{
TextBlock txtBlock = new TextBlock();
txtBlock.TextWrapping = TextWrapping.Wrap;
txtBlock.Text = ex.Message;
IPv6Label.Content = txtBlock;
}
}
public void ConvertAddressEntity_WhenValidEntity_ReturnViewModel()
{
var converter = new AddressConverter();
var viewModel = new Address
{
Street = "Washington Street",
ZipCode = "61-422",
Number = 25,
Id = 1,
ApartmentNumber = 22
};
var entity = new AddressViewModel();
converter.ConvertEntityToViewModel(viewModel, entity);
Assert.AreEqual(entity.Street, viewModel.Street);
}
public void SearchHeartBlock(int offset)
{
int HeartBlock = 0;
int barPointer = 0;
HeartBlock = AddressConverter.To2Offset(
PCSX2_RAM.SearchBytes(
BitConverter.GetBytes(0x0000 + _ucm), offset));
Console.WriteLine("Found possible HeartBlock at " + HeartBlock.ToString("X8"));
WriteToLog("Found possible HeartBlock at " + HeartBlock.ToString("X8"));
barPointer = AddressConverter.To2Offset(PCSX2_RAM.ReadInteger(HeartBlock + 16));
if (IsBARPointer(barPointer))
{
Console.WriteLine("BAR pointer at " + barPointer.ToString("X8"));
WriteToLog("BAR pointer at " + barPointer.ToString("X8"));
ReadBARFile(barPointer);
}
else
{
SearchHeartBlock(HeartBlock + 4);
}
}
protected virtual GetTaxRequest BuildGetTaxRequestUnbilled(POOrder order)
{
if (order == null)
{
throw new PXArgumentException(nameof(order));
}
Vendor vend = (Vendor)Base.vendor.View.SelectSingleBound(new object[] { order });
Location loc = (Location)Base.location.View.SelectSingleBound(new object[] { order });
IAddressBase fromAddress = GetFromAddress(order);
IAddressBase toAddress = GetToAddress(order);
if (fromAddress == null)
{
throw new PXException(Messages.FailedGetFromAddressSO);
}
if (toAddress == null)
{
throw new PXException(Messages.FailedGetToAddressSO);
}
GetTaxRequest request = new GetTaxRequest();
request.CompanyCode = CompanyCodeFromBranch(order.TaxZoneID, order.BranchID);
request.CurrencyCode = order.CuryID;
request.CustomerCode = vend.AcctCD;
request.OriginAddress = AddressConverter.ConvertTaxAddress(fromAddress);
request.DestinationAddress = AddressConverter.ConvertTaxAddress(toAddress);
request.DocCode = string.Format("PO.{0}.{1}", order.OrderType, order.OrderNbr);
request.DocDate = order.OrderDate.GetValueOrDefault();
request.LocationCode = GetExternalTaxProviderLocationCode(order);
int mult = 1;
request.CustomerUsageType = loc.CAvalaraCustomerUsageType;
if (!string.IsNullOrEmpty(loc.CAvalaraExemptionNumber))
{
request.ExemptionNo = loc.CAvalaraExemptionNumber;
}
request.DocType = TaxDocumentType.PurchaseOrder;
PXSelectBase <POLine> select = new PXSelectJoin <POLine,
LeftJoin <InventoryItem, On <POLine.FK.InventoryItem>,
LeftJoin <Account, On <Account.accountID, Equal <InventoryItem.salesAcctID> > > >,
Where <POLine.orderType, Equal <Current <POOrder.orderType> >, And <POLine.orderNbr, Equal <Current <POOrder.orderNbr> > > >,
OrderBy <Asc <POLine.lineNbr> > >(Base);
foreach (PXResult <POLine, InventoryItem, Account> res in select.View.SelectMultiBound(new object[] { order }))
{
POLine tran = (POLine)res;
InventoryItem item = (InventoryItem)res;
Account salesAccount = (Account)res;
if (tran.UnbilledAmt > 0)
{
var line = new TaxCartItem();
line.Index = tran.LineNbr ?? 0;
line.Amount = mult * tran.CuryUnbilledAmt.GetValueOrDefault();
line.Description = tran.TranDesc;
line.OriginAddress = AddressConverter.ConvertTaxAddress(GetFromAddress(order, tran));
line.DestinationAddress = AddressConverter.ConvertTaxAddress(GetToAddress(order, tran));
line.ItemCode = item.InventoryCD;
line.Quantity = tran.BaseUnbilledQty.GetValueOrDefault();
line.Discounted = request.Discount > 0;
line.RevAcct = salesAccount.AccountCD;
line.TaxCode = tran.TaxCategoryID;
request.CartItems.Add(line);
}
}
return(request);
}
public static int ReadStringsTable(byte[] bytes, int startingIndex, int stringsPerEntry, AddressConverter converter, Action <int, string[]> processTableEntry)
{
var strings = new List <string>();
var pos = startingIndex;
var numTableEntries = ConvertByteArrayToInt32(bytes, pos);
pos += 4;
for (var entry = 0; entry < numTableEntries; ++entry)
{
var offset = converter(ConvertByteArrayToInt32(bytes, pos));
pos += 4;
strings.Clear();
for (var j = 0; j < stringsPerEntry; ++j)
{
pos += ReadNullTerminatedString(bytes, pos, out var str);
strings.Add(str);
}
processTableEntry(offset, strings.ToArray());
}
return(pos - startingIndex);
}
public void ReadMoveset(Moveset mset)
{
Console.WriteLine("ReadMoveset Method - " + mset.ANBAddress.ToString("x8") + " - " + mset.Name);
WriteToLog("ReadMoveset Method - " + mset.ANBAddress.ToString("x8") + " - " + mset.Name);
int startBarOff = 0;
startBarOff = AddressConverter.To2Offset(mset.ANBAddress + 20);
MovesetAnimation manim = new MovesetAnimation();
manim.Address = startBarOff + 4;
manim.Value = PCSX2_RAM.ReadInteger(manim.Address);
mset.Animation = manim;
MovesetEffect meffect = new MovesetEffect();
meffect.Address = startBarOff + 20;
meffect.Value = PCSX2_RAM.ReadInteger(meffect.Address);
meffect.BoneStructure = new MovesetEffectBoneStructure();
meffect.BoneStructure.Address = startBarOff + 32;
meffect.BoneStructure.Value = PCSX2_RAM.ReadInteger(meffect.BoneStructure.Address);
//Wth can effects have a 0 value??
if (meffect.Value != 0)
{
//Read Effect Data Modifiers (name according to Xaddgx)
List <MovesetEffectDataModifierRaw> modifiers = new List <MovesetEffectDataModifierRaw>();
int startOfDataModifiers = AddressConverter.To2Offset(meffect.Value);
string tempDataMod = string.Empty;
int countertemp = 1;
while (tempDataMod != "BAR")
{
Console.WriteLine("Reading effect data modifier - " + startOfDataModifiers.ToString("X8"));
WriteToLog("Reading effect data modifier - " + startOfDataModifiers.ToString("X8"));
MovesetEffectDataModifierRaw mod = new MovesetEffectDataModifierRaw();
mod.Address = startOfDataModifiers;
mod.Value = PCSX2_RAM.ReadInteger(AddressConverter.To2Offset(startOfDataModifiers));
mod.DisplayNumber = countertemp;
modifiers.Add(mod);
startOfDataModifiers += 4;
countertemp++;
tempDataMod = PCSX2_RAM.ReadString(startOfDataModifiers);
}
meffect.DataModifiers = modifiers;
}
//Parsing that into Effect Casters (name according to SoraikoSan)
//if (meffect.Value != 0)
//{
// ECasterList eclist = new ECasterList();
// eclist.Children = new List<ECaster>();
// var start = AddressConverter.To2Offset(meffect.Value);
// eclist.Header = PCSX2_RAM.ReadBytes(start, 4);
// eclist.Group1Entries = Convert.ToInt32(eclist.Header[0]);
// eclist.Group2Entries = Convert.ToInt32(eclist.Header[1]);
// start += 4;
// for(int i = 0; i<eclist.Group1Entries; i++)
// {
// ECaster ec = new ECaster();
// ec.StartAddress = start;
// ec.Group = ECasterGroup.Group1;
// ec.StartAnimationFrame = PCSX2_RAM.ReadShort(start);
// ec.EndAnimationFrame = PCSX2_RAM.ReadShort(start + 2);
// ec.EffectType = PCSX2_RAM.ReadBytes(start + 4, 1)[0];
// Group1Effects eff = (Group1Effects)ec.EffectType;
// var addbytes = PCSX2_RAM.ReadBytes(start + 5, 1)[0];
// if (addbytes > 0)
// {
// ec.AdditionalBytes = PCSX2_RAM.ReadBytes(start+6, addbytes*2);
// }
// ec.Length = 6 + (addbytes * 2);
// eclist.Children.Add(ec);
// start += ec.Length;
// }
// for (int j = 0; j<eclist.Group2Entries; j++)
// {
// ECaster ec = new ECaster();
// ec.StartAddress = start;
// ec.Group = ECasterGroup.Group2;
// ec.StartAnimationFrame = PCSX2_RAM.ReadShort(start);
// ec.EffectType = PCSX2_RAM.ReadBytes(start + 2, 1)[0];
// Group2Effects ec2 = (Group2Effects)ec.EffectType;
// var addbytes = PCSX2_RAM.ReadBytes(start + 3, 1)[0];
// if (addbytes > 0)
// {
// ec.AdditionalBytes = PCSX2_RAM.ReadBytes(start + 4, addbytes * 2);
// }
// ec.Length = 4 + (addbytes * 2);
// eclist.Children.Add(ec);
// start += ec.Length;
// }
// meffect.EffectCasterList = eclist;
//}
mset.Effect = meffect;
_movesets.Add(mset);
}
public void createOneWayAnova(List <Variable> variables)
{
_Worksheet sheet = WorksheetHelper.NewWorksheet("One-Way ANOVA");
sheet.Cells[1, 1] = "ANOVA Summary";
sheet.Cells[2, 1] = "Total Sample Size";
sheet.Cells[3, 1] = "Grand Mean";
sheet.Cells[4, 1] = "Pooled Std Dev";
sheet.Cells[5, 1] = "Pooled Variance";
sheet.Cells[6, 1] = "Number of Samples";
sheet.Cells[7, 1] = "Confidence Level";
sheet.Cells[9, 1] = "ANOVA Sample Stats";
sheet.Cells[10, 1] = "Sample Size";
sheet.Cells[11, 1] = "Sample Mean";
sheet.Cells[12, 1] = "Sample Std Dev";
sheet.Cells[13, 1] = "Sample Variance";
sheet.Cells[14, 1] = "Pooling Weight";
sheet.Cells[17, 1] = "One-Way ANOVA Table";
sheet.Cells[18, 1] = "Between Variation";
sheet.Cells[19, 1] = "Within Variation";
sheet.Cells[20, 1] = "Total Variation";
sheet.Cells[23, 1] = "Confidence Interval Tests";
sheet.Cells[16, 2] = "Sum of";
sheet.Cells[17, 2] = "Squares";
sheet.Cells[16, 3] = "Degrees of";
sheet.Cells[17, 3] = "Freedom";
sheet.Cells[16, 4] = "Mean";
sheet.Cells[17, 4] = "Squares";
sheet.Cells[17, 5] = "F-Ratio";
sheet.Cells[17, 6] = "p-Value";
sheet.Cells[22, 2] = "Difference";
sheet.Cells[23, 2] = " of Means";
int col = 1;
foreach (Variable variable in variables)
{
col++;
var range = variable.getRange().Address(true, true, true);
sheet.Cells[9, col] = variable.name;
sheet.WriteFunction(10, col, "COUNT(" + range + ")");
sheet.WriteFunction(11, col, "AVERAGE(" + range + ")");
sheet.WriteFunction(12, col, "STDEV.S(" + range + ")");
sheet.WriteFunction(13, col, "VAR.S(" + range + ")");
}
sheet.WriteFunction(2, 2, "SUM(" + AddressConverter.CellAddress(10, 2, false, false) + ":" + AddressConverter.CellAddress(10, col, false, false) + ")");
sheet.WriteFunction(3, 2, "SUMPRODUCT(" + AddressConverter.CellAddress(10, 2, false, false) + ":" + AddressConverter.CellAddress(10, col, false, false) + "," + AddressConverter.CellAddress(11, 2, false, false) + ":" + AddressConverter.CellAddress(11, col, false, false) + ")/" + AddressConverter.CellAddress(2, 2, false, false));
sheet.Cells[6, 2] = variables.Count;
sheet.Cells[7, 2] = model.confidenceLevel;
((Range)sheet.Cells[7, 2]).NumberFormat = "0.00%";
col = 1;
foreach (Variable variable in variables)
{
col++;
sheet.WriteFunction(14, col, "(" + AddressConverter.CellAddress(10, col, false, false) + "-1)/(B2-B6)");
}
sheet.WriteFunction(5, 2, "SUMPRODUCT(" + AddressConverter.CellAddress(13, 2, false, false) + ":" + AddressConverter.CellAddress(13, col, false, false) + "," + AddressConverter.CellAddress(14, 2, false, false) + ":" + AddressConverter.CellAddress(14, col, false, false) + ")");
sheet.WriteFunction(4, 2, "SQRT(" + AddressConverter.CellAddress(5, 2, false, false) + ")");
sheet.WriteFunction(18, 2, "SUMPRODUCT(" + AddressConverter.CellAddress(10, 2, false, false) + ":" + AddressConverter.CellAddress(10, col, false, false) + ",(" + AddressConverter.CellAddress(11, 2, false, false) + ":" + AddressConverter.CellAddress(11, col, false, false) + "-B3)^2)");
sheet.WriteFunction(19, 2, "(B2-" + variables.Count + ")*B5");
sheet.WriteFunction(20, 2, "B18+B19");
sheet.Cells[18, 3] = variables.Count - 1;
sheet.WriteFunction(19, 3, "B2-" + variables.Count);
sheet.WriteFunction(20, 3, "C18+C19");
sheet.WriteFunction(18, 4, "B18/C18");
sheet.WriteFunction(19, 4, "B19/C19");
sheet.WriteFunction(18, 5, "D18/D19");
sheet.WriteFunction(18, 6, "F.DIST.RT(E18,C18,C19)");
int row = 24;
int c = 0;
for (int i = 0; i < variables.Count; i++)
{
for (int j = i + 1; j < variables.Count; j++)
{
c++;
Variable var1 = variables[i];
Variable var2 = variables[j];
sheet.Cells[row, 1] = var1.name + " - " + var2.name;
sheet.WriteFunction(row, 2, AddressConverter.CellAddress(11, i + 2, false, false) + "-" + AddressConverter.CellAddress(11, j + 2, false, false));
col = 3;
if (model.noCorrection)
{
sheet.WriteFunction(row, col++, AddressConverter.CellAddress(row, 2, false, false) + "-(ABS(T.INV((1-B7)/2,C19)))*SQRT(D19*(1/" + AddressConverter.CellAddress(10, i + 2, false, false) + "+1/" + AddressConverter.CellAddress(10, j + 2, false, false) + "))");
sheet.WriteFunction(row, col++, AddressConverter.CellAddress(row, 2, false, false) + "+(ABS(T.INV((1-B7)/2,C19)))*SQRT(D19*(1/" + AddressConverter.CellAddress(10, i + 2, false, false) + "+1/" + AddressConverter.CellAddress(10, j + 2, false, false) + "))");
}
if (model.bonferroni)
{
sheet.WriteFunction(row, col++, AddressConverter.CellAddress(row, 2, false, false) + "-(ABS(T.INV(((1-B7)/(B6*(B6-1)/2))/2,C19)))*SQRT(D19*(1/" + AddressConverter.CellAddress(10, i + 2, false, false) + "+1/" + AddressConverter.CellAddress(10, j + 2, false, false) + "))");
sheet.WriteFunction(row, col++, AddressConverter.CellAddress(row, 2, false, false) + "+(ABS(T.INV(((1-B7)/(B6*(B6-1)/2))/2,C19)))*SQRT(D19*(1/" + AddressConverter.CellAddress(10, i + 2, false, false) + "+1/" + AddressConverter.CellAddress(10, j + 2, false, false) + "))");
}
if (model.scheffe)
{
sheet.WriteFunction(row, col++, AddressConverter.CellAddress(row, 2, false, false) + "-SQRT((B6-1)*F.INV.RT(1-B7,B6-1,C19))*SQRT(D19*(1/" + AddressConverter.CellAddress(10, i + 2, false, false) + "+1/" + AddressConverter.CellAddress(10, j + 2, false, false) + "))");
sheet.WriteFunction(row, col, AddressConverter.CellAddress(row, 2, false, false) + "+SQRT((B6-1)*F.INV.RT(1-B7,B6-1,C19))*SQRT(D19*(1/" + AddressConverter.CellAddress(10, i + 2, false, false) + "+1/" + AddressConverter.CellAddress(10, j + 2, false, false) + "))");
}
row++;
}
}
col = 3;
if (model.noCorrection)
{
sheet.Cells[22, col] = "No Correction";
sheet.Range[AddressConverter.CellAddress(22, col, false, false), AddressConverter.CellAddress(22, col + 1, false, false)].Merge();
sheet.Cells[23, col] = "Lower";
sheet.Cells[23, col + 1] = "Upper";
sheet.Range[AddressConverter.CellAddress(23, col, false, false), AddressConverter.CellAddress(23, col + 1, false, false)].Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble;
sheet.Range[AddressConverter.CellAddress(24, col, false, false), AddressConverter.CellAddress(23 + c, col, false, false)].Borders[XlBordersIndex.xlEdgeLeft].LineStyle = XlLineStyle.xlDot;
col += 2;
}
if (model.bonferroni)
{
sheet.Cells[22, col] = "Bonferroni";
sheet.Range[AddressConverter.CellAddress(22, col, false, false), AddressConverter.CellAddress(22, col + 1, false, false)].Merge();
sheet.Cells[23, col] = "Lower";
sheet.Cells[23, col + 1] = "Upper";
sheet.Range[AddressConverter.CellAddress(23, col, false, false), AddressConverter.CellAddress(23, col + 1, false, false)].Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble;
sheet.Range[AddressConverter.CellAddress(24, col, false, false), AddressConverter.CellAddress(23 + c, col, false, false)].Borders[XlBordersIndex.xlEdgeLeft].LineStyle = XlLineStyle.xlDot;
col += 2;
}
if (model.scheffe)
{
sheet.Cells[22, col] = "Scheffe";
sheet.Range[AddressConverter.CellAddress(22, col, false, false), AddressConverter.CellAddress(22, col + 1, false, false)].Merge();
sheet.Cells[23, col] = "Lower";
sheet.Cells[23, col + 1] = "Upper";
sheet.Range[AddressConverter.CellAddress(23, col, false, false), AddressConverter.CellAddress(23, col + 1, false, false)].Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble;
sheet.Range[AddressConverter.CellAddress(24, col, false, false), AddressConverter.CellAddress(23 + c, col, false, false)].Borders[XlBordersIndex.xlEdgeLeft].LineStyle = XlLineStyle.xlDot;
}
((Range)sheet.Cells[1, 1]).EntireColumn.AutoFit();
((Range)sheet.Cells[1, 2]).EntireColumn.AutoFit();
((Range)sheet.Cells[1, 3]).EntireColumn.AutoFit();
((Range)sheet.Cells[1, 4]).EntireColumn.AutoFit();
((Range)sheet.Cells[1, 5]).EntireColumn.AutoFit();
((Range)sheet.Cells[1, 6]).EntireColumn.AutoFit();
((Range)sheet.Cells[1, 7]).EntireColumn.AutoFit();
((Range)sheet.Cells[1, 8]).EntireColumn.AutoFit();
sheet.Range["B1", "J200"].Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter;
sheet.Range["A1", "B1"].Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble;
sheet.Range["A9", AddressConverter.CellAddress(9, variables.Count + 1, false, false)].Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble;
sheet.Range["A17", "F17"].Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble;
sheet.Range["A23", "B17"].Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble;
sheet.Range["B3", "B5"].NumberFormat = "0.0000";
sheet.Range["B11", AddressConverter.CellAddress(14, variables.Count + 1, false, false)].NumberFormat = "0.000";
sheet.Range["B18", "B20"].NumberFormat = "0.0000";
sheet.Range["D18", "E19"].NumberFormat = "0.0000";
sheet.Range["B24", AddressConverter.CellAddress(23 + c, 2, false, false)].NumberFormat = "0.0000";
sheet.Range["C24", AddressConverter.CellAddress(23 + c, 8, false, false)].NumberFormat = "0.000000";
sheet.Range["C24", AddressConverter.CellAddress(23 + c, 8, false, false)].Cells.HorizontalAlignment = XlHAlign.xlHAlignRight;
Globals.ExcelAddIn.Application.ActiveWindow.DisplayGridlines = false;
}
public void createChiKwadraatPlot(DataSet dataSet)
{
string range = model.range.Replace("$", null);
//string range = model.range;
//string[] splitted = range.Split(':');
string first = range.Split(':')[0];
string second = range.Split(':')[1];
/*if (splitted[0].Length > 4)
* {
* int i = splitted[0].Length;
* first = splitted[0][1] + Convert.ToString(splitted[0][3]);
* int j = 4;
* while (j < i)
* {
* first = first + Convert.ToString(splitted[0][j]);
* j++;
* }
* }
* else
* {
* first = splitted[0][1] + Convert.ToString(splitted[0][3]);
* }
* if (splitted[1].Length > 4)
* {
* int i = splitted[1].Length;
* second = splitted[1][1] + Convert.ToString(splitted[1][3]);
* int j = 4;
* while (j < i)
* {
* second = second + Convert.ToString(splitted[1][j]);
* j++;
* }
* }
* else
* {
* second = splitted[1][1] + Convert.ToString(splitted[1][3]);
* }*/
_Worksheet sheet = WorksheetHelper.NewWorksheet("Chi Square");
Range from = dataSet.getWorksheet().Range[first, second];
int rangeWidth = from.Columns.Count;
int rangeHeight = from.Rows.Count;
//original Counts
sheet.Cells[2, 1] = "Original Counts";
//sheet.get_Range("A2").Cells.HorizontalAlignment = XlHAlign.xlHAlignFill;
sheet.get_Range("A2", AddressConverter.CellAddress(2, rangeWidth + 2, false, false)).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble;
Range to = sheet.Range[sheet.Cells[3, 2], sheet.Cells[3, 2].offset(rangeHeight, rangeWidth)];
from.Copy(to);
int col = 2;
while (col < rangeWidth + 2)
{
int row = 3;
double sum = 0;
while (row < rangeHeight + 3)
{
sum += Convert.ToDouble((sheet.Cells[row, col] as Range).Value);
row++;
}
sheet.Cells[3 + rangeHeight, col] = sum;
col++;
}
sheet.Cells[2, col] = "Total";
int roww = 3;
while (roww < rangeHeight + 4)
{
int coll = 2;
double summ = 0;
while (coll < rangeWidth + 2)
{
summ += Convert.ToDouble((sheet.Cells[roww, coll] as Range).Value);
coll++;
}
sheet.Cells[roww, 2 + rangeWidth] = summ;
roww++;
}
sheet.Cells[roww - 1, 1] = "Total";
// percentage of Rows
sheet.Cells[rangeHeight + 6, 1] = "Percentage of Rows";
sheet.get_Range("A2", AddressConverter.CellAddress(rangeHeight + 6, rangeWidth + 1, false, false)).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble;
int rowb = rangeHeight + 7;
while (rowb < rangeHeight + rangeHeight + 7)
{
int cola = 2;
while (cola < rangeWidth + 2)
{
double proda = 0;
proda = Convert.ToDouble((sheet.Cells[rowb - rangeHeight - 4, cola] as Range).Value) / Convert.ToDouble((sheet.Cells[rowb - rangeHeight - 4, rangeWidth + 2] as Range).Value);
sheet.Cells[rowb, cola] = Math.Round(proda * 100, 2);
//sheet.Range[rowb, cola].NumberFormat = "0.00%";
cola++;
}
rowb++;
}
//Percentage of Colums
sheet.Cells[rangeHeight + rangeHeight + 9, 1] = "Percentage of Colums";
sheet.get_Range("A2", AddressConverter.CellAddress(2 * rangeHeight + 9, rangeWidth + 1, false, false)).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble;
int rowd = rangeHeight + rangeHeight + 10;
while (rowd < rangeHeight + rangeHeight + rangeHeight + 10)
{
int colc = 2;
while (colc < rangeWidth + 2)
{
double prodc = 0;
prodc = Convert.ToDouble((sheet.Cells[rowd - rangeHeight - rangeHeight - 7, colc] as Range).Value) / Convert.ToDouble((sheet.Cells[3 + rangeHeight, colc] as Range).Value);
sheet.Cells[rowd, colc] = Math.Round(prodc * 100, 2);
//sheet.Range[rowd, colc].NumberFormat = "0.00%";
colc++;
}
rowd++;
}
// Expected Counts
sheet.Cells[3 * (rangeHeight) + 12, 1] = "Expected Counts";
sheet.get_Range("A2", AddressConverter.CellAddress(3 * rangeHeight + 12, rangeWidth + 1, false, false)).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble;
int rowe = 3 * rangeHeight + 13;
while (rowe < 4 * (rangeHeight) + 13)
{
int cole = 2;
while (cole < rangeWidth + 2)
{
double prode = 0;
prode = Convert.ToDouble((sheet.Cells[rangeHeight + 3, cole] as Range).Value)
* Convert.ToDouble((sheet.Cells[rowe - 3 * (rangeHeight) - 10, rangeWidth + 2] as Range).Value)
/ Convert.ToDouble((sheet.Cells[rangeHeight + 3, rangeWidth + 2] as Range).Value);
sheet.Cells[rowe, cole] = prode;
cole++;
}
rowe++;
}
//distance ftom expected
sheet.Cells[4 * rangeHeight + 15, 1] = "Distance from Expected";
sheet.get_Range("A2", AddressConverter.CellAddress(4 * rangeHeight + 15, rangeWidth + 1, false, false)).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble;
int rowf = 4 * rangeHeight + 16;
while (rowf < 5 * rangeHeight + 16)
{
int colf = 2;
while (colf < rangeWidth + 2)
{
double prodf = 0;
double tussenprod = Convert.ToDouble((sheet.Cells[rowf - 4 * rangeHeight - 13, colf] as Range).Value)
- Convert.ToDouble((sheet.Cells[rowf - rangeHeight - 3, colf] as Range).Value);
prodf = Math.Pow(tussenprod, 2)
/ Convert.ToDouble((sheet.Cells[rowf - rangeHeight - 3, colf] as Range).Value);
sheet.Cells[rowf, colf] = Math.Round(prodf, 4);
colf++;
}
rowf++;
}
//chi square dings
sheet.Cells[5 * rangeHeight + 18, 1] = "Chi-Square Statistic";
sheet.Cells[5 * rangeHeight + 19, 1] = "CHi-Square";
sheet.Cells[5 * rangeHeight + 20, 1] = "p-value";
//=1-CHIKW.VERD(B39;3*3;1)
sheet.get_Range("A2", AddressConverter.CellAddress(5 * rangeHeight + 18, 2, false, false)).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble;
int rowg = 4 * rangeHeight + 16;
double som = 0;
while (rowg < 5 * rangeHeight + 16)
{
int colg = 2;
while (colg < rangeWidth + 2)
{
som += Convert.ToDouble((sheet.Cells[rowg, colg] as Range).Value);
colg++;
}
rowg++;
}
sheet.Cells[5 * rangeHeight + 19, 2] = som;
sheet.Cells[5 * rangeHeight + 20, 2] = sheet.Application.WorksheetFunction.ChiSq_Dist(som, (rangeHeight - 1) * (rangeWidth - 1), false);
//autofit
((Range)sheet.Cells[1, 1]).EntireColumn.AutoFit();
//((Range)sheet.Cells[1, 2]).EntireColumn.AutoFit();
//((Range)sheet.Cells[1, 3]).EntireColumn.AutoFit();
//((Range)sheet.Cells[1, 4]).EntireColumn.AutoFit();
//((Range)sheet.Cells[1, 5]).EntireColumn.AutoFit();
//((Range)sheet.Cells[1, 6]).EntireColumn.AutoFit();
//((Range)sheet.Cells[1, 7]).EntireColumn.AutoFit();
//((Range)sheet.Cells[1, 8]).EntireColumn.AutoFit();
//((Range)sheet.Cells[1, 9]).EntireColumn.AutoFit();
//((Range)sheet.Cells[1, 10]).EntireColumn.AutoFit();
// Merge Headers
sheet.Range[sheet.Cells[1, 2], sheet.Cells[1, rangeWidth + 2]].Merge();
sheet.Range[sheet.Cells[rangeHeight + 5, 2], sheet.Cells[rangeHeight + 5, rangeWidth + 1]].Merge();
sheet.Range[sheet.Cells[2 * rangeHeight + 8, 2], sheet.Cells[2 * rangeHeight + 8, rangeWidth + 1]].Merge();
sheet.Range[sheet.Cells[3 * rangeHeight + 11, 2], sheet.Cells[3 * rangeHeight + 11, rangeWidth + 1]].Merge();
sheet.Range[sheet.Cells[4 * rangeHeight + 14, 2], sheet.Cells[4 * rangeHeight + 14, rangeWidth + 1]].Merge();
// Build Headers
var header = "";
if (model.hasRowTitle)
{
header += "Rows: {0}";
}
if (model.hasRowTitle && model.hasColTitle)
{
header += " / ";
}
if (model.hasColTitle)
{
header += "Columns: {1}";
}
header = String.Format(header, model.rowTitle, model.colTitle);
// Fill in Headers
sheet.Cells[1, 2] = header;
sheet.Cells[rangeHeight + 5, 2] = header;
sheet.Cells[2 * rangeHeight + 8, 2] = header;
sheet.Cells[3 * rangeHeight + 11, 2] = header;
sheet.Cells[4 * rangeHeight + 14, 2] = header;
// Center Headers
sheet.Cells[1, 2].HorizontalAlignment = XlHAlign.xlHAlignCenter;
sheet.Cells[rangeHeight + 5, 2].HorizontalAlignment = XlHAlign.xlHAlignCenter;
sheet.Cells[2 * rangeHeight + 8, 2].HorizontalAlignment = XlHAlign.xlHAlignCenter;
sheet.Cells[3 * rangeHeight + 11, 2].HorizontalAlignment = XlHAlign.xlHAlignCenter;
sheet.Cells[4 * rangeHeight + 14, 2].HorizontalAlignment = XlHAlign.xlHAlignCenter;
// Row and column titles
if (model.hasRowColHeaders)
{
String[] rowTitles = new String[rangeHeight];
String[] colTitles = new String[rangeWidth];
for (int i = 0; i < rangeHeight; i++)
{
rowTitles[i] = dataSet.getWorksheet().Range[first].Offset[i, -1].Value;
}
for (int i = 0; i < rangeWidth; i++)
{
colTitles[i] = dataSet.getWorksheet().Range[first].Offset[-1, i].Value;
}
for (int i = 0; i < rangeHeight; i++)
{
sheet.Cells[3 + i, 1] = rowTitles[i];
sheet.Cells[7 + rangeHeight + i, 1] = rowTitles[i];
sheet.Cells[10 + 2 * rangeHeight + i, 1] = rowTitles[i];
sheet.Cells[13 + 3 * rangeHeight + i, 1] = rowTitles[i];
sheet.Cells[16 + 4 * rangeHeight + i, 1] = rowTitles[i];
}
for (int i = 0; i < rangeWidth; i++)
{
sheet.Cells[2, 2 + i] = colTitles[i];
sheet.Cells[6 + rangeHeight, 2 + i] = colTitles[i];
sheet.Cells[9 + 2 * rangeHeight, 2 + i] = colTitles[i];
sheet.Cells[12 + 3 * rangeHeight, 2 + i] = colTitles[i];
sheet.Cells[15 + 4 * rangeHeight, 2 + i] = colTitles[i];
}
}
}
protected GetTaxRequest BuildGetTaxRequest(FSServiceOrder order)
{
if (order == null)
{
throw new PXArgumentException(ErrorMessages.ArgumentNullException);
}
BAccount cust = (BAccount)PXSelect <BAccount,
Where <BAccount.bAccountID, Equal <Required <BAccount.bAccountID> > > > .
Select(Base, order.BillCustomerID);
Location loc = (Location)PXSelect <Location,
Where <Location.bAccountID, Equal <Required <Location.bAccountID> >, And <Location.locationID, Equal <Required <Location.locationID> > > > > .
Select(Base, order.BillCustomerID, order.BillLocationID);
IAddressBase addressFrom = GetFromAddress();
IAddressBase addressTo = GetToAddress(order);
if (addressFrom == null)
{
throw new PXException(PX.Objects.CR.Messages.FailedGetFromAddressSO);
}
if (addressTo == null)
{
throw new PXException(PX.Objects.CR.Messages.FailedGetToAddressSO);
}
GetTaxRequest request = new GetTaxRequest();
request.CompanyCode = CompanyCodeFromBranch(order.TaxZoneID, Base.Accessinfo.BranchID);
request.CurrencyCode = order.CuryID;
request.CustomerCode = cust.AcctCD;
request.OriginAddress = AddressConverter.ConvertTaxAddress(addressFrom);
request.DestinationAddress = AddressConverter.ConvertTaxAddress(addressTo);
request.DocCode = $"CR.{order.SOID}";
request.DocDate = order.OrderDate.GetValueOrDefault();
int mult = 1;
if (!string.IsNullOrEmpty(loc.CAvalaraCustomerUsageType))
{
request.CustomerUsageType = loc.CAvalaraCustomerUsageType;
}
if (!string.IsNullOrEmpty(loc.CAvalaraExemptionNumber))
{
request.ExemptionNo = loc.CAvalaraExemptionNumber;
}
request.DocType = TaxDocumentType.SalesOrder;
PXSelectBase <FSSODet> select = new PXSelectJoin <FSSODet,
LeftJoin <InventoryItem, On <InventoryItem.inventoryID, Equal <FSSODet.inventoryID> >,
LeftJoin <Account, On <Account.accountID, Equal <InventoryItem.salesAcctID> > > >,
Where <FSSODet.sOID, Equal <Current <FSServiceOrder.sOID> > >,
OrderBy <Asc <FSSODet.lineNbr> > >(Base);
foreach (PXResult <FSSODet, InventoryItem, Account> res in select.View.SelectMultiBound(new object[] { order }))
{
FSSODet tran = (FSSODet)res;
InventoryItem item = (InventoryItem)res;
Account salesAccount = (Account)res;
var line = new TaxCartItem();
line.Index = tran.LineNbr ?? 0;
line.Amount = mult * tran.CuryBillableTranAmt.GetValueOrDefault();
line.Description = tran.TranDesc;
line.DestinationAddress = request.DestinationAddress;
line.OriginAddress = request.OriginAddress;
line.ItemCode = item.InventoryCD;
line.Quantity = Math.Abs(tran.BillableQty.GetValueOrDefault());
line.Discounted = request.Discount > 0;
line.RevAcct = salesAccount.AccountCD;
line.TaxCode = tran.TaxCategoryID;
request.CartItems.Add(line);
}
return(request);
}
public async Task <List <AddressViewModel> > GetAddressesByUserIdAsync(int id, CancellationToken ct = default(CancellationToken))
{
return(AddressConverter.ConvertList(await _addressRepository.GetByUserIdAsync(id, ct)));
}
protected virtual GetTaxRequest BuildGetTaxRequest(ARCashSale invoice)
{
if (invoice == null)
{
throw new PXArgumentException(nameof(invoice), ErrorMessages.ArgumentNullException);
}
Customer cust = (Customer)Base.customer.View.SelectSingleBound(new object[] { invoice });
Location loc = (Location)Base.location.View.SelectSingleBound(new object[] { invoice });
GetTaxRequest request = new GetTaxRequest();
request.CompanyCode = CompanyCodeFromBranch(invoice.TaxZoneID, invoice.BranchID);
request.CurrencyCode = invoice.CuryID;
request.CustomerCode = cust.AcctCD;
IAddressBase fromAddress = GetFromAddress(invoice);
IAddressBase toAddress = GetToAddress(invoice);
if (fromAddress == null)
{
throw new PXException(Messages.FailedGetFrom);
}
if (toAddress == null)
{
throw new PXException(Messages.FailedGetTo);
}
request.OriginAddress = AddressConverter.ConvertTaxAddress(fromAddress);
request.DestinationAddress = AddressConverter.ConvertTaxAddress(toAddress);
request.DocCode = $"AR.{invoice.DocType}.{invoice.RefNbr}";
request.DocDate = invoice.DocDate.GetValueOrDefault();
request.LocationCode = GetExternalTaxProviderLocationCode(invoice);
request.CustomerUsageType = invoice.AvalaraCustomerUsageType;
if (!string.IsNullOrEmpty(loc.CAvalaraExemptionNumber))
{
request.ExemptionNo = loc.CAvalaraExemptionNumber;
}
request.DocType = GetTaxDocumentType(invoice);
Sign sign = GetDocumentSign(invoice);
PXSelectBase <ARTran> select = new PXSelectJoin <ARTran,
LeftJoin <InventoryItem, On <InventoryItem.inventoryID, Equal <ARTran.inventoryID> >,
LeftJoin <Account, On <Account.accountID, Equal <ARTran.accountID> > > >,
Where <ARTran.tranType, Equal <Current <ARCashSale.docType> >,
And <ARTran.refNbr, Equal <Current <ARCashSale.refNbr> >,
And <Where <ARTran.lineType, NotEqual <SOLineType.discount>, Or <ARTran.lineType, IsNull> > > > >,
OrderBy <Asc <ARTran.tranType, Asc <ARTran.refNbr, Asc <ARTran.lineNbr> > > > >(Base);
request.Discount = GetDocDiscount().GetValueOrDefault();
DateTime?taxDate = invoice.OrigDocDate;
foreach (PXResult <ARTran, InventoryItem, Account> res in select.View.SelectMultiBound(new object[] { invoice }))
{
ARTran tran = (ARTran)res;
InventoryItem item = (InventoryItem)res;
Account salesAccount = (Account)res;
var line = new TaxCartItem();
line.Index = tran.LineNbr ?? 0;
line.Amount = sign * tran.CuryTranAmt.GetValueOrDefault();
line.Description = tran.TranDesc;
line.DestinationAddress = request.DestinationAddress;
line.OriginAddress = request.OriginAddress;
line.ItemCode = item.InventoryCD;
line.Quantity = Math.Abs(tran.Qty.GetValueOrDefault());
line.Discounted = request.Discount > 0;
line.RevAcct = salesAccount.AccountCD;
line.TaxCode = tran.TaxCategoryID;
if (tran.OrigInvoiceDate != null)
{
taxDate = tran.OrigInvoiceDate;
}
request.CartItems.Add(line);
}
if (invoice.DocType == ARDocType.CashReturn && invoice.OrigDocDate != null)
{
request.TaxOverride.Reason = Messages.ReturnReason;
request.TaxOverride.TaxDate = taxDate.Value;
request.TaxOverride.TaxOverrideType = TaxOverrideType.TaxDate;
}
return(request);
}
protected virtual GetTaxRequest BuildGetTaxRequest(CAAdj invoice)
{
if (invoice == null)
{
throw new PXArgumentException(nameof(invoice), ErrorMessages.ArgumentNullException);
}
GetTaxRequest request = new GetTaxRequest();
request.CompanyCode = CompanyCodeFromBranch(invoice.TaxZoneID, invoice.BranchID);
request.CurrencyCode = invoice.CuryID;
request.CustomerCode = "N/A";
IAddressBase fromAddress = GetToAddress(invoice);
IAddressBase toAddress = fromAddress;
if (fromAddress == null)
{
throw new PXException(Messages.FailedGetFrom);
}
if (toAddress == null)
{
throw new PXException(Messages.FailedGetTo);
}
request.OriginAddress = AddressConverter.ConvertTaxAddress(fromAddress);
request.DestinationAddress = AddressConverter.ConvertTaxAddress(toAddress);
request.DocCode = $"CA.{invoice.AdjRefNbr}";
request.DocDate = invoice.TranDate.GetValueOrDefault();
request.LocationCode = GetExternalTaxProviderLocationCode(invoice);
Location branchLoc = GetBranchLocation(invoice);
if (branchLoc != null)
{
request.CustomerUsageType = branchLoc.CAvalaraCustomerUsageType;
request.ExemptionNo = branchLoc.CAvalaraExemptionNumber;
}
request.DocType = TaxDocumentType.PurchaseInvoice;
int mult = 1;
if (invoice.DrCr == CADrCr.CADebit)
{
request.DocType = TaxDocumentType.SalesInvoice;
}
else
{
request.DocType = TaxDocumentType.PurchaseInvoice;
}
PXSelectBase <CASplit> select = new PXSelectJoin <CASplit,
LeftJoin <InventoryItem, On <InventoryItem.inventoryID, Equal <CASplit.inventoryID> > >,
Where <CASplit.adjRefNbr, Equal <Current <CAAdj.adjRefNbr> > >,
OrderBy <Asc <CASplit.adjRefNbr, Asc <CASplit.lineNbr> > > >(Base);
foreach (PXResult <CASplit, InventoryItem> res in select.View.SelectMultiBound(new object[] { invoice }))
{
CASplit tran = (CASplit)res;
InventoryItem item = (InventoryItem)res;
var line = new TaxCartItem
{
Index = tran.LineNbr ?? 0,
Amount = mult * tran.CuryTranAmt.GetValueOrDefault(),
Description = tran.TranDesc,
DestinationAddress = request.DestinationAddress,
OriginAddress = request.OriginAddress,
ItemCode = item.InventoryCD,
Quantity = Math.Abs(tran.Qty.GetValueOrDefault()),
Discounted = request.Discount > 0,
TaxCode = tran.TaxCategoryID,
};
request.CartItems.Add(line);
}
return(request);
}
public AddressService(DALFacade facade)
{
_facade = facade;
_conv = new AddressConverter();
}
protected virtual GetTaxRequest BuildGetTaxRequest(CROpportunity order)
{
if (order == null)
{
throw new PXArgumentException(nameof(order));
}
BAccount cust = (BAccount)PXSelect <BAccount,
Where <BAccount.bAccountID, Equal <Required <BAccount.bAccountID> > > > .
Select(Base, order.BAccountID);
Location loc = (Location)PXSelect <Location,
Where <Location.bAccountID, Equal <Required <Location.bAccountID> >, And <Location.locationID, Equal <Required <Location.locationID> > > > > .
Select(Base, order.BAccountID, order.LocationID);
IAddressBase addressFrom = GetFromAddress();
IAddressBase addressTo = GetToAddress(order);
if (addressFrom == null)
{
throw new PXException(Messages.FailedGetFromAddressCR);
}
if (addressTo == null)
{
throw new PXException(Messages.FailedGetToAddressCR);
}
GetTaxRequest request = new GetTaxRequest();
request.CompanyCode = CompanyCodeFromBranch(order.TaxZoneID, Base.Accessinfo.BranchID);
request.CurrencyCode = order.CuryID;
request.CustomerCode = cust?.AcctCD;
request.OriginAddress = AddressConverter.ConvertTaxAddress(addressFrom);
request.DestinationAddress = AddressConverter.ConvertTaxAddress(addressTo);
request.DocCode = $"CR.{order.OpportunityID}";
request.DocDate = order.CloseDate.GetValueOrDefault();
request.Discount = order.CuryLineDocDiscountTotal.GetValueOrDefault();
int mult = 1;
if (loc != null)
{
request.CustomerUsageType = loc.CAvalaraCustomerUsageType;
}
if (!string.IsNullOrEmpty(loc?.CAvalaraExemptionNumber))
{
request.ExemptionNo = loc.CAvalaraExemptionNumber;
}
request.DocType = TaxDocumentType.SalesOrder;
PXSelectBase <CROpportunityProducts> select = new PXSelectJoin <CROpportunityProducts,
LeftJoin <InventoryItem, On <InventoryItem.inventoryID, Equal <CROpportunityProducts.inventoryID> >,
LeftJoin <Account, On <Account.accountID, Equal <InventoryItem.salesAcctID> > > >,
Where <CROpportunityProducts.quoteID, Equal <Current <CROpportunity.quoteNoteID> > >,
OrderBy <Asc <CROpportunityProducts.lineNbr> > >(Base);
foreach (PXResult <CROpportunityProducts, InventoryItem, Account> res in select.View.SelectMultiBound(new object[] { order }))
{
CROpportunityProducts tran = (CROpportunityProducts)res;
InventoryItem item = (InventoryItem)res;
Account salesAccount = (Account)res;
var line = new TaxCartItem();
line.Index = tran.LineNbr ?? 0;
line.Amount = mult * tran.CuryAmount.GetValueOrDefault();
line.Description = tran.Descr;
line.DestinationAddress = request.DestinationAddress;
line.OriginAddress = request.OriginAddress;
line.ItemCode = item.InventoryCD;
line.Quantity = Math.Abs(tran.Qty.GetValueOrDefault());
line.Discounted = request.Discount > 0;
line.RevAcct = salesAccount.AccountCD;
line.TaxCode = tran.TaxCategoryID;
request.CartItems.Add(line);
}
return(request);
}
public virtual CommitTaxRequest BuildCommitTaxRequestWithFrt(ARInvoice invoice)
{
if (invoice == null)
{
throw new PXArgumentException(nameof(invoice), ErrorMessages.ArgumentNullException);
}
Customer cust = (Customer)Base.customer.View.SelectSingleBound(new object[] { invoice });
CR.Location loc = (CR.Location)Base.location.View.SelectSingleBound(new object[] { invoice });
CommitTaxRequest request = new CommitTaxRequest();
request.CompanyCode = Base1.CompanyCodeFromBranch(invoice.TaxZoneID, invoice.BranchID);
request.CurrencyCode = invoice.CuryID;
request.CustomerCode = cust.AcctCD;
request.TaxRegistrationID = loc?.TaxRegistrationID;
IAddressBase fromAddress = Base1.GetFromAddress(invoice);
IAddressBase toAddress = Base1.GetToAddress(invoice);
if (fromAddress == null)
{
throw new PXException(Messages.FailedGetFrom);
}
if (toAddress == null)
{
throw new PXException(Messages.FailedGetTo);
}
request.OriginAddress = AddressConverter.ConvertTaxAddress(fromAddress);
request.DestinationAddress = AddressConverter.ConvertTaxAddress(toAddress);
request.DocCode = $"AR.{invoice.DocType}.{invoice.RefNbr}";
request.DocDate = invoice.DocDate.GetValueOrDefault();
request.LocationCode = GetExternalTaxProviderLocationCode <ARTran, ARTran.FK.Invoice.SameAsCurrent, ARTran.siteID>(invoice);
request.CustomerUsageType = invoice.AvalaraCustomerUsageType;
if (!string.IsNullOrEmpty(invoice.ExternalTaxExemptionNumber))
{
request.ExemptionNo = invoice.ExternalTaxExemptionNumber;
}
request.DocType = Base1.GetTaxDocumentType(invoice);
Sign sign = Base1.GetDocumentSign(invoice);
PXSelectBase <ARTran> select = new PXSelectJoin <ARTran, LeftJoin <InventoryItem, On <InventoryItem.inventoryID, Equal <ARTran.inventoryID> >,
LeftJoin <Account, On <Account.accountID, Equal <ARTran.accountID> > > >,
Where <ARTran.tranType, Equal <Current <ARInvoice.docType> >,
And <ARTran.refNbr, Equal <Current <ARInvoice.refNbr> >,
And <Where <ARTran.lineType, NotEqual <SOLineType.discount>, Or <ARTran.lineType, IsNull> > > > >,
OrderBy <Asc <ARTran.tranType, Asc <ARTran.refNbr, Asc <ARTran.lineNbr> > > > >(Base);
request.Discount = Base.Document.Current.CuryDiscTot.GetValueOrDefault();
DateTime?taxDate = invoice.OrigDocDate;
bool applyRetainage = Base.ARSetup.Current?.RetainTaxes != true && invoice.IsOriginalRetainageDocument();
/// <summary>
/// Add the following condition and logic per Jira [IP-23]
/// </summary>>
string taxCategory = (Base as SOInvoiceEntry).FreightDetails.Current?.TaxCategoryID;
if (invoice.CuryFreightTot > 0 && GL.Branch.PK.Find(Base, Base.Accessinfo.BranchID).CountryID == Country_US && invoice.TaxZoneID == TaxCloud && !string.IsNullOrEmpty(taxCategory))
{
var line = new TaxCartItem();
line.Index = short.MinValue;
line.Quantity = 1;
line.UOM = "EA";
line.Amount = sign * invoice.CuryFreightTot.GetValueOrDefault();
line.Description = PXMessages.LocalizeNoPrefix(SO.Messages.FreightDesc);
line.DestinationAddress = request.DestinationAddress;
line.OriginAddress = request.OriginAddress;
line.ItemCode = "N/A";
line.Discounted = false;
line.TaxCode = taxCategory;
request.CartItems.Add(line);
}
foreach (PXResult <ARTran, InventoryItem, Account> res in select.View.SelectMultiBound(new object[] { invoice }))
{
ARTran tran = (ARTran)res;
InventoryItem item = (InventoryItem)res;
Account salesAccount = (Account)res;
var line = new TaxCartItem();
line.Index = tran.LineNbr ?? 0;
line.Amount = sign * (tran.CuryTranAmt.GetValueOrDefault() + (applyRetainage ? tran.CuryRetainageAmt.GetValueOrDefault() : 0m));
line.Description = tran.TranDesc;
line.DestinationAddress = AddressConverter.ConvertTaxAddress(Base1.GetToAddress(invoice, tran));
line.OriginAddress = AddressConverter.ConvertTaxAddress(Base1.GetFromAddress(invoice, tran));
line.ItemCode = item.InventoryCD;
line.Quantity = Math.Abs(tran.Qty.GetValueOrDefault());
line.UOM = tran.UOM;
line.Discounted = tran.LineType != SOLineType.Freight && request.Discount > 0;
line.RevAcct = salesAccount.AccountCD;
line.TaxCode = tran.TaxCategoryID;
line.CustomerUsageType = tran.AvalaraCustomerUsageType;
if (tran.OrigInvoiceDate != null)
{
taxDate = tran.OrigInvoiceDate;
}
request.CartItems.Add(line);
}
if (applyRetainage)
{
var line = new TaxCartItem();
line.Index = invoice.LineCntr.GetValueOrDefault() + 1;
line.Amount = Sign.Minus * sign * invoice.CuryLineRetainageTotal.GetValueOrDefault();
line.Description = PXMessages.LocalizeFormatNoPrefix(AP.Messages.RetainageForTransactionDescription, GetLabel.For <ARDocType>(invoice.DocType), invoice.RefNbr);
line.DestinationAddress = request.DestinationAddress;
line.OriginAddress = request.OriginAddress;
line.ItemCode = "Retainage";
line.Discounted = false;
line.NonTaxable = true;
request.CartItems.Add(line);
}
if ((invoice.DocType == ARDocType.CreditMemo || invoice.DocType == ARDocType.CashReturn) && invoice.OrigDocDate != null)
{
request.TaxOverride.Reason = Messages.ReturnReason;
request.TaxOverride.TaxDate = taxDate.Value;
request.TaxOverride.TaxOverrideType = TaxOverrideType.TaxDate;
sign = Sign.Minus;
}
return(request);
}
protected GetTaxRequest BuildGetTaxRequestWithFRTCate <TLineAmt, TLineQty, TDocDiscount>(SOOrder order, string docCode, string debugMethodName)
where TLineAmt : IBqlField
where TLineQty : IBqlField
where TDocDiscount : IBqlField
{
Stopwatch sw = new Stopwatch();
sw.Start();
Debug.Indent();
if (order == null)
{
throw new PXArgumentException(nameof(order));
}
Customer cust = (Customer)Base.customer.View.SelectSingleBound(new object[] { order });
Location loc = (Location)Base.location.View.SelectSingleBound(new object[] { order });
IAddressBase fromAddress = GetFromAddress(order);
IAddressBase toAddress = GetToAddress(order);
Debug.Print($"{DateTime.Now.TimeOfDay} Select Customer, Location, Addresses in {sw.ElapsedMilliseconds} millisec");
if (fromAddress == null)
{
throw new PXException(Messages.FailedGetFromAddressSO);
}
if (toAddress == null)
{
throw new PXException(Messages.FailedGetToAddressSO);
}
GetTaxRequest request = new GetTaxRequest();
request.CompanyCode = Base1.CompanyCodeFromBranch(order.TaxZoneID, order.BranchID);
request.CurrencyCode = order.CuryID;
request.CustomerCode = cust.AcctCD;
request.TaxRegistrationID = loc?.TaxRegistrationID;
request.OriginAddress = AddressConverter.ConvertTaxAddress(fromAddress);
request.DestinationAddress = AddressConverter.ConvertTaxAddress(toAddress);
request.DocCode = docCode;
request.DocDate = order.OrderDate.GetValueOrDefault();
request.LocationCode = GetExternalTaxProviderLocationCode <SOLine, SOLine.FK.Order.SameAsCurrent, SOLine.siteID>(order);
Sign docSign = Sign.Plus;
request.CustomerUsageType = order.AvalaraCustomerUsageType;
if (!string.IsNullOrEmpty(order.ExternalTaxExemptionNumber))
{
request.ExemptionNo = order.ExternalTaxExemptionNumber;
}
SOOrderType orderType = (SOOrderType)Base.soordertype.View.SelectSingleBound(new object[] { order });
if (orderType.DefaultOperation == SOOperation.Receipt)
{
request.DocType = TaxDocumentType.ReturnOrder;
docSign = Sign.Minus;
}
else
{
request.DocType = TaxDocumentType.SalesOrder;
}
PXSelectBase <SOLine> select = new PXSelectJoin <SOLine, LeftJoin <InventoryItem, On <SOLine.FK.InventoryItem>,
LeftJoin <Account, On <Account.accountID, Equal <SOLine.salesAcctID> > > >,
Where <SOLine.orderType, Equal <Current <SOOrder.orderType> >,
And <SOLine.orderNbr, Equal <Current <SOOrder.orderNbr> > > >,
OrderBy <Asc <SOLine.orderType,
Asc <SOLine.orderNbr,
Asc <SOLine.lineNbr> > > > >(Base);
PXCache documentCache = Base.Caches[typeof(SOOrder)];
request.Discount = (documentCache.GetValue <TDocDiscount>(order) as decimal?) ?? 0m;
Stopwatch sw2 = new Stopwatch();
sw2.Start();
// Add additional condition to skip the calculation including the freight amount. Per YJ's request.
if (order.CuryFreightTot > 0 && !string.IsNullOrEmpty(order.FreightTaxCategoryID))
{
var line = new TaxCartItem();
line.Index = short.MinValue;
line.Quantity = 1;
line.UOM = "EA";
line.Amount = docSign * order.CuryFreightTot.GetValueOrDefault();
line.Description = PXMessages.LocalizeNoPrefix(Messages.FreightDesc);
line.DestinationAddress = request.DestinationAddress;
line.OriginAddress = request.OriginAddress;
line.ItemCode = "N/A";
line.Discounted = false;
line.TaxCode = order.FreightTaxCategoryID;
request.CartItems.Add(line);
}
PXCache lineCache = Base.Caches[typeof(SOLine)];
foreach (PXResult <SOLine, InventoryItem, Account> res in select.View.SelectMultiBound(new object[] { order }))
{
SOLine tran = (SOLine)res;
InventoryItem item = (InventoryItem)res;
Account salesAccount = (Account)res;
bool lineIsDiscounted = request.Discount > 0m &&
((tran.DocumentDiscountRate ?? 1m) != 1m || (tran.GroupDiscountRate ?? 1m) != 1m);
var line = new TaxCartItem();
line.Index = tran.LineNbr ?? 0;
decimal lineAmount = (lineCache.GetValue <TLineAmt>(tran) as decimal?) ?? 0m;
decimal lineQty = (lineCache.GetValue <TLineQty>(tran) as decimal?) ?? 0m;
line.Amount = orderType.DefaultOperation != tran.Operation
? Sign.Minus * docSign * lineAmount
: docSign * lineAmount;
line.Description = tran.TranDesc;
line.DestinationAddress = AddressConverter.ConvertTaxAddress(Base1.GetToAddress(order, tran));
line.OriginAddress = AddressConverter.ConvertTaxAddress(Base1.GetFromAddress(order, tran));
line.ItemCode = item.InventoryCD;
line.Quantity = Math.Abs(lineQty);
line.UOM = tran.UOM;
line.Discounted = lineIsDiscounted;
line.RevAcct = salesAccount.AccountCD;
line.TaxCode = tran.TaxCategoryID;
line.CustomerUsageType = tran.AvalaraCustomerUsageType;
if (tran.Operation == SOOperation.Receipt && tran.InvoiceDate != null)
{
line.TaxOverride.Reason = Messages.ReturnReason;
line.TaxOverride.TaxDate = tran.InvoiceDate.Value;
line.TaxOverride.TaxOverrideType = TaxOverrideType.TaxDate;
}
request.CartItems.Add(line);
}
sw2.Stop();
Debug.Print($"{DateTime.Now.TimeOfDay} Select detail lines in {sw2.ElapsedMilliseconds} millisec.");
Debug.Unindent();
sw.Stop();
Debug.Print($"{DateTime.Now.TimeOfDay} {debugMethodName}() in {sw.ElapsedMilliseconds} millisec.");
return(request);
}
public bool checkInput(List <Variable> variables, DataSet selectedDataSet, bool rdbMean, bool rdbMedian)
{
if (variables.Count == 0)
{
return(false);
}
_Worksheet worksheet = WorksheetHelper.NewWorksheet("Runs test");
int column = 1;
int row = 2;
worksheet.Cells[row, column] = "Runs test for randomness";
worksheet.Cells[row++, column] = "Observations";
if (rdbMean)
{
worksheet.Cells[row++, column] = "Mean";
}
if (rdbMedian)
{
worksheet.Cells[row++, column] = "Median";
}
worksheet.Cells[row++, column] = "Below cutoff";
worksheet.Cells[row++, column] = "Above cutoff";
worksheet.Cells[row++, column] = "Number of runs";
worksheet.Cells[row++, column] = "E(R)";
worksheet.Cells[row++, column] = "Stddev(R)";
worksheet.Cells[row++, column] = "Z-Value";
worksheet.Cells[row++, column] = "P-Value (two-tailed)";
((Range)worksheet.Cells[row, column]).EntireColumn.AutoFit();
row = 1;
column = 2;
foreach (Variable variable in variables)
{
worksheet.Cells[row++, column] = variable.name;
var range = variable.getRange().Address(true, true, true);
worksheet.Cells[row++, column] = selectedDataSet.rangeSize();
var ntotal = selectedDataSet.rangeSize();
if (rdbMean)
{
worksheet.Cells[row++, column] = "=AVERAGE(" + range + ")";
}
if (rdbMedian)
{
worksheet.Cells[row++, column] = "=MEDIAN(" + range + ")";
}
var cutoffValue = (double)worksheet.Cells[row - 1, column].Value;
int amountOfRuns = calculateRuns(worksheet, selectedDataSet, variable.getRange(), cutoffValue);
worksheet.WriteFunction(row++, column, "COUNTIF(" + range + ",\"<\"&" + AddressConverter.CellAddress(row - 2, column) + ")");
worksheet.WriteFunction(row++, column, "COUNTIF(" + range + ",\">\"&" + AddressConverter.CellAddress(row - 3, column) + ")");
worksheet.Cells[row++, column] = amountOfRuns;
worksheet.WriteFunction(row++, column, "1 + (2*" + AddressConverter.CellAddress(row - 4, column) + "*" + AddressConverter.CellAddress(row - 3, column) + ")/(" + AddressConverter.CellAddress(row - 4, column) + "+" + AddressConverter.CellAddress(row - 3, column) + ")");
worksheet.WriteFunction(row++, column, "SQRT(2*" + AddressConverter.CellAddress(row - 5, column) + "*" + AddressConverter.CellAddress(row - 4, column) + "*(2*" + AddressConverter.CellAddress(row - 5, column) + "*" + AddressConverter.CellAddress(row - 4, column) + "-" + AddressConverter.CellAddress(row - 5, column) + "-" + AddressConverter.CellAddress(row - 4, column) + ")/((" + AddressConverter.CellAddress(row - 5, column) + "+" + AddressConverter.CellAddress(row - 4, column) + ")*(" + AddressConverter.CellAddress(row - 5, column) + "+" + AddressConverter.CellAddress(row - 4, column) + ")*(" + AddressConverter.CellAddress(row - 5, column) + "+" + AddressConverter.CellAddress(row - 4, column) + "-1)))");
worksheet.WriteFunction(row++, column, "(" + AddressConverter.CellAddress(row - 4, column) + "-" + AddressConverter.CellAddress(row - 3, column) + ")/" + AddressConverter.CellAddress(row - 2, column));
worksheet.WriteFunction(row++, column, "2*(1-NORMSDIST(ABS(" + AddressConverter.CellAddress(row - 2, column) + ")))");
((Range)worksheet.Cells[row, column]).EntireColumn.AutoFit();
row = 1;
column++;
}
return(true);
}
public AddressService(DataAccess dataAccess)
{
this._dataAccess = dataAccess;
_converterAddress = new AddressConverter();
}
public async Task <List <AddressViewModel> > GetAllAddressAsync(CancellationToken ct = default(CancellationToken))
{
return(AddressConverter.ConvertList(await _addressRepository.GetAllAsync(ct)));
}
