/// <summary>
/// 同步成交数据到CRMCustomerDeal表
/// (依赖Customer)
/// </summary>
/// <param name="deal"></param>
private void Sync2CRMCustomerDeal(string ContractNum)
{
//-------------- Delete & Insert CRMCustomerDeals Again -------------
this.CRMCustomerDeals.DeleteAllOnSubmit(this.CRMCustomerDeals.Where(t => t.ContractNum == ContractNum).ToList());
var DealList = vw_BillDeals.Where(t => t.ContractNum.Equals(ContractNum) && t.Price > 0).ToList();
foreach (var deal in DealList)
{
CRMCustomerDeal entity = new CRMCustomerDeal();
entity.ContractNum = deal.ContractNum;
entity.CreateDate = deal.CreateDate;
entity.CreateUserID = deal.CreateUserID;
entity.Currency = deal.Currency;
entity.CustID = (long)deal.CustID;
entity.DealDate = deal.DealDate;
entity.DealOwner = deal.DealOwner;
entity.ModifyDate = deal.ModifyDate;
entity.ModifyUserID = deal.ModifyUserID;
entity.ProdID = deal.ProdID;
entity.ProdSerialNum = deal.ProdSerialNum;
entity.Qty = deal.Qty;
entity.ReferenceNum = deal.ReferenceNum;
entity.TotalAmount = deal.Price;
entity.Unit = deal.Unit;
entity.UnitPrice = deal.Price;
entity.Status = 0;
entity.Remark = "";
this.CRMCustomerDeals.InsertOnSubmit(entity);
}
this.dataCtx.SubmitChanges();
}
public CRMCustomerDeal SaveDeal(CRMCustomerDeal entity)
{
if (this.dataCtx.Connection != null)
{
if (this.dataCtx.Connection.State == ConnectionState.Closed)
{
this.dataCtx.Connection.Open();
}
}
DbTransaction tran = this.dataCtx.Connection.BeginTransaction();
dataCtx.Transaction = tran;
try
{
var qry = from t in CRMCustomerDeals
where t.ID == entity.ID
select t;
var obj = qry.SingleOrDefault();
if (obj != null)
{
entity.CreateDate = obj.CreateDate;
entity.CreateUserID = obj.CreateUserID;
this.CopyEntity(obj, entity);
}
else
{
entity.CreateDate = DateTime.Now;
entity.CreateUserID = entity.ModifyUserID;
this.CRMCustomerDeals.InsertOnSubmit(entity);
}
this.dataCtx.SubmitChanges();
tran.Commit();
return(entity);
}
catch (Exception ex)
{
tran.Rollback();
throw ex;
}
finally
{
dataCtx.Connection.Close();
}
}
/// <summary>
/// A 内部订单号
/// B 客人姓名 title
/// C售价
/// D备注
/// E负责销售人员
/// F机票号
/// G Amadeus预订号
/// H 起飞日期
/// </summary>
/// <param name="filename"></param>
private void ExtractExcelData(string filename)
{
CRMCustomerDeal Deal;
FileStream file = new FileStream(filename, FileMode.Open);
HSSFWorkbook wb = new HSSFWorkbook(file);
HSSFSheet sht;
sht = wb.GetSheetAt(0); //取第一个sheet
//取行Excel的最大行数
int rowsCount = sht.PhysicalNumberOfRows;
//第1行是header,不是数据
for (int i = 1; i < rowsCount; i++)
{
//如果内部订单号是空,跳过
if (sht.GetStringCellValue(i, "A") == "")
{
continue;
}
Deal = new CRMCustomerDeal();
Deal.ContractNum = sht.GetStringCellValue(i, "A");
//如果单元格为空，GetCell方法会返回null,自己定义个扩展方法返回非空
string UserName = sht.GetStringCellValue(i, "E");
var objUser = usr.LoadByUserName(UserName);
if (objUser != null)
{
Deal.DealOwner = objUser.UserID;
}
//产品序列号,机票号
Deal.ProdSerialNum = sht.GetStringCellValue(i, "F");
//外部amadeus订单号
Deal.ReferenceNum = sht.GetStringCellValue(i, "G");
try
{
Deal.DealDate = Convert.ToDateTime(sht.GetDateCellValue(i, "H"));
}
catch { }
string CustName = sht.GetStringCellValue(i, "B");
long CustID = cus.GetCustIDByName(CustName);
if (CustID > 0)
{
Deal.CustID = CustID;
}
else
{
//新建一个客户
CRMCustomer entity = GetNewCustomer(CustName, Deal.DealOwner);
entity = cus.Save(entity, null);
Deal.CustID = entity.CustID;
}
Deal.ProdID = 1;//现暂固定为1=机票
Deal.Qty = 1;
Deal.Unit = "PCS";
try
{
Deal.UnitPrice = Convert.ToDecimal(sht.GetStringCellValue(i, "C"));
//Deal.UnitPrice = Convert.ToDecimal(sht.GetStringCellValue(i, "C"));
}
catch (Exception)
{
Deal.UnitPrice = 0;
}
Deal.TotalAmount = Deal.UnitPrice;
Deal.Currency = "EUR";
Deal.Remark = sht.GetStringCellValue(i, "D");
//现在CANX是取消，是放在客户名字里，但是否应该分开单列一列？
if (CustName.Contains("(CANX)"))
{
Deal.Status = 1; //取消
}
else
{
Deal.Status = 0; //正常
}
Deal.CreateDate = DateTime.Now;
Deal.CreateUserID = base.LoginUserID;
Deal.ModifyDate = DateTime.Now;
Deal.ModifyUserID = base.LoginUserID;
try
{
cus.SaveDeal(Deal);
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message + Deal.ContractNum + " ");
this.ShowMessage("Please check " + Deal.ContractNum + " " + ex.Message);
}
}
file.Close();
}
//获取输入数据
private CRMCustomerDeal GetSaveEntity()
{
var entity = new CRMCustomerDeal();
entity.ID = long.Parse(hidID.Value);
if (string.IsNullOrEmpty(Request["CustID"]) == false)
entity.CustID = int.Parse(Request["CustID"]);
if (string.IsNullOrEmpty(txtBrand.Text.Trim()) == false)
entity.Brand = txtBrand.Text.Trim();
if (string.IsNullOrEmpty(txtContractNum.Text.Trim()) == false)
entity.ContractNum = txtContractNum.Text.Trim();
if (string.IsNullOrEmpty(txtDealDate.Text.Trim()) == false)
entity.DealDate = DateTime.Parse(txtDealDate.Text);
if (string.IsNullOrEmpty(txtPayTerm.Text.Trim()) == false)
entity.PayTerm = txtPayTerm.Text.Trim();
if (string.IsNullOrEmpty(txtQty.Text.Trim()) == false)
entity.QtyDesc = txtQty.Text;
if (string.IsNullOrEmpty(txtUnitPrice.Text.Trim()) == false)
entity.UnitPriceDesc = txtUnitPrice.Text;
try
{
entity.TotalAmount = entity.UnitPrice * entity.Qty;
}
catch(Exception){}
if (string.IsNullOrEmpty(ddlUnit.Text.Trim()) == false)
entity.Unit = ddlUnit.Text.Trim();
if (string.IsNullOrEmpty(ddlCurrency.Text.Trim()) == false)
entity.Currency = ddlCurrency.Text.Trim();
if (string.IsNullOrEmpty(ddlProduct.Text.Trim()) == false)
entity.ProdID = int.Parse(ddlProduct.Text.Trim());
if (string.IsNullOrEmpty(txtShipment.Text.Trim()) == false)
entity.Shipment =txtShipment.Text.Trim();
entity.StockCategory = txtStockCat.Text.Trim();
entity.Status = int.Parse(RadStatus.SelectedValue);
entity.Remark = txtRemark.Text.Trim();
//entity.ReferenceNum = txtReferenceNum.Text.Trim();
//entity.ProdSerialNum = txtProdSerialNum.Text.Trim();
entity.DealOwner = long.Parse(txtCustOwnerID.SelectedValue);
entity.ModifyDate = DateTime.Now;
entity.ModifyUserID = base.LoginUserID;
return entity;
}
public CRMCustomerDeal SaveDeal(CRMCustomerDeal entity)
{
if (this.dataCtx.Connection != null)
if (this.dataCtx.Connection.State == ConnectionState.Closed)
this.dataCtx.Connection.Open();
DbTransaction tran = this.dataCtx.Connection.BeginTransaction();
dataCtx.Transaction = tran;
try
{
var qry = from t in CRMCustomerDeals
where t.ID == entity.ID
select t;
var obj = qry.SingleOrDefault();
if (obj != null)
{
entity.CreateDate = obj.CreateDate;
entity.CreateUserID = obj.CreateUserID;
this.CopyEntity(obj, entity);
}
else
{
entity.CreateDate = DateTime.Now;
entity.CreateUserID = entity.ModifyUserID;
this.CRMCustomerDeals.InsertOnSubmit(entity);
}
this.dataCtx.SubmitChanges();
tran.Commit();
return entity;
}
catch (Exception ex)
{
tran.Rollback();
throw ex;
}
finally
{
dataCtx.Connection.Close();
}
}
/// <summary>
/// A 内部订单号
/// B 客人姓名 title
/// C售价
/// D备注
/// E负责销售人员
/// F机票号
/// G Amadeus预订号
/// H 起飞日期
/// </summary>
/// <param name="filename"></param>
private void ExtractExcelData(string filename)
{
CRMCustomerDeal Deal;
FileStream file = new FileStream(filename, FileMode.Open);
HSSFWorkbook wb = new HSSFWorkbook(file);
HSSFSheet sht;
sht = wb.GetSheetAt(0); //取第一个sheet
//取行Excel的最大行数
int rowsCount = sht.PhysicalNumberOfRows;
//第1行是header,不是数据
for (int i = 1; i < rowsCount; i++)
{
//如果内部订单号是空,跳过
if (sht.GetStringCellValue(i,"A") == "")
continue;
Deal = new CRMCustomerDeal();
Deal.ContractNum = sht.GetStringCellValue(i, "A");
//如果单元格为空，GetCell方法会返回null,自己定义个扩展方法返回非空
string UserName = sht.GetStringCellValue(i, "E");
var objUser = usr.LoadByUserName(UserName);
if (objUser != null)
Deal.DealOwner = objUser.UserID;
//产品序列号,机票号
Deal.ProdSerialNum = sht.GetStringCellValue(i, "F");
//外部amadeus订单号
Deal.ReferenceNum = sht.GetStringCellValue(i, "G");
try
{
Deal.DealDate = Convert.ToDateTime(sht.GetDateCellValue(i, "H"));
}
catch { }
string CustName = sht.GetStringCellValue(i, "B");
long CustID = cus.GetCustIDByName(CustName);
if (CustID > 0)
Deal.CustID = CustID;
else
{
//新建一个客户
CRMCustomer entity = GetNewCustomer(CustName, Deal.DealOwner);
entity = cus.Save(entity, null);
Deal.CustID = entity.CustID;
}
Deal.ProdID = 1;//现暂固定为1=机票
Deal.Qty = 1;
Deal.Unit = "PCS";
try
{
Deal.UnitPrice = Convert.ToDecimal(sht.GetStringCellValue(i, "C"));
//Deal.UnitPrice = Convert.ToDecimal(sht.GetStringCellValue(i, "C"));
}
catch (Exception)
{
Deal.UnitPrice = 0;
}
Deal.TotalAmount = Deal.UnitPrice;
Deal.Currency = "EUR";
Deal.Remark = sht.GetStringCellValue(i, "D");
//现在CANX是取消，是放在客户名字里，但是否应该分开单列一列？
if (CustName.Contains("(CANX)"))
Deal.Status = 1; //取消
else
Deal.Status = 0; //正常
Deal.CreateDate = DateTime.Now;
Deal.CreateUserID = base.LoginUserID;
Deal.ModifyDate = DateTime.Now;
Deal.ModifyUserID = base.LoginUserID;
try
{
cus.SaveDeal(Deal);
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message + Deal.ContractNum + " ");
this.ShowMessage("Please check " + Deal.ContractNum + " " + ex.Message);
}
}
file.Close();
}
/// <summary>
/// 同步成交数据到CRMCustomerDeal表
/// (依赖Customer)
/// 在使用LINQ中更新数据或删除数据,总是出现找不到行或行已更改，最后发现每行数据中如果有值为NULL的时候，就会出现这个错误
/// 原来是LinqToSql的数据实体对象在进行更新时会进行字段检查.
/// </summary>
/// <param name="deal"></param>
private void Sync2CRMCustomerDeal(string ContractNum)
{
//-------------- Delete & Insert CRMCustomerDeals Again -------------
var OldDealList = this.CRMCustomerDeals.Where(t => t.ContractNum == ContractNum).ToList();
this.CRMCustomerDeals.DeleteAllOnSubmit(OldDealList);
this.dataCtx.SubmitChanges();
var DealList = vw_BillDeals.Where(t => t.ContractNum.Equals(ContractNum) && t.Price > 0).ToList();
foreach (var deal in DealList)
{
CRMCustomerDeal entity = new CRMCustomerDeal();
entity.ContractNum = deal.ContractNum;
entity.CreateDate = deal.CreateDate;
entity.CreateUserID = deal.CreateUserID;
entity.Currency = deal.Currency;
entity.CustID = (long)deal.CustID;
entity.DealDate = deal.DealDate;
entity.DealOwner = deal.DealOwner;
entity.ModifyDate = deal.ModifyDate;
entity.ModifyUserID = deal.ModifyUserID;
entity.ProdID = deal.ProdID;
entity.ProdSerialNum = deal.ProdSerialNum;
entity.Qty = deal.Qty;
entity.ReferenceNum = deal.ReferenceNum;
entity.TotalAmount = deal.Price;
entity.Unit = deal.Unit;
entity.UnitPrice = deal.Price;
entity.Status = 0;
entity.Remark = "";
entity.Brand = "";
entity.PayTerm = "";
entity.Shipment = "";
entity.StockCategory = "";
entity.UnitPriceDesc = deal.Price.ToString();
entity.QtyDesc = deal.Qty.ToString();
this.CRMCustomerDeals.InsertOnSubmit(entity);
}
this.dataCtx.SubmitChanges();
}
//获取输入数据
private CRMCustomerDeal GetSaveEntity()
{
var entity = new CRMCustomerDeal();
entity.ID = long.Parse(hidID.Value);
if (string.IsNullOrEmpty(Request["CustID"]) == false)
{
entity.CustID = int.Parse(Request["CustID"]);
}
entity.Brand = "";
if (string.IsNullOrEmpty(txtContractNum.Text.Trim()) == false)
{
entity.ContractNum = txtContractNum.Text.Trim();
}
if (string.IsNullOrEmpty(txtDealDate.Text.Trim()) == false)
{
entity.DealDate = DateTime.Parse(txtDealDate.Text);
}
if (string.IsNullOrEmpty(txtPayTerm.Text.Trim()) == false)
{
entity.PayTerm = txtPayTerm.Text.Trim();
}
if (string.IsNullOrEmpty(txtQty.Text.Trim()) == false)
{
entity.Qty = decimal.Parse(txtQty.Text);
}
if (string.IsNullOrEmpty(txtUnitPrice.Text.Trim()) == false)
{
entity.UnitPrice = decimal.Parse(txtUnitPrice.Text);
}
entity.TotalAmount = entity.UnitPrice * entity.Qty;
if (string.IsNullOrEmpty(ddlUnit.Text.Trim()) == false)
{
entity.Unit = ddlUnit.Text.Trim();
}
if (string.IsNullOrEmpty(ddlCurrency.Text.Trim()) == false)
{
entity.Currency = ddlCurrency.Text.Trim();
}
if (string.IsNullOrEmpty(ddlProduct.Text.Trim()) == false)
{
entity.ProdID = int.Parse(ddlProduct.Text.Trim());
}
entity.Shipment = "";
entity.StockCategory = "";
entity.Status = int.Parse(RadStatus.SelectedValue);
entity.Remark = txtRemark.Text.Trim();
entity.ReferenceNum = txtReferenceNum.Text.Trim();
entity.ProdSerialNum = txtProdSerialNum.Text.Trim();
entity.DealOwner = long.Parse(txtCustOwnerID.SelectedValue);
entity.ModifyDate = DateTime.Now;
entity.ModifyUserID = base.LoginUserID;
return(entity);
}
