public MpImportMsSQL()
{
connectionString = ImportHelper.MaestroPanelMsSqlConnectionString();
}
public bool ImportItem(ApplicationUser username, ExcelPackage package, List <Inspection> inspectionList, int j)
{
ExcelHandlingHelper excelHandling = new ExcelHandlingHelper();
ExcelWorksheet workSheet = package.Workbook.Worksheets[j];
//read every column in each worksheet
if (workSheet.Dimension != null)
{
int totalRows = workSheet.Dimension.Rows;
var passionBrands = _passionBrandRepository.PassionBrands;
var factories = _factoryRepository.Factories;
var finalWeeks = _finalWeekRepository.FinalWeeks;
var techManagers = _techManagerRepository.TechManagers;
for (int i = 2; i <= totalRows; i++)
{
if (workSheet.Cells[i, 1].Value == null)
{
continue;
}
var inspection = new Inspection();
inspection.FactoryName = excelHandling.AssignCell(i, workSheet, "Factory");
inspection.OrderNumber = excelHandling.AssignCell(i, workSheet, "Order number");
inspection.IMAN = excelHandling.AssignCell(i, workSheet, "Iman");
inspection.Model = excelHandling.AssignCell(i, workSheet, "Model");
inspection.PassionBrandName = excelHandling.AssignCell(i, workSheet, "Passion brand");
inspection.Description = excelHandling.AssignCell(i, workSheet, "Description");
inspection.OrderQuantity = excelHandling.AssignCell(i, workSheet, "Ord Q'ty") != null?int.Parse(excelHandling.AssignCell(i, workSheet, "Ord Q'ty").Trim()) : 0;
inspection.OrderType = excelHandling.AssignCell(i, workSheet, "Implantation") == null ? 0 : (excelHandling.AssignCell(i, workSheet, "Implantation").Trim().ToUpper() == "YES" ? 1 : 0);
inspection.TechManagerName = excelHandling.AssignCell(i, workSheet, "KĨ THUẬT TRƯỞNG");
//Add finalweek into db, finalweek is based on Final column in excel file. It must be unique. If it is existing, get existing id.
double finalDate;
int weekOfYear = 0;
var importHelper = new ImportHelper();
if (double.TryParse(excelHandling.AssignCell(i, workSheet, "Final"), out finalDate))
{
inspection.FinalDate = importHelper.FromOADate(finalDate);
weekOfYear = importHelper.GetIso8601WeekOfYear(inspection.FinalDate);
}
if (weekOfYear != 0)
{
//the format standard is Year + -W + WeekNumberOfYear, for example: 2018-W14
var finalWeekName = inspection.FinalDate.Year.ToString() + "-W" + weekOfYear;
var existingFinalWeek = finalWeeks.FirstOrDefault(x => x.Name == finalWeekName);
//if finalweek is existing, get the existing FinalWeekID and assign it to inspection
if (existingFinalWeek != null)
{
inspection.FinalWeekId = existingFinalWeek.FinalWeekId;
}
//if finalweek is NOT existing, create new one and assign this to inspection
else
{
var newFinalWeek = new FinalWeek()
{
Week = weekOfYear,
Name = finalWeekName,
Year = Int32.Parse(inspection.FinalDate.Year.ToString()),
Description = inspection.FinalDate.Year.ToString() + "-W" + weekOfYear,
FinalWeekDay = inspection.FinalDate,
DateRegister = DateTime.Now
};
int finalWeekId = _finalWeekRepository.CreateFinalWeek(newFinalWeek);
inspection.FinalWeekId = finalWeekId;
}
}
//Add passionBranch into db, passionBranch is based on Passion Branch column in excel file. It must be unique. If it is existing, get existing id.
var brand = passionBrands.FirstOrDefault(x => x.Name.Contains(inspection.PassionBrandName));
//var brand = passionBrands.FirstOrDefault(x => x.Name.IndexOf(inspection.PassionBrandName, StringComparison.OrdinalIgnoreCase) >= 0);
if (brand != null)
{
inspection.PassionBrandId = brand.PassionBrandId;
}
else
{
var passionBrand = new PassionBrand()
{
Name = inspection.PassionBrandName
};
inspection.PassionBrandId = _passionBrandRepository.CreatePassionBrand(passionBrand);
}
//Add factory into db, factory is based on Factory column in excel file. It must be unique. If it is existing, get existing id.
var factory = factories.FirstOrDefault(x => x.Name.Contains(inspection.FactoryName));
if (factory != null)
{
inspection.FactoryId = factory.FactoryId;
}
else
{
var newFactory = new Factory()
{
Name = inspection.FactoryName
};
inspection.FactoryId = _factoryRepository.CreateFactory(newFactory);
}
//Add techmanager into db, techmanager is based on Kĩ Thuật Trưởng column in excel file. It must be unique. If it is existing, get existing id.
//var techManager = techManagers.FirstOrDefault(x => x.Name.Contains(inspection.TechManagerName));
//if (techManager != null)
//{
// inspection.TechManagerId = techManager.TechManagerId;
//}
//else
//{
// var newTechManager = new TechManager()
// {
// Name = inspection.TechManagerName
// };
// inspection.TechManagerId = _techManagerRepository.CreateTechManager(newTechManager);
//}
inspection.TenantId = username.TenantId;
inspectionList.Add(inspection);
}
return(true);
}
else
{
return(false);
}
}
public void ImportItems_WhenCalled_ImportItems()
{
var importItem1 = new ImportExportItem
{
StorageCardName = "storageCard",
AccountName = "account",
CategoryName = "category",
StoreName = "store",
ItemDateTime = DateTime.Now,
ItemName = "item1",
ItemMovement = 1,
ItemQty = 2,
ItemPrice = 3,
ItemInvoice = "invoice1",
ItemCustomerName = null,
};
var importItem2 = new ImportExportItem
{
StorageCardName = "storageCard",
AccountName = "account",
CategoryName = "category",
StoreName = "store",
ItemDateTime = DateTime.Now,
ItemName = "item2",
ItemMovement = -1,
ItemQty = 5,
ItemPrice = 10,
ItemInvoice = "invoice2",
ItemCustomerName = "customer",
};
var importItems = new List <ImportExportItem>
{
importItem1,
importItem2,
};
var importHelper = new ImportHelper(this.databaseHelper);
importHelper.ImportItems(importItems);
this.databaseContext.SaveChanges();
var storageCard = this.databaseContext.StorageCards.Single();
Assert.That(storageCard.Name, Is.EqualTo("storageCard"));
Assert.That(storageCard.Account.Name, Is.EqualTo("account"));
Assert.That(storageCard.Category.Name, Is.EqualTo("category"));
Assert.That(storageCard.Store.Name, Is.EqualTo("store"));
Assert.That(storageCard.Items.Count, Is.EqualTo(2));
Assert.That(storageCard.Items[0].DateTime, Is.EqualTo(importItem1.ItemDateTime));
Assert.That(storageCard.Items[0].Name, Is.EqualTo(importItem1.ItemName));
Assert.That(storageCard.Items[0].Movement, Is.EqualTo(importItem1.ItemMovement));
Assert.That(storageCard.Items[0].Qty, Is.EqualTo(importItem1.ItemQty));
Assert.That(storageCard.Items[0].Price, Is.EqualTo(importItem1.ItemPrice));
Assert.That(storageCard.Items[0].Invoice, Is.EqualTo(importItem1.ItemInvoice));
Assert.That(storageCard.Items[0].Customer?.Name, Is.EqualTo(importItem1.ItemCustomerName));
Assert.That(storageCard.Items[1].DateTime, Is.EqualTo(importItem2.ItemDateTime));
Assert.That(storageCard.Items[1].Name, Is.EqualTo(importItem2.ItemName));
Assert.That(storageCard.Items[1].Movement, Is.EqualTo(importItem2.ItemMovement));
Assert.That(storageCard.Items[1].Qty, Is.EqualTo(importItem2.ItemQty));
Assert.That(storageCard.Items[1].Price, Is.EqualTo(importItem2.ItemPrice));
Assert.That(storageCard.Items[1].Invoice, Is.EqualTo(importItem2.ItemInvoice));
Assert.That(storageCard.Items[1].Customer?.Name, Is.EqualTo(importItem2.ItemCustomerName));
}
protected void Page_Load(object sender, EventArgs e)
{
user = (Teacher)Session["user"];
string op = Context.Request["op"];
//第一次加载页面时
if (!Page.IsPostBack)
{
Search();
getdata(Search());
}
//添加学院
if (op == "add")
{
saveCollege();
Search();
getdata(Search());
}
//编辑学院信息
else if (op == "edit")
{
editCollege();
Search();
getdata(Search());
}
//批量上传
//else if (op == "upload")
//{
// upload();
// Search();
// getdata(Search());
//}
else if (op == "import")
{
string path = Security.Decrypt(Request["fileName"]);
DataTable dt = TableHelper.GetDistinctSelf(ExcelHelp.excelToDt(path, "excel"), "学院名称");
int i = ImportHelper.College(dt);
int row = ExcelHelp.excelToDt(path, "excel").Rows.Count;
int repeat = row - i;
if (i > 0)
{
LogHelper.Info(this.GetType(), user.TeaAccount + "学院信息导入 -" + i + " " + "条信息");
Response.Write("导入成功，总数据有" + row + "条，共导入" + i + "条数据，重复数据有" + repeat + "条");
Response.End();
}
else
{
Response.Write("导入失败，总数据有" + row + "条，共导入" + i + "条数据，重复数据有" + repeat + "条");
Response.End();
}
}
//删除学院
else if (op == "dele")
{
deleteCollege();
Search();
getdata(Search());
}
//批量删除
else if (op == "batchDel")
{
batchDeleteCollege();
Search();
getdata(Search());
}
}
public static string Import(HttpContext context)
{
dbDataContext db = new dbDataContext();
//HttpPostedFile
HttpPostedFile file = HttpContext.Current.Request.Files["file"];//接收客户端传递过来的数据.
HttpFileCollection files = context.Request.Files;
var result = "";
var filename = file.FileName;
if (file == null)
{
result = JsonConvert.SerializeObject(new { msg = "请选择上传的excel", state = 2 });
}
StringBuilder errorMsg = new StringBuilder(); // 错误信息
try
{
#region 1.获取Excel文件并转换为一个List集合
// 1.1存放Excel文件到本地服务器
// HttpPostedFile filePost = context.Request.Files["filed"]; // 获取上传的文件
string filePath = ImportHelper.SaveExcelFile(file); // 保存文件并获取文件路径
// 单元格抬头
// key：实体对象属性名称，可通过反射获取值
// value：属性对应的中文注解
Dictionary <string, string> cellheader = new Dictionary <string, string> {
{ "Name", "姓名" },
{ "Mobile", "手机号" },
{ "RoleID", "角色" },
{ "Areas", "所属区县" },
{ "Grids", "所属网格" },
{ "Post", "岗位" },
// { "Grids", "备注" },
};
// 1.2解析文件，存放到一个List集合里
List <Users> enlist = ImportHelper.ExcelToEntityList <Users>(cellheader, filePath, out errorMsg);
if (enlist.Count == 0)
{
result = JsonConvert.SerializeObject(new { msg = "导入失败", state = 0 });
}
#region 2.2检测Excel中是否有重复对象
var list = db.Users.Where(x => 1 == 1).ToList();
for (int i = 0; i < enlist.Count; i++)
{
Users enA = enlist[i];
for (int j = 0; j < list.Count; j++)
{
Users enB = list[j];
if (enA.Name == enB.Name)
{
//enA.IsExcelVaildateOK = false;
//enB.IsExcelVaildateOK = false;
errorMsg.AppendLine("EXCEL表中第" + (i + 1) + "行与第" + (j + 1) + "行的数据重复了");
//return Json(new { state = 2, msg = errorMsg.ToString() });
result = JsonConvert.SerializeObject(new { msg = errorMsg.ToString(), state = errorMsg.ToString() });
}
}
}
#endregion
// TODO：其他检测
#endregion
// 3.TODO：对List集合进行持久化存储操作。如：存储到数据库
db.Users.InsertAllOnSubmit(enlist);
db.SubmitChanges();
// 4.返回操作结果
bool isSuccess = false;
if (errorMsg.Length == 0)
{
isSuccess = true; // 若错误信息成都为空，表示无错误信息
//delImg(filename);
result = JsonConvert.SerializeObject(new { msg = "导入成功", state = 1 });
}
result = JsonConvert.SerializeObject(new { state = 0, msg = errorMsg.ToString() });
}
catch (Exception ex)
{
result = JsonConvert.SerializeObject(new { state = 0, msg = "导入失败" + ex });
}
return(result);
}
/// <summary>
/// start
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private async void button1_Click(object sender, EventArgs e)
{
richTextBox2.Text = "";
if (Variables.Punkte.Count == 0)
{
var response = ImportHelper.CsvContentToPraxen(TierärtzteDatei);
if (response == null)
{
button1.Enabled = true;
return;
}
richTextBox2.Text += response;
try
{
var count = 0;
var praxisdic = SaveHelper.RetrieveScore(ScoreJsonDatei);
foreach (var key in praxisdic.Keys)
{
var praxis = Variables.Praxen.FirstOrDefault(p => p.Id == key);
if (praxis != null)
{
count++;
praxis.LastYearScore = praxisdic[key];
}
}
richTextBox2.Text += count + " Praxen konnten ein Score zugeordnet werden\n";
}
catch (Exception ex)
{
var message =
"score.json Datei fehlerhaft, das Einlesen musste abgebrochen werden. \nGenauer Fehler: " + ex;
MessageBox.Show(message);
richTextBox2.Text += message;
return;
}
try
{
Variables.History = SaveHelper.RetrieveHistory(HistoryJsonDatei);
richTextBox2.Text += "history.json hat " + Variables.History.Count + " Einträge\n";
}
catch (Exception ex)
{
var message =
"history.json Datei fehlerhaft, das Einlesen musste abgebrochen werden. \nGenauer Fehler: " + ex;
MessageBox.Show(message);
richTextBox2.Text += message;
return;
}
}
//init
Variables.Minanzahl = new int[4] {
0, 0, 0, 0
};
//Feiertage werden eingelesen
string[] daten = this.feiertage.Text.Split(new char[] { Convert.ToChar("\n") });
for (int i = 0; i < daten.Count(); i++)
{
if (daten[i].Count() > 2)
{
try
{
Variables.Feiertage.Add(Convert.ToDateTime(daten[i].Trim()));
}
catch
{
MessageBox.Show("Feiertag auf der Zeile " + (i + 1) + " konnte nicht eingelesen werden");
return;
}
}
}
DateTime start = Start.Value.Date;
DateTime ende = Ende.Value.Date;
if (ende - start < TimeSpan.FromDays(0))
{
MessageBox.Show("Bitte wählen Sie gültige Anfangs- und Enddaten");
string.Format("hallo {0}!", "ww");
return;
}
int normaldays = 0;
int saturdays = 0;
int sundays = 0;
int holidays = 0;
//bestimmung anzahl der jeweiligen gruppe
while (start < ende)
{
if (DateTimeHelper.IsFeiertag(start))
{
holidays++;
}
else if (start.DayOfWeek == DayOfWeek.Saturday)
{
saturdays++;
}
else if (start.DayOfWeek == DayOfWeek.Sunday)
{
sundays++;
}
else
{
normaldays++;
}
start = start.AddDays(1);
}
int totaldays = holidays + normaldays + saturdays + sundays;
richTextBox2.Text += totaldays + " Tage zu verteilen\n";
//feiertage
VerteilerHelper.VerteileTageZuPunkte(holidays, 0);
Variables.Punkte = Variables.Punkte.OrderBy(d => d.Score).ToList();
//sonntag
VerteilerHelper.VerteileTageZuPunkte(sundays, 1);
Variables.Punkte = Variables.Punkte.OrderBy(d => d.Score).ToList();
//samstag
VerteilerHelper.VerteileTageZuPunkte(saturdays, 2);
Variables.Punkte = Variables.Punkte.OrderBy(d => d.Score).ToList();
//wochentag
VerteilerHelper.VerteileTageZuPunkte(normaldays, 3);
Variables.Punkte = Variables.Punkte.OrderByDescending(d => d.Score).ToList();
//sotiere nahc feiertag, damit niemand zwei hat
Variables.Punkte = Variables.Punkte.OrderByDescending(p => p.Anzahl[0]).ToList();
var firsttime = true;
//Punkte an Praxen verteilen
while (Variables.Punkte.Count > 0)
{
if (!firsttime)
{
Variables.Punkte = Variables.Punkte.OrderByDescending(d => d.Score).ToList();
}
else
{
firsttime = false;
}
//Punkte nach score sortieren (highest -> last)
Variables.Praxen = Variables.Praxen.OrderBy(p => p.ScorePerPoint).ToList();
//Punkte einfügen
for (int i = 0; i < Variables.Praxen.Count; i++)
{
if (Variables.Praxen[i].Punkte > Variables.Praxen[i].AssignedPoints.Count)
{
Variables.Punkte[0].Praxis = Variables.Praxen[i];
Variables.Praxen[i].AssignedPoints.Add(Variables.Punkte[0]);
Variables.Punkte.RemoveAt(0);
}
}
}
//beste Verteilung (einmalig)
List <Punkt> punkteliste = new List <Punkt>();
foreach (var item in Variables.Praxen)
{
int count = item.AssignedPoints.Count;
int totalcount = punkteliste.Count;
int step = totalcount / count;
count--;
while (true)
{
punkteliste.Insert(totalcount, item.AssignedPoints[count]);
count--;
totalcount -= step;
if (count <= -1)
{
break;
}
}
}
start = Start.Value.Date;
ende = Ende.Value.Date;
//daten verteilen
List <Punkt> punkte = CloneHelper.DeepClone(punkteliste);
var tries = 0;
var maxtries = Convert.ToInt32(triesproabstand.Text);
var minabst = Convert.ToInt32(minabstand.Text);
richTextBox2.Text += "versuche mit mindestabstand " + minabst + "\n";
var richtextboxtext = richTextBox2.Text;
bool found = false;
while (!found)
{
found = await Task.Run(() => VerteilerHelper.VerteileNotfalldienstPunkteZuDaten(punkte, start, ende,
Variables.History, minabst));
if (!found)
{
punkte = CloneHelper.DeepClone(punkteliste);
ThreadSafeRandom.Shuffle(punkte);
if (++tries > maxtries)
{
tries = 0;
minabst--;
if (minabst == 0)
{
MessageBox.Show(
"Vorgang kann nicht abgeschlossen werden. Die Punkte können nicht gemäss den Regeln verteilt werden. Durch das Zufallsprinzip kann das vorkommen, bitte versuchen Sie es einfach nocheinmal.");
return;
}
richTextBox2.Text += "\nversuche mit mindestabstand " + minabst + "\n";
richtextboxtext = richTextBox2.Text;
}
else
{
richTextBox2.Text = richtextboxtext + "gescheitert für mindestabstand " + minabst + " (" + tries + " / " + maxtries + ")\n";
}
}
}
richTextBox2.Text += "\n\nLösung gefunden!\n";
richTextBox2.Text += "mindestabstand: " + minabst + " Tage\n";
//add correct points to praxen
foreach (var praxise in Variables.Praxen)
{
praxise.AssignedPoints = punkte.Where(p => p.Praxis == praxise).ToList();
}
VerteilerHelper.VerteileWochentelefone(Variables.Praxen.OrderBy(p => p.Id).ToList(), start, ende, Convert.ToInt32(textBox2.Text.Trim()));
double tiefstes = Variables.Praxen.Where(d => d.Punkte > 0).OrderBy(d => d.ScorePerPoint).First().ScorePerPoint;
double durchschnitt = Variables.Praxen.Sum(d => d.ScorePerPoint * d.Punkte) / Variables.Praxen.Sum(p => p.Punkte);
double höchstens = Variables.Praxen.Where(d => d.Punkte > 0).OrderByDescending(d => d.ScorePerPoint).First().ScorePerPoint;
richTextBox2.Text += "tiefstes: " + tiefstes + "\n";
richTextBox2.Text += "höchstens: " + höchstens + "\n";
richTextBox2.Text += "durchschnitt: " + Math.Round(durchschnitt, 3) + "\n";
richTextBox2.Text += "dateien werden generiert...\n";
ExportHelper.ExportStats(savePath.Text);
var dic = Variables.Praxen.ToDictionary(praxis => praxis.Id, praxis => praxis.ScorePerPoint * praxis.Punkte - (praxis.Punkte * durchschnitt));
ExportHelper.ExportSave(dic, savePath.Text);
ExportHelper.ExportSql(punkte, Variables.Praxen, savePath.Text);
ExportHelper.ExportAnalythicsAndHistory(punkte, savePath.Text);
richTextBox2.Text += "erfolgreich abgeschlossen";
}
public MpImportMsSQLIceWarp()
{
connectionString = ImportHelper.PleskMySqlConnectionString();
}
public ActionResult NordicFranceBroker()
{
int userId = 15;
int parentPlaceId = 6;
var model = new NordicFranceViewModel();
var franceProducts = _db.NordicFranceProductRepository.GetAll().Where(n => n.Id > 3616).ToList();
var products = new List <ProductItem>();
foreach (var s_product in franceProducts)
{
if (!string.IsNullOrEmpty(s_product.Address) && !string.IsNullOrEmpty(s_product.Address2))
{
string placeName = string.Empty;
if (!string.IsNullOrEmpty(s_product.City))
{
placeName = ImportHelper.GetFirstString(s_product.City).Trim();
}
else
{
placeName = ImportHelper.GetFirstString(s_product.Area).Trim();
}
var p = new ProductItem();
p.Price = ImportHelper.GetDecimalFromString(s_product.Price) / 10;
p.SalesResponsibleId = userId;
p.PlaceId = SetCreateWretmanPlaceId(placeName, parentPlaceId);
p.DisplayPlace = placeName;
//p.LivingSpace = ImportHelper.GetFirstIntFromString(s_product.LivingSpace);
//p.NumberOfRooms = ImportHelper.GetFirstIntFromString(s_product.NumberOfRooms);
p.ExternalLink = s_product.ExternalLink;
p.Address = !string.IsNullOrEmpty(s_product.Address) ? s_product.Address.Replace("+", "").Replace(":", "").Replace(".", "-").Replace("&", "") : ImportHelper.GetPartOfString(s_product.Address2, '-', 1).Replace("+", "").Replace(":", "").Replace(".", "-").Replace("&", "");
p.Description = s_product.Desc1 + s_product.Desc2;
p.ImageUrl1 = s_product.ImageUrl1;
p.ImageUrl2 = s_product.ImageUrl2;
p.ImageUrl3 = s_product.ImageUrl3;
p.ImageUrl4 = s_product.ImageUrl4;
p.ImageUrl5 = s_product.ImageUrl5;
p.ProductTypeId = 1;
if (!string.IsNullOrEmpty(placeName))
{
string requestUri = string.Format("https://maps.googleapis.com/maps/api/geocode/xml?address={0}&key=AIzaSyBML9XUO8FpiHQShjmlL7QnIw7UkWheOQE", Uri.EscapeDataString(placeName));
XmlDocument xdoc = new XmlDocument();
xdoc.Load(requestUri);
var result = xdoc.SelectNodes("/GeocodeResponse/result/geometry/location");
foreach (XmlNode objXmlNode in result)
{
p.Lat = objXmlNode.ChildNodes.Item(0).InnerText;
p.Long = objXmlNode.ChildNodes.Item(1).InnerText;
}
}
SaveProduct(p);
products.Add(p);
}
}
model.Products = products;
model.NumberOfProducts = products.Count;
return(View(model));
}
public ActionResult CCHomesBroker()
{
int userId = 6;
var model = new CCHomesViewModel();
var ccProducts = _db.CcHomesProductRepository.GetAll().Where(x => x.Id > 1688).ToList();
var products = new List <ProductItem>();
foreach (var s_product in ccProducts)
{
var place = SetCCHomesPlaces(s_product.Place);
if (place != null)
{
var p = new ProductItem();
if (s_product.Price.Contains("SEK"))
{
p.Price = ImportHelper.GetDecimalFromString(s_product.Price) / 10;
}
else
{
p.Price = ImportHelper.GetDecimalFromString(s_product.Price);
}
p.SalesResponsibleId = userId;
p.PlaceId = place.Id;
p.DisplayPlace = place.NameSV;
p.LivingSpace = ImportHelper.GetFirstIntFromString(s_product.LivingSpace);
p.NumberOfRooms = ImportHelper.GetFirstIntFromString(s_product.NumberOfRooms);
p.ExternalLink = s_product.ExternalLink;
p.Address = s_product.Address;
p.Description = s_product.Description;
p.ImageUrl1 = ImportHelper.GetUrlFromString(s_product.ImageUrl1);
p.ImageUrl2 = ImportHelper.GetUrlFromString(s_product.ImageUrl2);
p.ImageUrl3 = ImportHelper.GetUrlFromString(s_product.ImageUrl3);
p.ImageUrl4 = ImportHelper.GetUrlFromString(s_product.ImageUrl4);
p.ImageUrl5 = ImportHelper.GetUrlFromString(s_product.ImageUrl5);
p.ProductTypeId = 1;
if (!string.IsNullOrEmpty(place.Name))
{
string requestUri = string.Format("https://maps.googleapis.com/maps/api/geocode/xml?address={0}&key=AIzaSyBML9XUO8FpiHQShjmlL7QnIw7UkWheOQE", Uri.EscapeDataString(place.Name));
XmlDocument xdoc = new XmlDocument();
xdoc.Load(requestUri);
var result = xdoc.SelectNodes("/GeocodeResponse/result/geometry/location");
foreach (XmlNode objXmlNode in result)
{
p.Lat = objXmlNode.ChildNodes.Item(0).InnerText;
p.Long = objXmlNode.ChildNodes.Item(1).InnerText;
}
}
SaveProduct(p);
products.Add(p);
}
}
model.Products = products;
model.NumberOfProducts = products.Count;
return(View(model));
}
public void FormatJSON()
{
var tmpNewText = ExportHelper.CreateJsonFromClassList(ImportHelper.ImportClasses(JavaLangClassJson.JavaLang));
Assert.AreEqual(true, true);
}
public ActionResult PortugalBroker()
{
int userId = 7;
int parentPlaceId = 16;
var model = new PortugalImportViewModel();
var portugalProducts = _db.PortugalMaklarnaProductRepository.GetAll().Where(p => p.Id > 946).ToList();
var products = new List <ProductItem>();
foreach (var s_product in portugalProducts)
{
string placeName = string.Empty;
if (!string.IsNullOrEmpty(s_product.City))
{
if (s_product.City.Contains("("))
{
placeName = ImportHelper.GetPartOfString(s_product.City, '(', 0).Trim();
}
else
{
placeName = s_product.City.Trim();
}
}
else
{
placeName = s_product.Area.Trim();
}
var p = new ProductItem();
p.Price = ImportHelper.GetDecimalFromString(s_product.Price);
p.SalesResponsibleId = userId;
p.PlaceId = SetCreateWretmanPlaceId(placeName, parentPlaceId);
p.DisplayPlace = placeName;
p.LivingSpace = ImportHelper.GetFirstIntFromString(s_product.LivingSpace);
p.NumberOfRooms = ImportHelper.GetFirstIntFromString(s_product.NumberOfRooms);
p.ExternalLink = s_product.ExternalLink;
p.YearBuilt = !string.IsNullOrEmpty(s_product.YearBuilt) ? new DateTime(int.Parse(s_product.YearBuilt), 1, 1) : (DateTime?)null;
p.Address = s_product.Address.Replace("+", "").Replace(":", "").Replace(".", "-").Replace("&", "");
p.Description = s_product.Description;
p.RefNr = s_product.RefNo;
p.ImageUrl1 = s_product.ImageUrl1;
p.ImageUrl2 = s_product.ImageUrl2;
p.ImageUrl3 = s_product.ImageUrl3;
p.ImageUrl4 = s_product.ImageUrl4;
p.ImageUrl5 = s_product.ImageUrl5;
p.ProductTypeId = 1;
if (!string.IsNullOrEmpty(placeName))
{
string requestUri = string.Format("https://maps.googleapis.com/maps/api/geocode/xml?address={0}&key=AIzaSyBML9XUO8FpiHQShjmlL7QnIw7UkWheOQE", Uri.EscapeDataString(placeName));
XmlDocument xdoc = new XmlDocument();
xdoc.Load(requestUri);
var result = xdoc.SelectNodes("/GeocodeResponse/result/geometry/location");
foreach (XmlNode objXmlNode in result)
{
p.Lat = objXmlNode.ChildNodes.Item(0).InnerText;
p.Long = objXmlNode.ChildNodes.Item(1).InnerText;
}
}
SaveProduct(p);
products.Add(p);
}
model.Products = products;
model.NumberOfProducts = products.Count;
return(View(model));
}
public void ImportExcel()
{
var sheet = ImportHelper.GetWorkSheet(ExportExcel());
var data = sheet.Read <TestModel>();
}
internal static SPSite ConvertWebToSite(string sourceurl, string targeturl, SPSiteSubscription siteSubscription, bool suppressAfterEvents,
bool noFileCompression, string exportedFile, bool createSiteInDB, string databaseName, bool createManagedPath,
bool haltOnWarning, bool haltOnFatalError, bool deleteSource, string title, string description, uint nLCID, string ownerName,
string ownerEmail, string ownerLogin, string secondaryContactName, string secondaryContactLogin, string secondaryContactEmail,
string quota, bool useHostHeaderAsSiteName)
{
try
{
if (!string.IsNullOrEmpty(exportedFile))
{
if (noFileCompression)
{
if (!Directory.Exists(exportedFile))
{
throw new SPException(SPResource.GetString("DirectoryNotFoundExceptionMessage", new object[] { exportedFile }));
}
}
else
{
if (!File.Exists(exportedFile))
{
throw new SPException(SPResource.GetString("FileNotFoundExceptionMessage", new object[] { exportedFile }));
}
}
}
if (createSiteInDB && string.IsNullOrEmpty(databaseName))
{
throw new SPSyntaxException("databasename is required if creating the site in a new or existing database.");
}
using (SPSite sourceSite = new SPSite(sourceurl))
using (SPWeb sourceWeb = sourceSite.AllWebs[Utilities.GetServerRelUrlFromFullUrl(sourceurl)])
{
if (createManagedPath)
{
Logger.Write("Adding managed path...");
AddManagedPath(targeturl, haltOnWarning);
Logger.Write("Managed path added.\r\n");
}
if (string.IsNullOrEmpty(exportedFile))
{
Logger.Write("Exporting site...");
exportedFile = ExportHelper.ExportSite(sourceurl,
haltOnWarning,
haltOnFatalError,
noFileCompression,
true, !Logger.Verbose, SPIncludeVersions.All, 0, false, true);
Logger.Write("Site exported.\r\n");
}
Logger.Write("Creating site for import...");
SPWebApplicationPipeBind webAppBind = new SPWebApplicationPipeBind(targeturl);
SPWebApplication webApp = webAppBind.Read(false);
SPSite targetSite = CreateSite(webApp, databaseName, siteSubscription, null, title, description, ownerName, ownerEmail, quota, secondaryContactName,
secondaryContactEmail, useHostHeaderAsSiteName, nLCID, new Uri(targeturl), ownerLogin, secondaryContactLogin);
SPWeb targetWeb = targetSite.AllWebs[Utilities.GetServerRelUrlFromFullUrl(targeturl)];
Logger.Write("Site created.\r\n");
Logger.Write("Importing site...");
ImportHelper import = new ImportHelper();
import.ImportSite(exportedFile,
targeturl,
haltOnWarning,
haltOnFatalError,
noFileCompression, true, !Logger.Verbose, true,
false, SPUpdateVersions.Append, suppressAfterEvents);
Logger.Write("Site imported.\r\n");
#if MOSS
Logger.Write("Repairing imported site...");
// Need to add a small delay here as the repair seems to fail occassionally due to a timing issue.
Common.TimerJobs.ExecAdmSvcJobs.Execute(false, true);
int tryCount = 0;
while (true)
{
try
{
tryCount++;
Common.SiteCollections.RepairSiteCollectionImportedFromSubSite.RepairSite(sourceurl, targeturl);
break;
}
catch (InvalidPublishingWebException)
{
if (haltOnWarning)
{
throw;
}
if (tryCount > 3)
{
Logger.WriteWarning("Repair of site collection failed - unable to get Pages library. Manually run 'repairsitecollectionimportedfromsubsite' command to try again.");
break;
}
else
{
Thread.Sleep(10000);
}
}
catch (OutOfMemoryException)
{
if (haltOnWarning)
{
throw;
}
Logger.WriteWarning("Server ran out of memory and was not able to complete the repair operation. Manually run 'repairsitecollectionimportedfromsubsite' command to try again.");
break;
}
}
Logger.Write("Imported site repaired.\r\n");
#endif
// Upgrade any redirect links if present.
if (sourceSite.WebApplication.Id == targetSite.WebApplication.Id)
{
//Console.WriteLine("Repairing Area upgrade URLs...");
// The spsredirect.aspx page which uses the upgrade list only supports server relative urls so
// if we change the link to be absolute the redirect won't work so don't bother changing it.
// Note that we could get around this if we had a simple redirect page that could take in
// a target url - then we could have spsredirect.aspx load the server relative redirect
// page which would have the actual url passed into it (unfortunately redirect.aspx does
// some funky stuff so we can't use it).
//UpdateV2ToV3UpgradeAreaUrlMappings.FixUpgradeAreaUrlMappings(sourceSite.WebApplication,
// sourceWeb.ServerRelativeUrl,
// targetWeb.ServerRelativeUrl);
//Console.WriteLine("Area upgrade URLs repaired.\r\n");
}
if (deleteSource)
{
Logger.Write("Deleting source web...");
if (!sourceWeb.IsRootWeb)
{
DeleteSubWebs(sourceWeb.Webs);
sourceWeb.Delete();
Logger.Write("Source web deleted.\r\n");
Logger.Write("You can find the exported web at " + exportedFile + "\r\n");
}
else
{
Logger.Write("Source web is a root web - cannot delete.");
}
}
return(targetSite);
}
}
catch (Exception ex)
{
Logger.WriteException(new System.Management.Automation.ErrorRecord(ex, null, System.Management.Automation.ErrorCategory.NotSpecified, null));
}
return(null);
}
public void Page_Load(object sender, EventArgs e)
{
string op = Context.Request["op"];
string editorOp = Context.Request["editorOp"];
//下拉专业id
string proId = Request.QueryString["proId"];
//下拉学院id
string collegeId = Request.QueryString["collegeId"];
//输入框信息
string strsearch = Request.QueryString["search"];
userType = Session["state"].ToString();
if (userType == "0")
{
colds = colBll.Select();
prods = proBll.Select();
stuAddProds = proBll.Select();
if (collegeId == null || collegeId == "0" || collegeId == "null")
{
prods = proBll.Select();
}
else
{
prods = proBll.SelectByCollegeId(int.Parse(collegeId));
}
if (proId != null && proId != "null" && collegeId == "null")
{
//学院为空 专业不为空
getdata(Searchdrop());
}
else if (collegeId != null && collegeId != "null" && (proId == "null" || proId == "0"))
{
//学院不为空 专业为空
getdata(SearchByCollege());
}
else if (collegeId != null && collegeId != "null" && proId != null && proId != "null")
{
//两个都不为空
getdata(SearchProAndCollege());
}
else if (strsearch != null)
{
getdata(Search());
}
else
{
getdata("");
colds = colBll.Select();
}
}
else if (userType == "2")
{
tea = (Teacher)Session["user"];
int usercollegeId = tea.college.ColID;
colds = colBll.Select();
prods = proBll.SelectByCollegeId(usercollegeId);
stuAddProds = proBll.SelectByCollegeId(usercollegeId);
if (strsearch != null)
{
getdata(Search());
}
else if (proId != null && proId != "null")
{
getdata(Searchdrop());
}
else
{
getdata("");
}
}
if (op == "add")//添加
{
saveStudent();
}
if (editorOp == "editor")//编辑
{
editorStu();
}
if (op == "delete")//删除
{
deleteStu();
}
if (op == "stuadd")
{
//学院与专业下拉联动
int addcollegeId = int.Parse(Context.Request["stuAddcollegeId"]);
stuAddProds = proBll.SelectByCollegeId(addcollegeId);
DataTable dt = stuAddProds.Tables[0];
string data = DataTableToJson(dt);
Response.Write(data);
Response.End();
}
if (op == "reset")
{
//重置密码
stuPasswordReset();
}
if (op == "import")
{
int proid = Convert.ToInt32(Context.Request["proId"]);
string path = Security.Decrypt(Request["fileName"]);
DataTable dt = TableHelper.GetDistinctSelf(ExcelHelp.excelToDt(path, "excel"), "学号");
int i = ImportHelper.Student(dt, proid);
int row = ExcelHelp.excelToDt(path, "excel").Rows.Count;
int repeat = row - i;
if (i > 0)
{
Response.Write("导入成功，总数据有" + row + "条，共导入" + i + "条数据，重复数据有" + repeat + "条");
Response.End();
}
else
{
Response.Write("导入失败，总数据有" + row + "条，共导入" + i + "条数据，重复数据有" + repeat + "条");
Response.End();
}
}
}
/// <summary>
/// 导入订单信息
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnImportOA01_Click(object sender, EventArgs e)
{
#region 效率低导入
//if (FileUpload1.HasFile)
//{
// string fileExt = System.IO.Path.GetExtension(FileUpload1.FileName);
// if (fileExt == ".xlsx" || fileExt == ".xls")
// {
// DataSet dt = Sinoo.Common.Excel.ExcelHandler.GetDataSetFromExcel(FileUpload1.FileContent);
// if (dt.Tables[0] != null && dt.Tables[0].Rows.Count > 0)
// {
// UserBase _UserBase = Session["USER_SESSION"] as UserBase;
// Dictionary<string, string> dictionary = _ExcelBLL.ImportOrderBase(dt, _UserBase);
// this.Label1.Text = string.Format("提示:{0}", dictionary["error"]);
// }
// else
// {
// this.Label1.Text = "导入失败";
// }
// }
// else
// {
// this.Label1.Text = "格式不正确";
// }
//}
#endregion
bool chkIsReWrite = false;
//验证Excel
if (!this.IsValidator(this.FileUpload1))
{
return;
}
ImportHelper importHelper = new ImportHelper(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString);
string tableHtml = string.Empty; //返回到前台的错误信息列表
const string ExcelFilePath = "PM"; //excel模板所在的文件夹
#region 导入Excel
Stream stream = Request.Files[0].InputStream; //this.FileUpload1.FileContent;
//获取excel中的数据
importHelper.GetExcel(stream);
//进行数据验证
importHelper.ValidateData(ExcelFilePath, "订单信息", chkIsReWrite);
#endregion
if (importHelper.ErrorInfo != null && importHelper.ErrorInfo.Rows.Count > 0)
{
tableHtml = DataTableHelper.DataTableToString(importHelper.ErrorInfo, 100);
this.Label1.Text = tableHtml;
//this.ErrorDiv.InnerHtml = tableHtml;
Page.ClientScript.RegisterStartupScript(Page.GetType(), "", "<script>alert('导入失败');Location();</script>");
return;
}
else
{
DataTable dt = importHelper.AllData.Tables[0];
DataTable dt5 = _UserBll.SelectUserBasePosition(""); //所有的销售人. （根据用户登录的所属区域）
DataTable dt6 = _CustomerBLL.SelectAC(); //所有应用代码
DataTable dt7 = _CustomerBLL.SelectCustomerBase(); //客户资料
dt.Columns.Add("GUID");
foreach (DataRow item in dt.Rows)
{
#region 验证销售员
DataRow[] dr = dt5.Select(string.Format(" UA01005 = '{0}' ", item["销售员"].ToString()));
if (dr.Length > 0)
{
item["销售员"] = dr[0]["UA01001"];
}
else
{
item["销售员"] = 0;
}
#endregion
#region 验证应用代码
DataRow[] dr1 = dt6.Select(string.Format(" OB02002 = '{0}' ", item["应用代码"].ToString()));
if (dr1.Length > 0)
{
item["应用代码"] = dr1[0]["OB02001"];
}
else
{
item["应用代码"] = 0;
}
#endregion
#region 验证客户名称
DataRow[] dr7 = dt7.Select(string.Format(" CA01003 = '{0}' ", item["客户名称"].ToString()));
if (dr7.Length > 0)
{
item["客户名称"] = dr7[0]["CA01001"];
}
else
{
item["客户名称"] = 0;
}
#endregion
item["GUID"] = Guid.NewGuid();
}
Stopwatch stop = new Stopwatch();
long millCount = 0;
stop.Start();
if (importHelper.InsertAllData())
{
stop.Stop();
millCount = stop.ElapsedMilliseconds;
//表示成功
Page.ClientScript.RegisterStartupScript(Page.GetType(), "", "<script>alert('导入成功')</script>");
}
else
{
stop.Stop();
millCount = stop.ElapsedMilliseconds;
//执行失败
Page.ClientScript.RegisterStartupScript(Page.GetType(), "", "<script>alert('导入失败');Location();</script>");
}
}
}
private Place SetCCHomesPlaces(string placeInput)
{
Place place = new Place();
if (!string.IsNullOrEmpty(placeInput))
{
var places = _db.PlaceRepository.GetAll();
StringBuilder builder = new StringBuilder();
string[] lines = placeInput.Split('\n');
for (int i = 0; i < lines.Length; i++)
{
if (i > 0)
{
builder.Append("<br/>\n");
}
builder.Append(WebUtility.HtmlEncode(lines[i]));
}
string firstplace = string.Empty;
string secondplace = string.Empty;
if (builder.ToString().Contains("<br/>\n<br/>"))
{
firstplace = ImportHelper.GetPartOfString(builder.ToString(), "<br/>\n<br/>", 0);
secondplace = ImportHelper.GetPartOfString(builder.ToString(), "<br/>\n<br/>", 1);
}
else
{
firstplace = ImportHelper.GetPartOfString(builder.ToString(), "<br/>", 0);
secondplace = ImportHelper.GetPartOfString(builder.ToString(), "<br/>", 1);
}
int?sPlaceId = null;
if (!string.IsNullOrEmpty(secondplace))
{
Place sPlace = new Place();
bool placeExist = false;
foreach (var p in places)
{
if (p.Name.ToLower().Trim() == WebUtility.HtmlDecode(secondplace).ToLower().Trim())
{
sPlace = p;
placeExist = true;
}
}
if (!placeExist)
{
sPlace.Name = Helper.FirstCharToUpper(WebUtility.HtmlDecode(secondplace).ToLower().Trim());
sPlace.NameSV = Helper.FirstCharToUpper(WebUtility.HtmlDecode(secondplace).ToLower().Trim());
sPlace.PlaceTypeId = 2;
_db.PlaceRepository.Insert(sPlace);
_db.SaveChanges();
}
sPlaceId = sPlace.Id;
}
if (!string.IsNullOrEmpty(firstplace))
{
bool placeExist = false;
foreach (var p in places)
{
if (p.Name.ToLower().Trim() == WebUtility.HtmlDecode(firstplace).ToLower().Trim())
{
place = p;
placeExist = true;
}
}
if (!placeExist)
{
place.Name = Helper.FirstCharToUpper(WebUtility.HtmlDecode(firstplace).ToLower().Trim());
place.NameSV = Helper.FirstCharToUpper(WebUtility.HtmlDecode(firstplace).ToLower().Trim());
place.PlaceTypeId = 3;
place.ParentId = sPlaceId.HasValue ? sPlaceId.Value : (int?)null;
_db.PlaceRepository.Insert(place);
_db.SaveChanges();
}
}
}
return(place);
}
protected void btnUpload_Click(object sender, EventArgs e)
{
try
{
HSSFWorkbook excel = new HSSFWorkbook(fileUpload.PostedFile.InputStream);
Sheet sheet = excel.GetSheetAt(0);
IEnumerator rows = sheet.GetRowEnumerator();
Row custrow = sheet.GetRow(2);
string cust = custrow.GetCell(1).StringCellValue;//客户代码
Customer customer = TheCustomerMgr.LoadCustomer(cust);
Row row_startdate = sheet.GetRow(3);
string startdate = row_startdate.GetCell(1).StringCellValue; //开始日期
Row row_enddate = sheet.GetRow(4);
string enddate = row_enddate.GetCell(1).StringCellValue; //结束日期
startdate = startdate == string.Empty ? DateTime.Now.AddMonths(-1).ToShortDateString() : startdate;
enddate = enddate == string.Empty ? DateTime.Now.ToShortDateString() : enddate;
IList <ActingBill> actingBillList = TheActingBillMgr.GetActingBill(cust, "", DateTime.Parse(startdate), DateTime.Parse(enddate), "", "", this.ModuleType, this.billNo);
var actbill = (from i in actingBillList//按ASN ITME聚合 得到总的可用数量
group i by new
{
i.IpNo,
i.Item.Code
} into k
select new
{
Item = k.Key.Code,
IpNo = k.Key.IpNo,
Amt = k.Sum(i => i.BillQty - i.BilledQty)
}).ToList();
ImportHelper.JumpRows(rows, 7);
IList <ActingBill> createBillList = new List <ActingBill>();
IList <Bill> createdBill = new List <Bill>();
while (rows.MoveNext())
{
Row curow = (HSSFRow)rows.Current;
string asn = curow.GetCell(0).StringCellValue;
string item = curow.GetCell(1).NumericCellValue.ToString();
decimal qty = decimal.Parse(curow.GetCell(2).NumericCellValue.ToString());
if (asn != string.Empty)
{
var temp_qty = actbill.Where(i => i.Item == item && i.IpNo == asn).ToList();
if (temp_qty.Count > 0)
{
if (Math.Abs(temp_qty[0].Amt) - Math.Abs(qty) >= 0)
{
IList <ActingBill> result = actingBillList.Where(i => i.Item.Code == item && i.IpNo == asn).ToList();
foreach (ActingBill _actbill in result)
{
if (qty == 0)
{
break; //扣减完了
}
if (_actbill.BillQty - _actbill.BilledQty - _actbill.CurrentBillQty == 0)
{
continue; //actbill可用数量用完了
}
//if (_actbill.BillQty - _actbill.BilledQty - _actbill.CurrentBillQty - qty >= 0)
//{
// _actbill.CurrentBillQty = _actbill.CurrentBillQty + qty;
// qty = 0;
//}
//else
//{
// _actbill.CurrentBillQty = _actbill.BillQty - _actbill.BilledQty - _actbill.CurrentBillQty;
// qty = qty - _actbill.BillQty - _actbill.BilledQty - _actbill.CurrentBillQty;
//}
if (_actbill.BillQty > 0)
{
if (_actbill.BillQty - _actbill.BilledQty - _actbill.CurrentBillQty - qty >= 0)
{
_actbill.CurrentBillQty = _actbill.CurrentBillQty + qty;
qty = 0;
}
else
{
_actbill.CurrentBillQty = _actbill.BillQty - _actbill.BilledQty - _actbill.CurrentBillQty;
qty = qty - _actbill.CurrentBillQty;
}
}
else
{
if (_actbill.BillQty - _actbill.BilledQty - _actbill.CurrentBillQty - qty <= 0)
{
_actbill.CurrentBillQty = _actbill.CurrentBillQty + qty;
qty = 0;
}
else
{
_actbill.CurrentBillQty = _actbill.BillQty - _actbill.BilledQty - _actbill.CurrentBillQty;
qty = qty - _actbill.CurrentBillQty;
}
}
createBillList.Add(_actbill);
}
}
else
{
ShowErrorMessage("行" + (curow.RowNum + 1).ToString() + "数量大于剩余开票数量！");
return;
}
}
else
{
ShowErrorMessage("行" + (curow.RowNum + 1).ToString() + " ASN或零件不存在！请查询对应记录后再导入！");
return;
}
}
}
int cnt = 0;
try
{
while (0 < createBillList.Count)
{
cnt++;
var t = from i in createBillList
group i by new
{
i.Item.Code
} into k
select new
{
Item = k.Key.Code,
Amt = k.Sum(i => i.CurrentBillQty * i.UnitPrice)
};
List <string> abM = new List <string>();
List <string> zeroM = new List <string>();
foreach (var i in t)
{
if (i.Amt > 1000000)
{
abM.Add(i.Item);
}
if (i.Amt == 0 && cnt == 1)
{
zeroM.Add(i.Item);
}
}
#region 超过100w的
foreach (string s in abM)
{
IList <ActingBill> tempList = createBillList.Where(i => i.Item.Code == s).OrderByDescending(i => i.UnitPrice * i.CurrentBillQty).ToList();
IList <ActingBill> amtList = new List <ActingBill>();
decimal amount = 0;
foreach (ActingBill act in tempList)
{
if (amount + act.CurrentBillQty * act.UnitPrice <= 1000000)
{
amtList.Add(act);
amount += act.CurrentBillQty * act.UnitPrice;
}
else if (act.CurrentBillQty * act.UnitPrice > 1000000)
{
decimal qty = Math.Round(1000000 / act.UnitPrice, 0);
act.CurrentBillQty = qty;
amtList.Add(act);
}
else
{
continue;
}
}
if (amtList.Count > 0)
{
IList <Bill> billList = TheBillMgr.CreateBill(amtList, this.CurrentUser);
createdBill.Add(billList[0]);
}
foreach (ActingBill act in amtList)
{
if (Math.Round(1000000 / act.UnitPrice, 0) > act.CurrentBillQty)
{
createBillList.Remove(act);
}
else
{
act.CurrentBillQty = act.BillQty - act.BilledQty - Math.Round(1000000 / act.UnitPrice, 0) * cnt;
}
}
}
#endregion
#region 未超过100w的
List <string> normal = new List <string>();
var bAm = (from i in createBillList
group i by new
{
i.Item.Code
} into k
select new
{
Item = k.Key.Code,
Amt = k.Sum(i => i.CurrentBillQty * i.UnitPrice)
}).Where(i => i.Amt < 1000000 && i.Amt != 0).OrderByDescending(i => i.Amt).ToList();
while (bAm.Count > 0)
{
decimal tempAmt = 0;
IList <string> tempGroup = new List <string>();
foreach (var i in bAm)
{
if (i.Amt + tempAmt <= 1000000)
{
tempGroup.Add(i.Item);
tempAmt += i.Amt;
}
else
{
continue;
}
}
List <ActingBill> tempAct = new List <ActingBill>();
foreach (string item in tempGroup)
{
List <ActingBill> _tempAct = (createBillList.Where(i => i.Item.Code == item)).ToList();
foreach (ActingBill bill in _tempAct)
{
tempAct.Add(bill);
}
}
for (int i = bAm.Count; i > 0; i--)
{
if (tempGroup.Contains(bAm[i - 1].Item))
{
bAm.Remove(bAm[i - 1]);
i = bAm.Count + 1;
}
}
if (tempAct.Count > 0)
{
IList <Bill> billList = TheBillMgr.CreateBill(tempAct, this.CurrentUser);
createdBill.Add(billList[0]);
}
foreach (ActingBill bill in tempAct)
{
createBillList.Remove(bill);
}
}
#endregion
if (zeroM.Count > 0 && cnt == 1)
{
foreach (string code in zeroM)
{
IList <ActingBill> tempList = createBillList.Where(i => i.Item.Code == code).OrderByDescending(i => i.UnitPrice * i.CurrentBillQty).ToList();
if (tempList.Count > 0)
{
if (createdBill.Count > 0)
{
TheBillMgr.AddBillDetail(createdBill[0], tempList, this.CurrentUser);
}
else
{
IList <Bill> billList = TheBillMgr.CreateBill(tempList, this.CurrentUser);
createdBill.Add(billList[0]);
}
}
foreach (ActingBill actb in tempList)
{
createBillList.Remove(actb);
}
}
}
}
}
catch (BusinessErrorException ex)
{
ShowErrorMessage(ex);
}
catch (Exception ex)
{
ShowErrorMessage(ex.Message);
}
_createdBillNo += " 已创建以下账单：<br/>";
foreach (Bill b in createdBill)
{
_createdBillNo += b.BillNo + "<br />";
}
#region output result xls
if (createdBill != null && createdBill.Count > 0)
{
this.btnBack_Click(sender, e);
ExportResult(createdBill);
}
#endregion
}
catch (Exception exception)
{
ShowErrorMessage(exception.Message);
}
}
public ActionResult WretmanBroker()
{
int userId = 10;
var model = new WretmanImportViewModel();
var wretmanProducts = _db.WretmanProductRepository.GetAll().Where(w => w.Id > 286).ToList();
var products = new List <ProductItem>();
foreach (var s_product in wretmanProducts)
{
string placeName = string.Empty;
if (s_product.Place.Contains(","))
{
placeName = ImportHelper.GetPartOfString(s_product.Place, ',', 0);;
}
else
{
placeName = s_product.Place;
}
var p = new ProductItem();
p.Price = ImportHelper.GetDecimalFromString(s_product.Price);
p.SalesResponsibleId = userId;
p.PlaceId = SetCreateWretmanPlaceId(placeName, 10);
p.DisplayPlace = placeName;
p.LivingSpace = ImportHelper.GetLivingSpace(s_product.Spaces);
p.NumberOfRooms = ImportHelper.GetFirstIntFromString(s_product.Spaces);
p.ExternalLink = s_product.ExternalLink;
p.Address = ImportHelper.GetPartOfString(s_product.Address, '|', 0);
p.Description = ImportHelper.GetPartOfString(s_product.Description, '—', 1);
p.RefNr = ImportHelper.GetPartOfString(s_product.Description, '—', 0).Trim();
p.ImageUrl1 = s_product.ImageUrl1.Contains("picture-100") ? s_product.ImageUrl1.Replace("picture-100", "picture-1024") : s_product.ImageUrl1;
p.ImageUrl2 = s_product.ImageUrl2.Contains("picture-100") ? s_product.ImageUrl2.Replace("picture-100", "picture-1024") : s_product.ImageUrl2;
p.ImageUrl3 = s_product.ImageUrl3.Contains("picture-100") ? s_product.ImageUrl3.Replace("picture-100", "picture-1024") : s_product.ImageUrl3;
p.ImageUrl4 = s_product.ImageUrl4.Contains("picture-100") ? s_product.ImageUrl4.Replace("picture-100", "picture-1024") : s_product.ImageUrl4;
p.ImageUrl5 = s_product.ImageUrl5.Contains("picture-100") ? s_product.ImageUrl5.Replace("picture-100", "picture-1024") : s_product.ImageUrl5;
p.ProductTypeId = 1;
if (!string.IsNullOrEmpty(placeName))
{
string requestUri = string.Format("https://maps.googleapis.com/maps/api/geocode/xml?address={0}&key=AIzaSyBML9XUO8FpiHQShjmlL7QnIw7UkWheOQE", Uri.EscapeDataString(placeName));
XmlDocument xdoc = new XmlDocument();
xdoc.Load(requestUri);
var result = xdoc.SelectNodes("/GeocodeResponse/result/geometry/location");
foreach (XmlNode objXmlNode in result)
{
p.Lat = objXmlNode.ChildNodes.Item(0).InnerText;
p.Long = objXmlNode.ChildNodes.Item(1).InnerText;
}
}
SaveProduct(p);
products.Add(p);
}
model.Products = products;
model.NumberOfProducts = products.Count;
return(View(model));
}
/// <summary>
/// 文件上传 taskno:181695
/// djin 2013-9-2
/// </summary>
protected void btnUpload_Click(object sender, EventArgs e)
{
HSSFWorkbook excel = new HSSFWorkbook(fileUpload.PostedFile.InputStream);
Sheet sheet = excel.GetSheetAt(0);
IEnumerator rows = sheet.GetRowEnumerator();
//Row custrow = sheet.GetRow(2);
// string sup = custrow.GetCell(1).StringCellValue;//客户代码
//Supplier su = TheSupplierMgr.LoadSupplier(sup);
//Row row_startdate = sheet.GetRow(3);
//string startdate = row_startdate.GetCell(1).StringCellValue;//开始日期
//Row row_enddate = sheet.GetRow(4);
//string enddate = row_enddate.GetCell(1).StringCellValue;//结束日期
//startdate = startdate == string.Empty ? DateTime.Now.AddMonths(-1).ToShortDateString() : startdate;
//enddate = enddate == string.Empty ? DateTime.Now.ToShortDateString() : enddate;
ImportHelper.JumpRows(rows, 1);
IList <TransportationActBill> tactbillList = new List <TransportationActBill>();
Hashtable th = new Hashtable();
string supply = string.Empty;
while (rows.MoveNext())
{
Row curow = (HSSFRow)rows.Current;
string shiporder = curow.GetCell(0).StringCellValue;
if (th.ContainsKey(shiporder))
{
continue; //避免重复
}
// decimal cur = decimal.Parse(curow.GetCell(1).NumericCellValue.ToString());
if (shiporder != string.Empty)
{
IList <TransportationActBill> tactbill = TheTransportationActBillMgr.GetTransportationActBill(shiporder);
if (tactbill.Count > 0)
{
foreach (TransportationActBill tbill in tactbill)
{
if (!string.IsNullOrEmpty(supply))
{
if (tbill.BillAddress.Party.Code != supply)
{
ShowErrorMessage("行" + curow.RowNum.ToString() + "供应商的代码不一致！");
return;
}
}
else
{
supply = tbill.BillAddress.Party.Code;
}
if (tbill.Status == "Create")
{
tbill.CurrentBillQty = tbill.BillQty - tbill.BilledQty;
tbill.CurrentBillAmount = tbill.CurrentBillQty * tbill.UnitPrice;
tactbillList.Add(tbill);
}
}
}
else
{
ShowErrorMessage("行" + curow.RowNum.ToString() + "还没有计价！");
return;
}
}
else
{
ShowErrorMessage("行" + curow.RowNum.ToString() + "无运单号！");
return;
}
}
if (tactbillList.Count > 0)
{
try
{
IList <TransportationBill> transportationBillList = TheTransportationBillMgr.CreateTransportationBill(tactbillList, this.CurrentUser);
if (transportationBillList != null && transportationBillList.Count > 0)
{
ExportResult(transportationBillList);
btnBack_Click(sender, e);
}
}
catch (BusinessErrorException ex)
{
ShowErrorMessage(ex);
}
catch (Exception ex)
{
ShowErrorMessage(ex.Message);
}
}
else
{
ShowErrorMessage("账单创建失败！");
}
}
public void test()
{
var dt = ImportHelper.ToDataTable(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "test.xls"), "result", 0);
}
static void Main(string[] args)
{
if (args.Count() > 0)
{
if (args[0] == "ship")
{
#region Mark As Shipped
try
{
//This is a fix for it being a desktop application. This should not
//be run on a live webserver
ServicePointManager.ServerCertificateValidationCallback = delegate { return(true); };
//Use app.config so the directory can be changed regardless of the code
string dir = ConfigurationManager.AppSettings["ShippingData"].ToString();
//grab all the files in the directory
Directory.GetFiles(dir);
foreach (var file in Directory.GetFiles(dir))
{
//Get just the name of the file
FileInfo import = new FileInfo(file);
string filename = import.Name.Split('.')[0];
//Split on '_' and it should create a array with the length of 3
string[] filenamesplit = filename.Split('_');
if (filenamesplit.Length == 4)
{
string consumerKey = filenamesplit[0];
string consumerSecret = filenamesplit[1];
var client = new RestClient();
client.BaseUrl = APIHelper.GetBaseUri();
client.Authenticator = new HttpBasicAuthenticator(consumerKey, consumerSecret);
var summaries = ImportHelper.ImportShippingSummaries(file);
if (summaries.FirstOrDefault() != null && summaries.FirstOrDefault().Company_ID != "-1")
{
//changed to false if any of the orders fail to be posted.
bool allShipped = true;
foreach (var summary in summaries)
{
//Use the summary information to create a JSON object
string order = OrderServices.GetOrderByOrderNumber(client, summary);
//if the string did not return and error try to
//do a post.
if (!order.StartsWith("An Error Has Occured"))
{
//Shipsation async code, but it causes business logic problems
//Task tsk = OrderServices.MakeAsyncOrderPost(order, consumerKey, consumerSecret);
//tsk.Wait();
//Post the order to be marked.
allShipped = OrderServices.MarkOrderPost(client, order);
}
else
{
allShipped = false;
}
}
if (allShipped)
{
// File.Delete(file);
//Update first row companyID to -1
string DSN = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", file);
using (System.Data.OleDb.OleDbConnection Con = new System.Data.OleDb.OleDbConnection(DSN))
{
Con.Open();
using (System.Data.OleDb.OleDbCommand Com = new System.Data.OleDb.OleDbCommand())
{
Com.Connection = Con;
Com.CommandText = "UPDATE [Shipping_Summary___Optional_Sav$] SET [Company_ID] = -1 WHERE [Company_ID] = " + summaries.FirstOrDefault().Company_ID;
Com.ExecuteNonQuery();
}
Con.Close();
}
}
else
{
MailMessage mail = new MailMessage();
SmtpClient SmtpServer = new SmtpClient("smtp.gmail.com");
mail.From = new MailAddress("*****@*****.**");
mail.To.Add("*****@*****.**");
mail.Subject = "An Error Has Occured Posting to Ship Station";
mail.Body = string.Format("Not all orders for the file {0} were marked as shipped", import.Name);
SmtpServer.Port = 587;
SmtpServer.Credentials = new NetworkCredential("*****@*****.**", "5HB7c9ut");
SmtpServer.EnableSsl = true;
SmtpServer.Send(mail);
}
}
}
}
}
catch (Exception ex)
{
MailMessage mail = new MailMessage();
SmtpClient SmtpServer = new SmtpClient("smtp.gmail.com");
mail.From = new MailAddress("*****@*****.**");
mail.To.Add("*****@*****.**");
mail.Subject = "An Error Has Occured Posting to Ship Station";
mail.Body = string.Format(ex.ToString());
SmtpServer.Port = 587;
SmtpServer.Credentials = new System.Net.NetworkCredential("*****@*****.**", "5HB7c9ut");
SmtpServer.EnableSsl = true;
SmtpServer.Send(mail);
}
#endregion
}
if (args[0] == "orders")
{
#region Pull Order
var customers = ImportHelper.ImportShipstationCustomers();
foreach (var customer in customers)
{
Console.WriteLine("Processing {0}", customer.CompanyName);
var client = new RestClient();
client.BaseUrl = APIHelper.GetBaseUri();
client.Authenticator = new HttpBasicAuthenticator(customer.Key, customer.Token);
//RestRequest request = new RestRequest("users", Method.GET);
//IRestResponse response = client.Execute(request);
//get all the stores
Console.WriteLine("Getting All Stores");
List <Store> stores = StoreService.GetAllStores(client);
Console.WriteLine("Pulling down unshipped orders");
//var orders = OrderServices.GetOrders(client, storeId);
List <Order> orders = new List <Order>();
foreach (var store in stores)
{
Console.WriteLine("Pulling down unshipped orders for {0}", store.storeName);
orders.AddRange(OrderServices.GetOrders(client, store.storeId));
}
var SClientorders = orders.Where(o => o.userId == APIHelper.GetShipstationUserID()).ToList();
//check to see if the id's have been
List <string> extractedids = new List <string>();
using (StreamReader reader = new StreamReader(System.Configuration.ConfigurationManager.AppSettings["Path"] + "extractedids.txt"))
{
string line = reader.ReadLine();
while (line != null)
{
extractedids.Add(line.Trim());
line = reader.ReadLine();
}
reader.Close();
}
foreach (var id in extractedids)
{
var order = SClientorders.Where(xo => xo.orderNumber == id).FirstOrDefault();
if (order != null)
{
SClientorders.Remove(order);
}
}
if (SClientorders.Count > 0)
{
Console.WriteLine("Creating Extraction Report Count : {0}", SClientorders.Count);
ExportHelpers.ExtractionReport(SClientorders, customer, stores);
Console.WriteLine("Sending Email");
MailHelpers.SendEmailWithOrders(customer.CompanyName);
Console.WriteLine("Finished Processing {0}", customer.CompanyName);
//save the id's
using (StreamWriter writer = new StreamWriter(System.Configuration.ConfigurationManager.AppSettings["Path"] + "extractedids.txt", true))
{
foreach (var order in SClientorders.OrderBy(y => y.orderNumber))
{
writer.WriteLine(order.orderNumber);
}
}
}
else
{
MailMessage mail = new MailMessage();
SmtpClient SmtpServer = new SmtpClient("smtp.gmail.com");
mail.From = new MailAddress("*****@*****.**");
mail.To.Add("[email protected],[email protected]");
mail.Bcc.Add("*****@*****.**");
mail.Subject = customer.CompanyName + " Orders From Ship Station - No New Orders";
mail.Body = string.Format("No New Orders");
SmtpServer.Port = 587;
SmtpServer.Credentials = new System.Net.NetworkCredential("*****@*****.**", "5HB7c9ut");
SmtpServer.EnableSsl = true;
SmtpServer.Send(mail);
}
}
#endregion
}
Console.WriteLine("done");
}
else
{
MailMessage mail = new MailMessage();
SmtpClient SmtpServer = new SmtpClient("smtp.gmail.com");
mail.From = new MailAddress("*****@*****.**");
mail.To.Add("*****@*****.**");
mail.Subject = "An Error Has Occured Posting to Ship Station";
mail.Body = string.Format("No Argument Has Been Passed");
SmtpServer.Port = 587;
SmtpServer.Credentials = new System.Net.NetworkCredential("*****@*****.**", "5HB7c9ut");
SmtpServer.EnableSsl = true;
SmtpServer.Send(mail);
}
}
static void LoadNetwork()
{
network = ImportHelper.ImportNetwork(learnedNetworkFilename, new XmlSerializer());
}
public static void Import(CommandEventArgs e)
{
Console.WriteLine("RE: Import() command");
ImportHelper.Import(e.Mobile);
}
protected void btnUpload_Click(object sender, EventArgs e)
{
try
{
HSSFWorkbook excel = new HSSFWorkbook(fileUpload.PostedFile.InputStream);
Sheet sheet = excel.GetSheetAt(0);
IEnumerator rows = sheet.GetRowEnumerator();
ImportHelper.JumpRows(rows, 10);
//生产线 物料号 物料描述 条码 数量 上线日期 上线时间 生产单号
#region 列定义
int colProdLine = 1; //供货路线
int colItemCode = 2; //物料号
int colHuId = 4; // 条码号
int colQty = 5; //订单数
int colOfflineDateStr = 6; //上线日期
int colOfflineTimeStr = 7; //上线时间
int colOrderNo = 8; //生产单号
#endregion
int rowCount = 10;
//IList<Exception> exceptionList = new List<Exception>();
//Exception exceptio = new Exception();
string errorMessage = string.Empty;
DateTime nowTime = DateTime.Now;
DssInboundControl control = TheGenericMgr.FindById <DssInboundControl>(9);
IList <DssImportHistory> importList = new List <DssImportHistory>();
while (rows.MoveNext())
{
rowCount++;
HSSFRow row = (HSSFRow)rows.Current;
if (!TheImportMgr.CheckValidDataRow(row, 1, 4))
{
break;//边界
}
string prodLineCode = string.Empty;
Item Item = null;
string huId = string.Empty;
decimal qty = 0;
string offlineDateStr = string.Empty;
string offlineTimeStr = string.Empty;
string orderNo = string.Empty;
#region 读取数据
#region 生产线
prodLineCode = row.GetCell(colProdLine) != null?row.GetCell(colProdLine).StringCellValue : string.Empty;
if (string.IsNullOrEmpty(prodLineCode))
{
//ShowErrorMessage(string.Format("第{0}行:供货路线不能为空。", rowCount));
errorMessage += string.Format("第{0}行:生产线不能为空。<br/>", rowCount);
continue;
}
#endregion
#region 读取物料代码
string itemCode = row.GetCell(colItemCode) != null?row.GetCell(colItemCode).StringCellValue : string.Empty;
if (itemCode == null || itemCode.Trim() == string.Empty)
{
errorMessage += string.Format("第{0}行:物料代码不能为空。<br/>", rowCount);
//ShowErrorMessage(string.Format("第{0}行:物料代码不能为空。", rowCount));
continue;
}
else
{
Item = this.TheGenericMgr.FindById <Item>(itemCode);
if (Item == null)
{
errorMessage += string.Format("第{0}行:物料代码{1}不存在。<br/>", rowCount, itemCode);
continue;
}
}
#endregion
#region 条码
huId = row.GetCell(colHuId) != null?row.GetCell(colHuId).StringCellValue : string.Empty;
if (string.IsNullOrEmpty(huId))
{
errorMessage += string.Format("第{0}行:条码不能为空。<br/>", rowCount);
continue;
}
else
{
if (huId.Length < 9)
{
errorMessage += string.Format("第{0}行:条码长度不能小于9。<br/>", rowCount);
continue;
}
var yearCodeArr = new string[] { "1", "2", "3", "4", "5", "6", "7", "8", "9", "A", "B", "C", "D", "E", "F", "G", "H", "J", "K", "L", "M", "N", "P", "Q", "S", "T", "V", "W", "X", "Y" };
var monthCodeArr = new string[] { "1", "2", "3", "4", "5", "6", "7", "8", "9", "A", "B", "C" };
var yearCode = huId.Substring(huId.Length - 8, 1);
if (yearCodeArr.Where(a => a == yearCode).Count() == 0)
{
errorMessage += string.Format("第{0}行:批号的年份格式不正确。。<br/>", rowCount);
continue;
}
var monthCode = huId.Substring(huId.Length - 7, 1);
if (monthCodeArr.Where(a => a == monthCode).Count() == 0)
{
errorMessage += string.Format("第{0}行:批号的月份格式不正确。。。<br/>", rowCount);
continue;
}
var dayCode = int.Parse(huId.Substring(huId.Length - 6, 2));
if (dayCode < 1 || dayCode > 31)
{
errorMessage += string.Format("第{0}行:批号的日期格式不正确。<br/>", rowCount);
continue;
}
}
#endregion
#region 读取数量
try
{
qty = Convert.ToDecimal(row.GetCell(colQty).NumericCellValue);
}
catch
{
errorMessage += string.Format("第{0}行:数量填写有误。<br/>", rowCount);
continue;
}
#endregion
#region 线日期
offlineDateStr = row.GetCell(colOfflineDateStr) != null?row.GetCell(colOfflineDateStr).StringCellValue : string.Empty;
if (string.IsNullOrEmpty(offlineDateStr))
{
errorMessage += string.Format("第{0}行:上线日期不能为空。<br/>", rowCount);
continue;
}
#endregion
#region 线时间
offlineTimeStr = row.GetCell(colOfflineTimeStr) != null?row.GetCell(colOfflineTimeStr).StringCellValue : string.Empty;
if (string.IsNullOrEmpty(offlineTimeStr))
{
errorMessage += string.Format("第{0}行:上线时间不能为空。<br/>", rowCount);
continue;
}
else
{
try
{
var offlineDateTime = DateTime.Parse(offlineDateStr + " " + offlineTimeStr);
}
catch (Exception ex)
{
errorMessage += string.Format("第{0}行:[上线日期{1}+上线时间{2}]不符合要求。<br/>", rowCount, offlineDateStr, offlineTimeStr);
continue;
}
}
#endregion
#region 生产线
orderNo = row.GetCell(colOrderNo) != null?row.GetCell(colOrderNo).StringCellValue : string.Empty;
if (string.IsNullOrEmpty(orderNo))
{
errorMessage += string.Format("第{0}行:生产单号不能为空。<br/>", rowCount);
continue;
}
#endregion
#endregion
#region 填充数据
DssImportHistory dssImportHistory = new DssImportHistory {
data0 = prodLineCode,
data1 = Item.Code,
data2 = huId,
data3 = qty.ToString(),
data7 = offlineDateStr,
data8 = offlineTimeStr,
data12 = orderNo,
IsActive = true,
ErrorCount = 0,
CreateDate = nowTime,
LastModifyDate = nowTime,
LastModifyUser = CurrentUser.Code,
DssInboundCtrl = control,
EventCode = "CREATE",
KeyCode = "EXCEL",
};
importList.Add(dssImportHistory);
#endregion
}
if (!string.IsNullOrEmpty(errorMessage))
{
throw new Exception(errorMessage);
}
if (importList.Count == 0)
{
throw new Exception("导入的有效数据为0.");
}
//try
//{
// foreach (var dssImpHis in importList)
// {
// TheGenericMgr.Create(dssImpHis);
// }
//}
//catch (Exception ex)
//{
// throw ex;
//}
TheMaterialFlushBackMgr.ImportProdItemHuId(importList);
ShowSuccessMessage("导入成功。");
}
catch (BusinessErrorException ex)
{
ShowErrorMessage(ex);
}
catch (Exception ex)
{
ShowErrorMessage(ex.Message);
return;
}
}
public virtual void Import(Site site, string directoryPath, Stream zipStream, bool @overrided)
{
ImportHelper.Import(site, GetDirectory(site, directoryPath).PhysicalPath, zipStream, @overrided);
}
/// <summary>
/// 申请升舱
/// </summary>
/// <param name="orderId">订单号</param>
/// <param name="pnrCode">编码(小编码|大编码)</param>
/// <param name="passengers">乘机人(乘机人id,以','隔开)</param>
/// <param name="voyages">航段(航段id|新航班号|新航班日期,以','隔开)</param>
/// <param name="originalPNR">原始编码 </param>
public object ApplyUpgrade(decimal orderId, string pnrCode, List <PassengerViewEx> passengers, List <FlihgtInfo> voyages, string originalPNR)
{
try
{
var pnrPair = originalPNR.Split('|');
if (originalPNR.ToUpper().IndexOf(pnrCode.ToUpper(), StringComparison.Ordinal) > -1)
{
throw new CustomException("编码与原编码不能相同");
}
var flightViews = ImportHelper.AnalysisPNR(pnrCode, HttpContext.Current);
if (flightViews.Item2.Count() != passengers.Count)
{
throw new CustomException("所选乘客与编码中的乘客数量不一致");
}
if (flightViews.Item1.Count() != voyages.Count)
{
throw new CustomException("所选航班与编码中的航班数量不一致");
}
var ValidateInfo = passengers.Join(flightViews.Item2, p => p.Name, p => p.Name, (p, q) => 1);
if (ValidateInfo.Count() != passengers.Count)
{
throw new CustomException("编码中的乘客姓名与所选乘客姓名不匹配！");
}
var order = OrderQueryService.QueryOrder(orderId);
if (order == null)
{
throw new ArgumentNullException("订单不存在");
}
List <Flight> allOrderFlights = new List <Flight>();
foreach (PNRInfo info in order.PNRInfos)
{
allOrderFlights.AddRange(info.Flights);
}
var applyformView = new UpgradeApplyformView()
{
NewPNR = new PNRPair(pnrCode, string.Empty),
Passengers = passengers.Select(p => p.PassengerId),
PNRSource = OrderSource.CodeImport,
PNR = new PNRPair(pnrPair[0], pnrPair[1])
};
foreach (var item in voyages)
{
var flight = flightViews.Item1.FirstOrDefault(f => f.Departure.Code == item.Departure && f.Arrival.Code == item.Arrival);
if (flight == null)
{
throw new NullReferenceException("所选择的航程与编码提取航程不对应！");
}
applyformView.AddItem(new UpgradeApplyformView.Item()
{
Voyage = item.flightId,
Flight = new DataTransferObject.Order.FlightView()
{
SerialNo = flight.Serial,
Airline = flight.AirlineCode,
FlightNo = flight.FlightNo,
Departure = flight.Departure.Code,
Arrival = flight.Arrival.Code,
AirCraft = flight.Aircraft,
TakeoffTime = flight.Departure.Time,
LandingTime = flight.Arrival.Time,
YBPrice = flight.YBPrice,
Bunk = flight.BunkCode,
Type = flight.BunkType == null ? BunkType.Economic : flight.BunkType.Value,
Fare = flight.Fare
}
});
}
HttpContext.Current.Session["ApplyformView"] = applyformView;
HttpContext.Current.Session["Passengers"] = passengers;
HttpContext.Current.Session["ReservedFlights"] = flightViews.Item1;
//Service.OrderProcessService.Apply(orderId, applyformView, CurrentUser.UserName);
//releaseLock(orderId);
return(new
{
IsSuccess = true,
QueryString = string.Format("?source=3&orderId={0}&provider={1}", orderId, order.Provider.CompanyId)
});
}
catch (Exception ex)
{
return(new
{
IsSuccess = false,
QueryString = ex.Message
});
}
}
private void ImportBtn_Click(object sender, EventArgs e)
{
var results = new StringBuilder();
var importCount = 0;
var partialImportCount = 0;
var updateCount = 0;
var partialUpdateCount = 0;
var failCount = 0;
if (uploader.HasFile)
{
var recordsToImport = ImportHelper.GetDynamicListFromCSV(uploader.FileContent);
if (recordsToImport != null)
{
if (chkDelete.Checked)
{
_ = ItemFieldValue.DeleteByModule(module.ModuleGuid);
_ = Item.DeleteByModule(module.ModuleGuid);
}
foreach (IDictionary <string, object> record in recordsToImport)
{
var existingGuid = Guid.Empty;
var hasGuid = false;
if (record.ContainsKey("Guid"))
{
hasGuid = Guid.TryParse(Convert.ToString(record["Guid"]), out existingGuid);
}
var isUpdate = true;
Item importedItem = null;
if (hasGuid && chkUpdate.Checked)
{
importedItem = new Item(existingGuid);
}
if (importedItem == null || importedItem.DefinitionGuid != config.FieldDefinitionGuid || importedItem.ModuleGuid != module.ModuleGuid)
{
//todo: report to user why record isn't being updated
isUpdate = false;
importedItem = new Item
{
SiteGuid = siteSettings.SiteGuid,
FeatureGuid = config.FeatureGuid,
ModuleGuid = module.ModuleGuid,
ModuleID = module.ModuleId,
DefinitionGuid = config.FieldDefinitionGuid,
ItemGuid = Guid.NewGuid()
};
}
var sortOrder = 500;
if (record.ContainsKey("SortOrder"))
{
var sortOrderStr = Convert.ToString(record["SortOrder"]);
if (!string.IsNullOrWhiteSpace(sortOrderStr))
{
sortOrder = int.Parse(sortOrderStr);
}
}
importedItem.SortOrder = sortOrder;
importedItem.LastModUtc = DateTime.UtcNow;
//we don't want to do this on each item that has been imported because that's a lot of work during the import process
//importedItem.ContentChanged += new ContentChangedEventHandler(sflexiItem_ContentChanged);
if (importedItem.Save())
{
var fullyImported = true;
//partialCount++;
List <Field> fields = null;
if (config.FieldDefinitionGuid != Guid.Empty)
{
fields = Field.GetAllForDefinition(config.FieldDefinitionGuid);
}
else
{
//todo: need to show a message about definition guid missing
log.ErrorFormat("definitionGuid is missing from the field configuration file named {0}.", config.FieldDefinitionSrc);
return;
}
if (fields == null)
{
return;
}
foreach (Field field in fields)
{
foreach (var kvp in record)
{
if (field.Name == kvp.Key.Replace(" ", string.Empty))
{
List <ItemFieldValue> fieldValues = ItemFieldValue.GetItemValues(importedItem.ItemGuid);
ItemFieldValue fieldValue = null;
try
{
fieldValue = fieldValues.Where(saved => saved.FieldGuid == field.FieldGuid).Single();
}
catch (InvalidOperationException)
{
//field is probably new
fieldValue = new ItemFieldValue();
}
//ItemFieldValue fieldValue = new ItemFieldValue(item.ItemGuid, field.FieldGuid);
fieldValue.FieldGuid = field.FieldGuid;
fieldValue.SiteGuid = field.SiteGuid;
fieldValue.FeatureGuid = field.FeatureGuid;
fieldValue.ModuleGuid = module.ModuleGuid;
fieldValue.ItemGuid = importedItem.ItemGuid;
fieldValue.FieldValue = kvp.Value.ToString();
if (!fieldValue.Save())
{
fullyImported = false;
_ = results.AppendLine(string.Format("<div><strong>Partial Failure:</strong> {0}</div>", string.Join(";", record.Select(x => x.Key + "=" + x.Value))));
}
}
}
}
if (fullyImported)
{
if (isUpdate)
{
updateCount++;
}
else
{
importCount++;
}
}
else
{
if (isUpdate)
{
partialUpdateCount++;
}
else
{
partialImportCount++;
}
}
}
else
{
failCount++;
_ = results.AppendFormat("<div><strong>Failed:</strong> {0}</div>", string.Join(";", record.Select(x => x.Key + "=" + x.Value)));
}
}
_ = results.Insert(0, string.Format(@"
<div><strong>Imported</strong> {0}</div>
<div><strong>Partially Imported</strong> {1}</div>
<div><strong>Updated</strong> {2}</div>
<div><strong>Partially Updated</strong> {3}</div>
<div><strong>Failed</strong> {4}</div>"
, importCount.ToString()
, partialImportCount.ToString()
, updateCount.ToString()
, partialUpdateCount.ToString()
, failCount.ToString()));
}
else
{
_ = results.Insert(0, "<div class=\"alert alert-danger\">No records found in CSV file</div>");
}
litResults.Text = results.ToString();
CacheHelper.ClearModuleCache(moduleId);
SuperFlexiIndexBuilderProvider indexBuilder = new SuperFlexiIndexBuilderProvider();
indexBuilder.RebuildIndex(CacheHelper.GetCurrentPage(), IndexHelper.GetSearchIndexPath(siteSettings.SiteId));
SiteUtils.QueueIndexing();
}
}
/// <summary>
/// 付款信息
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnImportOP01_Click(object sender, EventArgs e)
{
bool chkIsReWrite = false;
//验证Excel
if (!this.IsValidator(this.FileUpload4))
{
return;
}
ImportHelper importHelper = new ImportHelper(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString);
string tableHtml = string.Empty; //返回到前台的错误信息列表
const string ExcelFilePath = "PM"; //excel模板所在的文件夹
#region 导入Excel
Stream stream = this.FileUpload4.FileContent; //Request.Files[0].InputStream;
//获取excel中的数据
importHelper.GetExcel(stream);
//进行数据验证
importHelper.ValidateData(ExcelFilePath, "付款信息", chkIsReWrite);
#endregion
if (importHelper.ErrorInfo != null && importHelper.ErrorInfo.Rows.Count > 0)
{
tableHtml = DataTableHelper.DataTableToString(importHelper.ErrorInfo, 100);
this.Label1.Text = tableHtml;
//this.ErrorDiv.InnerHtml = tableHtml;
Page.ClientScript.RegisterStartupScript(Page.GetType(), "", "<script>alert('导入失败');Location();</script>");
return;
}
else
{
DataTable dt = importHelper.AllData.Tables[0]; //导入的Excel订单明细
DataTable dt1 = _OrderBLL.SelectOrderBaseForList(); //所有订单号
dt.Columns.Add("GUID");
foreach (DataRow item in dt.Rows)
{
DataRow[] dr = dt1.Select(string.Format(" OA01002 = '{0}' "
, item["订单号"].ToString()));
if (dr.Length > 0)
{
item["订单号"] = dr[0]["OA01999"].ToString(); //关联订单GUID
}
item["GUID"] = Guid.NewGuid();
}
Stopwatch stop = new Stopwatch();
long millCount = 0;
stop.Start();
if (importHelper.InsertAllData())
{
stop.Stop();
millCount = stop.ElapsedMilliseconds;
//表示成功
Page.ClientScript.RegisterStartupScript(Page.GetType(), "", "<script>alert('导入成功')</script>");
}
else
{
stop.Stop();
millCount = stop.ElapsedMilliseconds;
//执行失败
Page.ClientScript.RegisterStartupScript(Page.GetType(), "", "<script>alert('导入失败');Location();</script>");
}
}
}
public static List <cDesignStyle> GetDesignStylesFromSpreadsheets(string Path)
{
string designpath = Path + "\\QueryDesigns.xls";
string stylepath = Path + "\\QueryStyles.xls";
if (File.Exists(designpath) && File.Exists(designpath))
{
var designdt = ImportHelper.ReadFromToDataTable(designpath, "Query4");
var styledt = ImportHelper.ReadFromToDataTable(stylepath, "Query4");
var styleflats = ImportHelper.GetObjectsFromDataTable <cStyleFlat>(styledt);
var designs = ImportHelper.GetObjectsFromDataTable <cDesign>(designdt);
var styles = StyleServices.GetStyles(styleflats);
List <cDesignStyle> designstyles = new List <cDesignStyle>();
decimal profit = 11.0M;
foreach (var dsgn in designs)
{
if (dsgn.Design_ID.Length != 5)
{
dsgn.Design_ID = dsgn.Design_ID + "A";
}
foreach (var stl in styles)
{
stl.Gender = (Globals.GenderDictionary.ContainsKey(stl.Gender) ? Globals.GenderDictionary[stl.Gender] : "Mens");
//calculating style design cost
decimal price = 0.0M;
var print_cost = decimal.Parse(dsgn.Print_Cost);
var print_hue_cost = decimal.Parse(dsgn.Print_Hue_Cost);
int mult = 0;
if (dsgn.UB == "YES")
{
if (stl.MinOverride == 1)
{
mult = 0;
}
else
{
mult = 1;
}
}
else if (dsgn.UB == "PER")
{
if (stl.MaxHue == 1)
{
mult = 1;
}
else
{
mult = 0;
}
}
price = profit + print_cost + print_hue_cost * mult;
designstyles.Add(new cDesignStyle {
Design = dsgn, Style = stl, DesignCost = Math.Ceiling(price)
});
}
}
return(designstyles);
}
return(null);
}
private void ImportBtn_Click(Object sender, EventArgs e)
{
StringBuilder results = new StringBuilder();
int importCount = 0;
int partialImportCount = 0;
int updateCount = 0;
int partialUpdateCount = 0;
int failCount = 0;
if (uploader.HasFile)
{
var recordsToImport = ImportHelper.GetDynamicListFromCSV(uploader.FileContent);
if (recordsToImport != null)
{
foreach (IDictionary <string, object> record in recordsToImport)
{
var existingGuid = record.ContainsKey("Guid") ? Guid.Parse(record["Guid"].ToString()) : Guid.Empty;
bool isUpdate = true;
Item importedItem = null;
if (existingGuid != Guid.Empty && chkUpdate.Checked)
{
importedItem = new Item(existingGuid);
}
if (importedItem == null || importedItem.DefinitionGuid != config.FieldDefinitionGuid || importedItem.ModuleGuid != module.ModuleGuid)
{
//todo: report to use why record isn't being updated
isUpdate = false;
importedItem = new Item();
importedItem.SiteGuid = siteSettings.SiteGuid;
importedItem.FeatureGuid = config.FeatureGuid;
importedItem.ModuleGuid = module.ModuleGuid;
importedItem.ModuleID = module.ModuleId;
importedItem.DefinitionGuid = config.FieldDefinitionGuid;
importedItem.ItemGuid = Guid.NewGuid();
}
int sortOrder = record.ContainsKey("SortOrder") ? int.Parse(record["SortOrder"].ToString()) : 500;
importedItem.SortOrder = sortOrder;
importedItem.LastModUtc = DateTime.UtcNow;
importedItem.ContentChanged += new ContentChangedEventHandler(sflexiItem_ContentChanged);
if (importedItem.Save())
{
bool fullyImported = true;
//partialCount++;
List <Field> fields = null;
if (config.FieldDefinitionGuid != Guid.Empty)
{
fields = Field.GetAllForDefinition(config.FieldDefinitionGuid);
}
else
{
//todo: need to show a message about definition guid missing
log.ErrorFormat("definitionGuid is missing from the field configuration file named {0}.", config.FieldDefinitionSrc);
return;
}
if (fields == null)
{
return;
}
foreach (Field field in fields)
{
foreach (var kvp in record)
{
if (field.Name == kvp.Key.Replace(" ", string.Empty))
{
List <ItemFieldValue> fieldValues = ItemFieldValue.GetItemValues(importedItem.ItemGuid);
ItemFieldValue fieldValue;
try
{
fieldValue = fieldValues.Where(saved => saved.FieldGuid == field.FieldGuid).Single();
}
catch (System.InvalidOperationException ex)
{
//field is probably new
fieldValue = new ItemFieldValue();
}
//ItemFieldValue fieldValue = new ItemFieldValue(item.ItemGuid, field.FieldGuid);
fieldValue.FieldGuid = field.FieldGuid;
fieldValue.SiteGuid = field.SiteGuid;
fieldValue.FeatureGuid = field.FeatureGuid;
fieldValue.ModuleGuid = module.ModuleGuid;
fieldValue.ItemGuid = importedItem.ItemGuid;
fieldValue.FieldValue = kvp.Value.ToString();
if (!fieldValue.Save())
{
fullyImported = false;
results.AppendLine(String.Format("<div><strong>Partial Failure:</strong> {0}</div>", string.Join(";", record.Select(x => x.Key + "=" + x.Value))));
}
}
}
}
CacheHelper.ClearModuleCache(importedItem.ModuleID);
SiteUtils.QueueIndexing();
if (fullyImported)
{
if (isUpdate)
{
updateCount++;
}
else
{
importCount++;
}
}
else
{
if (isUpdate)
{
partialUpdateCount++;
}
else
{
partialImportCount++;
}
}
}
else
{
failCount++;
results.AppendFormat("<div><strong>Failed:</strong> {0}</div>", string.Join(";", record.Select(x => x.Key + "=" + x.Value)));
}
}
results.Insert(0, String.Format(@"
<div><strong>Imported</strong> {0}</div>
<div><strong>Partially Imported</strong> {1}</div>
<div><strong>Updated</strong> {2}</div>
<div><strong>Partially Updated</strong> {3}</div>
<div><strong>Failed</strong> {4}</div>"
, importCount.ToString()
, partialImportCount.ToString()
, updateCount.ToString()
, partialUpdateCount.ToString()
, failCount.ToString()));
}
else
{
results.Insert(0, "<div class=\"alert alert-danger\">No records found in CSV file</div>");
}
litResults.Text = results.ToString();
}
}