//这个Tuple类型数组的amountlist中依次存储 int SchedulerID, int TugID, float Amount, float Hours
//一个账单调用一次
public void AmountSumAdd_Update(int CustomerID, int CustomerShipID, int BillingID, DateTime BillingDate, Tuple<int, int, float, float>[] amountlist)
{
try
{
TugDataEntities db = new TugDataEntities();
//先删除
System.Linq.Expressions.Expression<Func<AmountSum, bool>> exp = u => u.BillingID == BillingID;
var entitys = db.AmountSum.Where(exp);
entitys.ToList().ForEach(entity => db.Entry(entity).State = System.Data.Entity.EntityState.Deleted); //不加这句也可以
db.AmountSum.RemoveRange(entitys);
db.SaveChanges();
//新增
foreach(var obj in amountlist)
{
TugDataModel.AmountSum newobj = new AmountSum();
newobj.CustomerID = CustomerID;
newobj.CustomerShipID = CustomerShipID;
newobj.BillingID = BillingID;
newobj.BillingDateTime = BillingDate;
newobj.SchedulerID = obj.Item1;//SchedulerID;
newobj.TugID = obj.Item2;;
newobj.Amount = obj.Item3; ;
newobj.Hours = obj.Item4; ;
newobj.OwnerID = -1;
newobj.CreateDate = newobj.LastUpDate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); ;//.ToString("yyyy-MM-dd");
newobj.UserID = Session.GetDataFromSession<int>("userid");
newobj.UserDefinedCol1 = "";
newobj.UserDefinedCol2 = "";
newobj.UserDefinedCol3 = "";
newobj.UserDefinedCol4 = "";
//if (Request.Form["UserDefinedCol5"].Trim() != "")
// newobj.UserDefinedCol5 = Convert.ToDouble(Request.Form["UserDefinedCol5"].Trim());
//if (Request.Form["UserDefinedCol6"].Trim() != "")
// newobj.UserDefinedCol6 = Util.toint(Request.Form["UserDefinedCol6"].Trim());
//if (Request.Form["UserDefinedCol7"].Trim() != "")
// newobj.UserDefinedCol7 = Util.toint(Request.Form["UserDefinedCol7"].Trim());
//if (Request.Form["UserDefinedCol8"].Trim() != "")
// newobj.UserDefinedCol8 = Util.toint(Request.Form["UserDefinedCol8"].Trim());
newobj.UserDefinedCol9 = "";
newobj.UserDefinedCol10 = "";
newobj = db.AmountSum.Add(newobj);
}
db.SaveChanges();
//var ret = new { code = Resources.Common.SUCCESS_CODE, message = Resources.Common.SUCCESS_MESSAGE };
//Response.Write(@Resources.Common.SUCCESS_MESSAGE);
//return Json(ret);
}
catch (Exception ex)
{
throw ex;
}
}
public ActionResult EditInvoice2(int billingId, int billingTemplateId, int billingTypeId, int timeTypeId,
string jobNo, string remark, string billing_code, double discount, double amount, string month, string date, int customer_ship_length,
int customer_ship_teus, string jsonArrayItems, string isShowShipLengthRule,
float? ratio1, float? ratio2, float? ratio3, float? ratio4, float? ratio5, float? ratio6, float? ratio7, float? minTime,
string isShowShipTEUSRule, string jsonArraySummaryItems)
{
this.Internationalization();
try
{
TugDataEntities db = new TugDataEntities();
{
Billing oldBilling = db.Billing.FirstOrDefault(u => u.IDX == billingId);
Billing tmp = db.Billing.FirstOrDefault(u => u.BillingCode.Trim() == billing_code.Trim());
if (tmp != null)
{
if (tmp.IDX != oldBilling.IDX)
{
return Json(new { code = Resources.Common.FAIL_CODE, message = Resources.Common.FAIL_MESSAGE }, JsonRequestBehavior.AllowGet);
}
}
if (oldBilling != null)
{
oldBilling.BillingTemplateID = billingTemplateId;
oldBilling.BillingTypeID = billingTypeId;
oldBilling.TimeTypeID = timeTypeId;
oldBilling.Discount = discount;
oldBilling.Amount = amount;
oldBilling.BillingCode = billing_code.Trim();
oldBilling.JobNo = jobNo;
if (ratio1 != null)
oldBilling.Ratio1 = (double?)Math.Round((double)ratio1, 2);
if (ratio2 != null)
oldBilling.Ratio2 = (double?)Math.Round((double)ratio2, 2);
if (ratio3 != null)
oldBilling.Ratio3 = (double?)Math.Round((double)ratio3, 2);
if (ratio4 != null)
oldBilling.Ratio4 = (double?)Math.Round((double)ratio4, 2);
if (ratio5 != null)
oldBilling.Ratio5 = (double?)Math.Round((double)ratio5, 2);
if (ratio6 != null)
oldBilling.Ratio6 = (double?)Math.Round((double)ratio6, 2);
if (ratio7 != null)
oldBilling.Ratio7 = (double?)Math.Round((double)ratio7, 2);
if (minTime != null)
oldBilling.MinTime = (double?)Math.Round((double)minTime, 2);
oldBilling.Remark = remark;
oldBilling.Month = month;
oldBilling.UserDefinedCol10 = date;
oldBilling.IsShowShipLengthRule = isShowShipLengthRule;
oldBilling.IsShowShipTEUSRule = isShowShipTEUSRule;
oldBilling.LastUpDate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
db.Entry(oldBilling).State = System.Data.Entity.EntityState.Modified;
int ret = db.SaveChanges();
if (ret > 0)
{
#region 更新客户船长、箱量
V_Billing2 vb2 = db.V_Billing2.FirstOrDefault(u => u.IDX == billingId);
if (vb2 != null)
{
//vb2.ShipID;
CustomerShip cs = db.CustomerShip.FirstOrDefault(u => u.IDX == vb2.ShipID);
if (cs != null)
{
cs.Length = customer_ship_length;
cs.TEUS = customer_ship_teus;
db.Entry(cs).State = System.Data.Entity.EntityState.Modified;
db.SaveChanges();
}
}
#endregion
#region 订单收费项
//1.订单收费项
List<BillingItem> invoiceItems = db.BillingItem.Where(u => u.BillingID == billingId).ToList();
if (invoiceItems != null)
{
db.BillingItem.RemoveRange(invoiceItems);
ret = db.SaveChanges();
if (ret > 0)
{
List<InVoiceItem> listInVoiceItems = new List<InVoiceItem>();
listInVoiceItems = TugBusinessLogic.Utils.JSONStringToList<InVoiceItem>(jsonArrayItems);
if (listInVoiceItems != null)
{
foreach (InVoiceItem item in listInVoiceItems)
{
BillingItem bi = new BillingItem();
bi.BillingID = billingId;
bi.SchedulerID = item.SchedulerID;
bi.ItemID = item.ItemID;
bi.UnitPrice = item.UnitPrice;
bi.Currency = item.Currency;
bi.OwnerID = -1;
bi.UserID = Session.GetDataFromSession<int>("userid"); ;
bi.CreateDate = bi.LastUpDate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
bi.UserDefinedCol1 = "";
bi.UserDefinedCol2 = "";
bi.UserDefinedCol3 = "";
bi.UserDefinedCol4 = "";
bi.UserDefinedCol9 = "";
bi.UserDefinedCol10 = "";
bi = db.BillingItem.Add(bi);
ret = db.SaveChanges();
}
}
else
{
return Json(new { code = Resources.Common.FAIL_CODE, message = Resources.Common.FAIL_MESSAGE }, JsonRequestBehavior.AllowGet);
}
}
else
{
return Json(new { code = Resources.Common.FAIL_CODE, message = Resources.Common.FAIL_MESSAGE }, JsonRequestBehavior.AllowGet);
}
}
else
{
List<InVoiceItem> listInVoiceItems = new List<InVoiceItem>();
listInVoiceItems = TugBusinessLogic.Utils.JSONStringToList<InVoiceItem>(jsonArrayItems);
if (listInVoiceItems != null)
{
foreach (InVoiceItem item in listInVoiceItems)
{
BillingItem bi = new BillingItem();
bi.BillingID = billingId;
bi.SchedulerID = item.SchedulerID;
bi.ItemID = item.ItemID;
bi.UnitPrice = item.UnitPrice;
bi.Currency = item.Currency;
bi.OwnerID = -1;
bi.UserID = Session.GetDataFromSession<int>("userid"); ;
bi.CreateDate = bi.LastUpDate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
bi.UserDefinedCol1 = "";
bi.UserDefinedCol2 = "";
bi.UserDefinedCol3 = "";
bi.UserDefinedCol4 = "";
bi.UserDefinedCol9 = "";
bi.UserDefinedCol10 = "";
bi = db.BillingItem.Add(bi);
ret = db.SaveChanges();
}
}
else
{
return Json(new { code = Resources.Common.FAIL_CODE, message = Resources.Common.FAIL_MESSAGE }, JsonRequestBehavior.AllowGet);
}
}
#endregion
#region 账单的汇总项目
//2.插入账单的汇总项目
List<AmountSum> oldAmountSumList = db.AmountSum.Where(u => u.BillingID == oldBilling.IDX).ToList();
db.AmountSum.RemoveRange(oldAmountSumList);
db.SaveChanges();
List<InVoiceSummaryItem> listInVoiceSummaryItems = new List<InVoiceSummaryItem>();
listInVoiceSummaryItems = TugBusinessLogic.Utils.JSONStringToList<InVoiceSummaryItem>(jsonArraySummaryItems);
if (listInVoiceSummaryItems != null)
{
V_Invoice2 vi2 = db.V_Invoice2.FirstOrDefault(u => u.BillingID == oldBilling.IDX);
foreach (InVoiceSummaryItem item in listInVoiceSummaryItems)
{
AmountSum amtSum = new AmountSum();
amtSum.CustomerID = vi2.CustomerID;
amtSum.CustomerShipID = vi2.ShipID;
amtSum.BillingID = oldBilling.IDX;
amtSum.BillingDateTime = TugBusinessLogic.Utils.CNDateTimeToDateTime(oldBilling.CreateDate);
amtSum.SchedulerID = item.SchedulerID;
amtSum.Amount = item.Amount;
amtSum.FuelAmount = item.FuelPrice;
amtSum.Currency = item.Currency;
amtSum.Hours = item.Hours;
amtSum.Year = oldBilling.Month.Split('-')[0];//DateTime.Now.Year.ToString();
amtSum.Month = oldBilling.Month.Split('-')[1];//oldBilling.Month;
amtSum.OwnerID = -1;
amtSum.CreateDate = amtSum.LastUpDate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
amtSum.UserID = Session.GetDataFromSession<int>("userid");
amtSum = db.AmountSum.Add(amtSum);
db.SaveChanges();
}
}
#endregion
#region 更新回扣单编号
System.Linq.Expressions.Expression<Func<Credit, bool>> expCredit = u => u.BillingID == billingId;
List<Credit> tmpCredit = db.Credit.Where(expCredit).Select(u => u).ToList<Credit>();
//Credit tmpCredit = db.Credit.Where(expCredit).FirstOrDefault();
if (tmpCredit.Count != 0)
{
foreach (var item in tmpCredit)
{
item.CreditCode = "C" + billing_code.Substring(1, billing_code.Length - 1);
db.Entry(item).State = System.Data.Entity.EntityState.Modified;
db.SaveChanges();
}
}
#endregion
}
else
{
return Json(new { code = Resources.Common.ERROR_CODE, message = Resources.Common.ERROR_CODE }, JsonRequestBehavior.AllowGet);
}
}
else
{
return Json(new { code = Resources.Common.ERROR_CODE, message = Resources.Common.ERROR_MESSAGE }, JsonRequestBehavior.AllowGet);
}
}
}
catch (Exception ex)
{
return Json(new { code = Resources.Common.EXCEPTION_CODE, message = Resources.Common.EXCEPTION_MESSAGE }, JsonRequestBehavior.AllowGet);
}
return Json(new { code = Resources.Common.SUCCESS_CODE, message = Resources.Common.SUCCESS_MESSAGE }, JsonRequestBehavior.AllowGet);
}
public ActionResult AddInvoice2(int custId, int custShipId, string orderIds, string orderServiceIds, int billingTemplateId, int billingTypeId, int timeTypeId,
string jobNo, string billing_code, string remark, double discount, double amount, string month, string date, int customer_ship_length,
int customer_ship_teus, string isShowShipLengthRule, string isShowShipTEUSRule,
float? ratio1, float? ratio2, float? ratio3, float? ratio4, float? ratio5, float? ratio6, float? ratio7, float? minTime,
string jsonArrayItems, string jsonArraySummaryItems)
{
this.Internationalization();
using (TransactionScope trans = new TransactionScope())
{
try
{
List<string> strOrderIDs = orderIds.Split(',').ToList();
List<int> iOrderIDs = new List<int>();
if (strOrderIDs != null)
{
foreach (var item in strOrderIDs)
{
iOrderIDs.Add(TugBusinessLogic.Module.Util.toint(item));
}
}
TugDataEntities db = new TugDataEntities();
{
//0.验证账单编号是否已存在
var tmp = db.Billing.FirstOrDefault(u => u.BillingCode.Trim() == billing_code.Trim());
if (tmp != null)
{
var ret2 = new { code = Resources.Common.FAIL_CODE, message = Resources.Common.FAIL_MESSAGE };
//Response.Write(@Resources.Common.SUCCESS_MESSAGE);
return Json(ret2, JsonRequestBehavior.AllowGet);
}
//1.插入账单
TugDataModel.Billing aScheduler = new Billing();
aScheduler.CustomerID = custId;
aScheduler.JobNo = jobNo;
aScheduler.IsShowShipLengthRule = isShowShipLengthRule;
aScheduler.IsShowShipTEUSRule = isShowShipTEUSRule;
aScheduler.InvoiceType = "普通账单";
aScheduler.BillingTemplateID = billingTemplateId;
aScheduler.BillingTypeID = billingTypeId;
//aScheduler.BillingCode = TugBusinessLogic.Utils.AutoGenerateBillCode();
aScheduler.BillingCode = billing_code.Trim() ;
aScheduler.BillingName = "";
aScheduler.TimeTypeID = timeTypeId;
aScheduler.Discount = discount;
aScheduler.TotalRebate = 0;
aScheduler.Amount = amount;
aScheduler.Remark = remark;
aScheduler.Month = month;
aScheduler.UserDefinedCol10 = date;
if (ratio1 != null)
aScheduler.Ratio1 = (int?)Math.Round((float)ratio1, 2);
if (ratio2 != null)
aScheduler.Ratio2 = (int?)Math.Round((float)ratio2, 2);
if (ratio3 != null)
aScheduler.Ratio3 = (int?)Math.Round((float)ratio3, 2);
if (ratio4 != null)
aScheduler.Ratio4 = (int?)Math.Round((float)ratio4, 2);
if (ratio5 != null)
aScheduler.Ratio5 = (int?)Math.Round((float)ratio5, 2);
if (ratio6 != null)
aScheduler.Ratio6 = (int?)Math.Round((float)ratio6, 2);
if (ratio7 != null)
aScheduler.Ratio7 = (int?)Math.Round((float)ratio7, 2);
if (minTime != null)
aScheduler.MinTime = (int?)Math.Round((float)minTime, 2);
aScheduler.TimesNo = 0;
aScheduler.Status = "创建";
aScheduler.Phase = 0;
aScheduler.OwnerID = -1;
aScheduler.CreateDate = aScheduler.LastUpDate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
aScheduler.UserID = Session.GetDataFromSession<int>("userid");
aScheduler.UserDefinedCol1 = "";
aScheduler.UserDefinedCol2 = "";
aScheduler.UserDefinedCol3 = "";
aScheduler.UserDefinedCol4 = "";
aScheduler.UserDefinedCol9 = "";
aScheduler = db.Billing.Add(aScheduler);
db.SaveChanges();
//2.插入账单、多个订单
List<BillingOrder> listBillingOrder = new List<BillingOrder>();
foreach (int orderId in iOrderIDs)
{
BillingOrder bo = new BillingOrder();
bo.BillingID = aScheduler.IDX;
bo.OrderID = orderId;
listBillingOrder.Add(bo);
}
db.BillingOrder.AddRange(listBillingOrder);
db.SaveChanges();
//3.插入账单的收费项目
List<InVoiceItem> listInVoiceItems = new List<InVoiceItem>();
listInVoiceItems = TugBusinessLogic.Utils.JSONStringToList<InVoiceItem>(jsonArrayItems);
if (listInVoiceItems != null)
{
foreach (InVoiceItem item in listInVoiceItems)
{
BillingItem bi = new BillingItem();
bi.BillingID = aScheduler.IDX;
bi.SchedulerID = item.SchedulerID;
bi.ItemID = item.ItemID;
bi.UnitPrice = item.UnitPrice;
bi.Currency = item.Currency;
bi.OwnerID = -1;
bi.UserID = Session.GetDataFromSession<int>("userid");
bi.CreateDate = bi.LastUpDate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
bi.UserDefinedCol1 = "";
bi.UserDefinedCol2 = "";
bi.UserDefinedCol3 = "";
bi.UserDefinedCol4 = "";
bi.UserDefinedCol9 = "";
bi.UserDefinedCol10 = "";
bi = db.BillingItem.Add(bi);
db.SaveChanges();
}
}
//4.插入账单的汇总项目
List<InVoiceSummaryItem> listInVoiceSummaryItems = new List<InVoiceSummaryItem>();
listInVoiceSummaryItems = TugBusinessLogic.Utils.JSONStringToList<InVoiceSummaryItem>(jsonArraySummaryItems);
if (listInVoiceSummaryItems != null)
{
foreach (InVoiceSummaryItem item in listInVoiceSummaryItems)
{
AmountSum amtSum = new AmountSum();
amtSum.CustomerID = custId;
amtSum.CustomerShipID = custShipId;
amtSum.BillingID = aScheduler.IDX;
amtSum.BillingDateTime = TugBusinessLogic.Utils.CNDateTimeToDateTime(aScheduler.CreateDate);
amtSum.SchedulerID = item.SchedulerID;
amtSum.Amount = item.Amount;
amtSum.FuelAmount = item.FuelPrice;
amtSum.Currency = item.Currency;
amtSum.Hours = item.Hours;
amtSum.Year = aScheduler.Month.Split('-')[0];//DateTime.Now.Year.ToString();
amtSum.Month = aScheduler.Month.Split('-')[1];// aScheduler.Month;
amtSum.OwnerID = -1;
amtSum.CreateDate = amtSum.LastUpDate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
amtSum.UserID = Session.GetDataFromSession<int>("userid");
amtSum = db.AmountSum.Add(amtSum);
db.SaveChanges();
}
}
//5.更新订单的字段 V_OrderInfor_HasInvoice 是否已有帳單
{
List<OrderInfor> odList = db.OrderInfor.Where(u => iOrderIDs.Contains(u.IDX)).ToList();
//throw new Exception();
if (odList != null)
{
foreach (OrderInfor od in odList)
{
od.HasInvoice = "是";
od.HasInFlow = "否";
db.Entry(od).State = System.Data.Entity.EntityState.Modified;
db.SaveChanges();
}
}
}
//6.更新订单服务表里的字段 HasBilling、HasBillingInFlow 是否已有帳單
{
List<string> strOrderServiceIDs = orderServiceIds.Split(',').ToList();
List<int> iOrderServiceIDs = new List<int>();
if (strOrderServiceIDs != null)
{
foreach (var item in strOrderServiceIDs)
{
iOrderServiceIDs.Add(TugBusinessLogic.Module.Util.toint(item));
}
}
List<OrderService> ordSrvList = db.OrderService.Where(u => iOrderServiceIDs.Contains((int)u.IDX)).ToList();
//throw new Exception();
if (ordSrvList != null)
{
foreach (OrderService ods in ordSrvList)
{
ods.HasBilling = "是";
ods.HasBillingInFlow = "否";
ods.BillingType = 0;
db.Entry(ods).State = System.Data.Entity.EntityState.Modified;
db.SaveChanges();
}
}
}
//7.更新客户船的船长、箱量
{
CustomerShip cs = db.CustomerShip.FirstOrDefault(u => u.IDX == custShipId);
if (cs != null)
{
cs.Length = customer_ship_length;
cs.TEUS = customer_ship_teus;
db.Entry(cs).State = System.Data.Entity.EntityState.Modified;
db.SaveChanges();
}
}
trans.Complete();
var ret = new { code = Resources.Common.SUCCESS_CODE, message = Resources.Common.SUCCESS_MESSAGE, billing_id = aScheduler.IDX };
//Response.Write(@Resources.Common.SUCCESS_MESSAGE);
return Json(ret, JsonRequestBehavior.AllowGet);
}
}
catch (Exception ex)
{
trans.Dispose();
var ret = new { code = Resources.Common.EXCEPTION_CODE, message = Resources.Common.EXCEPTION_MESSAGE, billing_id = -1 };
//Response.Write(@Resources.Common.EXCEPTION_MESSAGE);
return Json(ret, JsonRequestBehavior.AllowGet);
}
}
}
/// <summary>
/// 新增或编辑特殊账单之后，插入汇总项目
/// </summary>
/// <param name="billingId"></param>
/// <param name="userId"></param>
public static void UpdateSpecialBillingSummarizeItems(int billingId, int userId)
{
TugDataEntities db = new TugDataEntities();
var lstSpecialBillingSummarize = db.AmountSum.Where(u => u.BillingID == billingId).ToList();
if (lstSpecialBillingSummarize != null && lstSpecialBillingSummarize.Count > 0)
{
//先删除，再插入
foreach (var item in lstSpecialBillingSummarize)
{
db.AmountSum.Remove(item);
db.SaveChanges();
}
}
//else
{
//直接插入
var list = db.V_SpecialBillingSummarizeItem.Where(u => u.SpecialBillingID == billingId).ToList();
if (list != null)
{
List<AmountSum> ret = new List<AmountSum>();
foreach (var item in list)
{
AmountSum one = new AmountSum();
one.CustomerID = item.CustomerID;
one.CustomerShipID = item.CustomerShipID;
one.BillingID = billingId;
one.BillingDateTime = TugBusinessLogic.Utils.CNDateTimeToDateTime(item.BillingDateTime);
one.SchedulerID = item.SchedulerID;
one.FuelAmount = item.FeulUnitPrice;
one.Amount = item.Amount;
one.Currency = "港币";
int iDiffHour, iDiffMinute;
TugBusinessLogic.Utils.CalculateTimeDiff(item.DepartBaseTime, item.ArrivalBaseTime, out iDiffHour, out iDiffMinute);
#region 按一小时换算时间
{
//double consumeTime = 0;
//int count = 0;
//count += iDiffHour * 60 / 60;
//count += iDiffMinute / 60;
//if (iDiffMinute % 60 > 0)
//{
// count++;
//}
//consumeTime = (count * 60.0) / 60;
double tmp = ((double)iDiffMinute) / 60;
one.Hours = iDiffHour + Math.Round(tmp, 2);
}
#endregion
one.Year = item.Month.Split('-')[0];//one.BillingDateTime.Value.Year.ToString();
one.Month = item.Month.Split('-')[1];//item.Month;
one.OwnerID = -1;
one.CreateDate = one.LastUpDate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
one.UserID = userId;
ret.Add(one);
}
db.AmountSum.AddRange(ret);
db.SaveChanges();
}
}
}