public EntityReference ProductFromSpec(ExcelProxyProduct product)
{
using (var orgContext = new OrganizationServiceContext(service))
{
var finder = (from c in orgContext.CreateQuery <Product>()
where c.ProductNumber == product.SKU
select c).FirstOrDefault();
if (finder != null)
{
var entityProduct = new Product()
{
Id = finder.Id,
ProductNumber = product.SKU,
Name = product.Product,
Price = new Money(Convert.ToDecimal(product.recomendetPriceUSD)),
DefaultUoMScheduleId = (from i in orgContext.CreateQuery <UoMSchedule>()
where i.Name == "Единица измерения по умолчанию"
// where i.Id == new Guid("AFB0C13B-11DA-49D0-8767-A71F1AA593BF")//Единица измерения по умолчанию name
select new EntityReference
{
Id = i.Id,
LogicalName = i.LogicalName,
Name = i.Name
}).FirstOrDefault(),
DefaultUoMId = (from i in orgContext.CreateQuery <UoM>()
where i.Name == "Базовая единица"
//where i.Id == new Guid("28FD5C9C-22F7-419C-BBBC-720523DD3666") //Базовая единица
select new EntityReference
{
Id = i.Id,
LogicalName = i.LogicalName,
Name = i.Name
}).FirstOrDefault(),
new_manufacturer = findVendor(product.Vendor)
};
try
{
service.Update(entityProduct);
return(new EntityReference
{
Id = entityProduct.Id,
Name = entityProduct.Name,
LogicalName = entityProduct.LogicalName
});
}
catch
{
return(null);
}
}
else
{
return(CreateProduct(product));
}
}
}
public EntityReference ProductFromSpec(ExcelProxyProduct product, EntityReference priceName)
{
using (var orgContext = new OrganizationServiceContext(service))
{
var finder = (from c in orgContext.CreateQuery <Product>()
where c.ProductNumber == product.SKU
select c).FirstOrDefault();
if (finder != null)
{
var entityProduct = new Product()
{
Id = finder.Id,
ProductNumber = product.SKU,
Name = product.Product,
DefaultUoMScheduleId = (from i in orgContext.CreateQuery <UoMSchedule>()
where i.Name == "Единица измерения по умолчанию"
select new EntityReference
{
Id = i.Id,
LogicalName = i.LogicalName,
Name = i.Name
}).FirstOrDefault(),
DefaultUoMId = (from i in orgContext.CreateQuery <UoM>()
where i.Name == "Базовая единица"
select new EntityReference
{
Id = i.Id,
LogicalName = i.LogicalName,
Name = i.Name
}).FirstOrDefault(),
new_manufacturingname = findVendor(product.Vendor)
};
try
{
service.Update(entityProduct);
return(new EntityReference
{
Id = entityProduct.Id,
Name = entityProduct.Name,
LogicalName = entityProduct.LogicalName
});
}
catch
{
return(null);
}
}
else
{
return(CreateProduct(product, priceName));
}
}
}
public void Processes(Annotation annotation, IOrganizationService service)
{
var exel = new ExcelReader().ExcelOpenSpreadsheets(annotation.DocumentBody.ToString(),
annotation.FileName.ToString());
if (exel == null)
{
return;
}
if (exel.Tables[0].Rows[1][0].ToString() != "SKU")
{
return;
}
var data = new CreateNewProduct();
List <ExcelProxyInvoice> invoice = new List <ExcelProxyInvoice>();
data.service = service;
data.mainEntityId = annotation.ObjectId;
for (int i = 2; i < exel.Tables[0].Rows.Count; i++)
{
var product = new ExcelProxyProduct();
var invoicedetail = new ExcelProxyInvoiceDetail();
if (exel.Tables[0].Rows[i][0].ToString() != string.Empty && exel.Tables[0].Rows[i][0].ToString() != "Итого:")
{
product.SKU = exel.Tables[0].Rows[i][0].ToString();
product.Vendor = exel.Tables[0].Rows[i][1].ToString();
product.recomendetPriceUSD = exel.Tables[0].Rows[i][10].ToString();
product.Product = exel.Tables[0].Rows[i][2].ToString();
invoicedetail.Product = exel.Tables[0].Rows[i][2].ToString();
invoicedetail.Count = exel.Tables[0].Rows[i][3].ToString();
invoicedetail.Priceperunit = exel.Tables[0].Rows[i][4].ToString();
invoicedetail.Baseamount = exel.Tables[0].Rows[i][5].ToString();
invoicedetail.Purchaseprice = exel.Tables[0].Rows[i][6].ToString();
invoicedetail.Amountpurchase = exel.Tables[0].Rows[i][7].ToString();
invoicedetail.Pricepurchaseusd = exel.Tables[0].Rows[i][11].ToString();
invoicedetail.Exchangerates = exel.Tables[0].Rows[i][13].ToString();
invoicedetail.totalUSD = exel.Tables[0].Rows[i][12].ToString();
invoice.Add(new ExcelProxyInvoice
{
Marza = exel.Tables[0].Rows[i][8].ToString(),
MarzaPersent = exel.Tables[0].Rows[i][9].ToString(),
totalUSD = exel.Tables[0].Rows[i][12].ToString(),
exchangeRates = exel.Tables[0].Rows[i][13].ToString()
});
EntityReference idProduct = data.ProductFromSpec(product);
if (idProduct == null)
{
throw new InvalidPluginExecutionException("Product not found");
}
if (data.CreateInvoiceDetail(invoicedetail, idProduct) == false)
{
throw new InvalidPluginExecutionException("InvoiceDetail don`t create");
}
}
else
{
break;
}
}
decimal sum = (from i in invoice
select Convert.ToDecimal(i.Marza)).Sum();
decimal percent = (from i in invoice
select Convert.ToDecimal(i.MarzaPersent)).Average();
decimal totalUSD = (from i in invoice
select Convert.ToDecimal(i.totalUSD)).Sum();
double rates = (from i in invoice
select Convert.ToDouble(i.exchangeRates)).Average();
using (var orgContext = new OrganizationServiceContext(service))
{
var invoiceMain = (from y in orgContext.CreateQuery <Invoice>()
where y.Id == annotation.ObjectId.Id
select y).FirstOrDefault();
var updateInvoice = new Invoice()
{
Id = annotation.ObjectId.Id,
//new_marginUSD = invoiceMain.new_marginUSD + (double)sum,
//new_morginpercentage = percent == 0 ? 0 : (invoiceMain.new_morginpercentage ?? 0 + percent),
new_USD = invoiceMain.new_USD + (double)totalUSD,
//new_exchangerate = invoiceMain.new_exchangerate ?? 0 + rates
};
service.Update(updateInvoice);
}
}
public void Processes(Annotation annotation, IOrganizationService service)
{
using (var orgContext = new OrganizationServiceContext(service))
{
var quoteMain = (from y in orgContext.CreateQuery <Quote>()
where y.Id == annotation.ObjectId.Id
select y).FirstOrDefault();
var exel = new ExcelReader().ExcelOpenSpreadsheets(annotation.DocumentBody.ToString(),
annotation.FileName.ToString());
if (exel == null)
{
return;
}
var data = new CreateNewProduct();
List <ExcelProxyQuote> quote = new List <ExcelProxyQuote>();
data.service = service;
data.mainEntityId = annotation.ObjectId;
int line = 0;
for (var i = 0; i < exel.Tables[0].Rows.Count; i++)
{
if (exel.Tables[0].Rows[i][0].ToString() == "SKU")
{
line = i;
break;
}
}
for (int i = line + 1; i < exel.Tables[0].Rows.Count; i++)
{
var product = new ExcelProxyProduct();
var quotedetail = new ExcelProxyQuotedetail();
if (exel.Tables[0].Rows[i][1].ToString() != string.Empty && exel.Tables[0].Rows[i][0].ToString() != "Итого:")
{
product.SKU = exel.Tables[0].Rows[i][0].ToString();
product.Vendor = exel.Tables[0].Rows[i][1].ToString();
product.Product = exel.Tables[0].Rows[i][2].ToString();
quotedetail.Product = exel.Tables[0].Rows[i][2].ToString();
quotedetail.Vendor = exel.Tables[0].Rows[i][1].ToString();
quotedetail.SKU = exel.Tables[0].Rows[i][0].ToString();
quotedetail.quantity = exel.Tables[0].Rows[i][3].ToString();
quotedetail.priceperunit = exel.Tables[0].Rows[i][4].ToString();
quotedetail.baseamount = exel.Tables[0].Rows[i][5].ToString();
quotedetail.new_usdprice = exel.Tables[0].Rows[i][6].ToString();
quotedetail.new_totalpurchaseusd = exel.Tables[0].Rows[i][7].ToString();
quotedetail.new_koef = exel.Tables[0].Rows[i][8].ToString();
quotedetail.new_sellingusd = exel.Tables[0].Rows[i][9].ToString();
quotedetail.new_generalsellingusd = exel.Tables[0].Rows[i][10].ToString();
quotedetail.exchangeRates = exel.Tables[0].Rows[i][11].ToString();
quote.Add(new ExcelProxyQuote
{
new_usd = exel.Tables[0].Rows[i][7].ToString(),
new_summausd = exel.Tables[0].Rows[i][10].ToString()
});
EntityReference idProduct = data.ProductFromSpec(product, quoteMain.PriceLevelId);
if (idProduct == null)
{
throw new InvalidPluginExecutionException("Product not found");
}
if (data.CreateQuoteDetail(quotedetail, idProduct) == false)
{
throw new InvalidPluginExecutionException("QuoteDetail don`t create");
}
}
else
{
break;
}
}
double usd = (from i in quote
select Convert.ToDouble(i.new_usd)).Sum();
double summusd = (from i in quote
select Convert.ToDouble(i.new_summausd)).Sum();
var updateQuote = new Quote()
{
Id = annotation.ObjectId.Id,
new_usd = usd,
new_summausd = summusd
};
service.Update(updateQuote);
}
}
public EntityReference CreateProduct(ExcelProxyProduct product)
{
using (var orgContext = new OrganizationServiceContext(service))
{
Product entityProduct = new Product()
{
Id = Guid.NewGuid(),
ProductNumber = product.SKU,
Name = product.Product,
Price = new Money(Convert.ToDecimal(product.recomendetPriceUSD)),
DefaultUoMScheduleId = (from i in orgContext.CreateQuery <UoMSchedule>()
where i.Name == "Единица измерения по умолчанию"
//where i.Id == new Guid("AFB0C13B-11DA-49D0-8767-A71F1AA593BF")
select new EntityReference
{
Id = i.Id,
LogicalName = i.LogicalName,
Name = i.Name
}).FirstOrDefault(),
DefaultUoMId = (from i in orgContext.CreateQuery <UoM>()
where i.Name == "Базовая единица"
//where i.Id == new Guid("28FD5C9C-22F7-419C-BBBC-720523DD3666")
select new EntityReference
{
Id = i.Id,
LogicalName = i.LogicalName,
Name = i.Name
}).FirstOrDefault(),
new_manufacturer = findVendor(product.Vendor)
};
try
{
///////////////////////////////////////
Guid idNewProduct = service.Create(entityProduct);
var productPriceLevel = new ProductPriceLevel()
{
PriceLevelId = (from i in orgContext.CreateQuery <PriceLevel>()
where i.Name == "Default UAH Pricelist" //Default USD Pricelist
select new EntityReference
{
LogicalName = PriceLevel.EntityLogicalName,
Id = i.Id,
Name = i.Name
}).FirstOrDefault(),
UoMId = (from i in orgContext.CreateQuery <UoM>()
where i.Name == "Базовая единица"
select new EntityReference
{
Id = i.Id,
LogicalName = i.LogicalName,
Name = i.Name
}).FirstOrDefault(),
Amount = new Money(Convert.ToDecimal(product.recomendetPriceUSD)),
ProductId = new EntityReference
{
Id = idNewProduct,
LogicalName = Product.EntityLogicalName,
Name = product.Product
}
};
var idProductPriceLevel = service.Create(productPriceLevel);
var updateNewProduct = new Product()
{
Id = idNewProduct,
PriceLevelId = (from i in orgContext.CreateQuery <PriceLevel>()
where i.Name == "Default USD Pricelist"
select new EntityReference
{
LogicalName = PriceLevel.EntityLogicalName,
Id = i.Id,
Name = i.Name
}).FirstOrDefault()
};
service.Update(updateNewProduct);
return(new EntityReference
{
Id = idNewProduct,
Name = entityProduct.Name,
LogicalName = entityProduct.LogicalName
});
///////////////////////////////////////
}
catch
{
return(null);
}
}
}
internal void Processes(Annotation annotation, IOrganizationService service)
{
var exel = new ExcelReader().ExcelOpenSpreadsheets(annotation.DocumentBody.ToString(),
annotation.FileName.ToString());
if (exel == null)
{
return;
}
if (exel.Tables[0].Rows[1][0].ToString() != "SKU")
{
return;
}
var data = new CreateNewProduct();
List <ExcelProxyQuote> quote = new List <ExcelProxyQuote>();
data.service = service;
data.mainEntityId = annotation.ObjectId;
for (int i = 2; i < exel.Tables[0].Rows.Count; i++)
{
var product = new ExcelProxyProduct();
var quotedetail = new ExcelProxyQuotedetail();
if (exel.Tables[0].Rows[i][0].ToString() != string.Empty && exel.Tables[0].Rows[i][0].ToString() != "Итого:")
{
product.SKU = exel.Tables[0].Rows[i][0].ToString();
product.Vendor = exel.Tables[0].Rows[i][1].ToString();
product.recomendetPriceUSD = exel.Tables[0].Rows[i][10].ToString();
product.Product = exel.Tables[0].Rows[i][2].ToString();
quotedetail.Product = exel.Tables[0].Rows[i][2].ToString();
quotedetail.Count = exel.Tables[0].Rows[i][3].ToString();
quotedetail.priceForOneHRN = exel.Tables[0].Rows[i][4].ToString();
quotedetail.priceAllHRN = exel.Tables[0].Rows[i][5].ToString();
quotedetail.buyPriceHRN = exel.Tables[0].Rows[i][6].ToString();
quotedetail.buyPriceAllHRN = exel.Tables[0].Rows[i][7].ToString();
quotedetail.buyPriceAllUSD = exel.Tables[0].Rows[i][11].ToString();
quotedetail.exchangeRates = exel.Tables[0].Rows[i][13].ToString();
quotedetail.totalUSD = exel.Tables[0].Rows[i][12].ToString();
quote.Add(new ExcelProxyQuote
{
Marza = exel.Tables[0].Rows[i][8].ToString(),
MarzaPersent = exel.Tables[0].Rows[i][9].ToString(),
//discount = exel.Tables[0].Rows[i][13].ToString(),
//discountForPartners = exel.Tables[0].Rows[i][14].ToString(),
exchangeRates = exel.Tables[0].Rows[i][13].ToString()
});
EntityReference idProduct = data.ProductFromSpec(product);
if (idProduct == null)
{
throw new InvalidPluginExecutionException("Product not found");
}
if (data.CreateQuoteDetail(quotedetail, idProduct) == false)
{
throw new InvalidPluginExecutionException("QuoteDetail don`t create");
}
}
else
{
break;
}
}
decimal sum = (from i in quote
select Convert.ToDecimal(i.Marza)).Sum();
decimal percent = (from i in quote
select Convert.ToDecimal(i.MarzaPersent)).Average();
decimal discount = (from i in quote
select Convert.ToDecimal(i.discount)).Average();
double rates = (from i in quote
select Convert.ToDouble(i.exchangeRates)).Average();
using (var orgContext = new OrganizationServiceContext(service))
{
var quoteMain = (from y in orgContext.CreateQuery <Quote>()
where y.Id == annotation.ObjectId.Id
select y).FirstOrDefault();
var updateQuote = new Quote()
{
Id = annotation.ObjectId.Id,
new_marginUSD = new Money((quoteMain.new_marginUSD == null ? decimal.Zero : quoteMain.new_marginUSD.Value) + sum),
new_morginpercentage = percent == 0 ? 0 : (quoteMain.new_morginpercentage ?? 0 + percent) * 100,
new_exchangerate = quoteMain.new_exchangerate ?? 0 + rates/*,
* DiscountPercentage = quoteMain.DiscountPercentage ?? 0 + discount*/
};
service.Update(updateQuote);
}
}