public void BackDown(string tempGuid, int FPId, SqlCommand objCommand, out Sell_OrderFP model, out List <Sell_OrderFPs> fpList)
{
model = new Sell_OrderFP();
fpList = new List <Sell_OrderFPs>();
//删除上一条数据
string sql = string.Format("select * from Sell_OrderFP_History where TempGuid='{0}' and id={1} order by UpdateTime DESC ", tempGuid, FPId);
objCommand.CommandText = sql;
using (SqlDataReader dataReader = objCommand.ExecuteReader())
{
if (dataReader.Read())
{
model = ReaderBind(dataReader);
model.NowGuid = dataReader["TempGuid"].ToString();
//model.Status = "通过";
}
}
if (model != null)
{
sql = string.Format(@"select
Ids,Sell_OrderFPs_History.id,GooId,GoodNum,GoodPrice,GoodRemark,GoodNo,GoodName,GoodSpec,GoodModel,GoodUnit,GoodTypeSmName,GoodSellPrice,SellOutPONO,SellOutOrderId
from Sell_OrderFPs_History left join TB_Good on TB_Good.GoodId=Sell_OrderFPs_History.GooId where TempGuid='{0}' and Sell_OrderFPs_History.id={1} ", model.NowGuid, model.Id);
objCommand.CommandText = sql;
using (SqlDataReader dataReader = objCommand.ExecuteReader())
{
while (dataReader.Read())
{
fpList.Add(ReaderBindList(dataReader));
}
}
}
}
public bool updateTran_BakDown(string NowGuid, int id)
{
using (SqlConnection conn = DBHelp.getConn())
{
conn.Open();
SqlTransaction tan = conn.BeginTransaction();
SqlCommand objCommand = conn.CreateCommand();
objCommand.Transaction = tan;
Sell_OrderFP model = new Sell_OrderFP();
List <Sell_OrderFPs> orders = new List <Sell_OrderFPs>();
BackUpFPInfoService backUpSer = new BackUpFPInfoService();
backUpSer.BackDown(NowGuid, id, objCommand, out model, out orders);
if (model.Id > 0)
{
try
{
decimal total = 0;
foreach (var m in orders)
{
total += m.GoodSellPriceTotal;
}
model.Total = total;
System.Collections.Hashtable hs = new System.Collections.Hashtable();
objCommand.Parameters.Clear();
Update(model, objCommand);
Sell_OrderFPsService OrdersSer = new Sell_OrderFPsService();
//删除之前的数据
objCommand.CommandText = string.Format("delete from Sell_OrderFPs where id=" + model.Id);
objCommand.ExecuteNonQuery();
for (int i = 0; i < orders.Count; i++)
{
orders[i].id = model.Id;
OrdersSer.Add(orders[i], objCommand);
if (model.Status == "通过")
{
if (!hs.Contains(orders[i].SellOutPONO))
{
hs.Add(orders[i].SellOutPONO, null);
}
}
}
foreach (var key in hs.Keys)
{
//更改销售订单的发票号
string sql = string.Format("update Sell_OrderOutHouse set FPNo=FPNo+'{0}/' where ProNo='{1}'", model.FPNo, key);
objCommand.CommandText = sql;
objCommand.ExecuteNonQuery();
//更改项目订单的发票号
sql = string.Format("update CG_POOrder set FPTotal=isnull(FPTotal,'')+'{0}/' where PONo='{1}' and ifzhui=0 ", model.FPNo, model.PONo);
objCommand.CommandText = sql;
objCommand.ExecuteNonQuery();
}
tan.Commit();
if (model.Status == "通过")
{
new Sell_OrderFPBackService().SellFPOrderBackUpdatePoStatus(model.PONo);
new CG_POOrderService().GetOrder_ToInvoiceAndUpdatePoStatus(model.PONo);
new CG_POOrdersService().GetListArrayToFpsAndUpdatePoStatue(model.PONo, model.Status);
new Sell_OrderFPBackService().SellFPOrderBackUpdatePoStatus(model.PONo);
}
}
catch (Exception)
{
tan.Rollback();
return(false);
}
}
//else
//{
// tan.Rollback();
//}
}
return(true);
}
/// <summary>
/// 预付款结转 生成 实际到款单
/// </summary>
/// <returns></returns>
public bool YuPay_CreateInvoice(Sell_OrderFP model, decimal PayTotal)
{
DateTime daoKuanDate = Convert.ToDateTime("1900-1-1");
using (SqlConnection conn = DBHelp.getConn())
{
conn.Open();
SqlTransaction tan = conn.BeginTransaction();
SqlCommand objCommand = conn.CreateCommand();
objCommand.Transaction = tan;
objCommand.CommandText = string.Format("select isnull(sum(Total),0) from TB_ToInvoice where BusType=1 and PoNo='{0}' and State='通过'", model.PONo);
var result_payTotal = Convert.ToDecimal(objCommand.ExecuteScalar());
objCommand.CommandText = string.Format("select isnull(max(DaoKuanDate),getdate()) from TB_ToInvoice where BusType=1 and PoNo='{0}' and State='通过'", model.PONo);
daoKuanDate = Convert.ToDateTime(objCommand.ExecuteScalar());
if (PayTotal < model.Total || (PayTotal == model.Total && result_payTotal == PayTotal))
{
//所有预付款记录的剩余预付款字段金额更新 为0
objCommand.CommandText = string.Format("update TB_ToInvoice set Total=0 where BusType=1 and PoNo='{0}' and State='通过' ", model.PONo);
objCommand.ExecuteNonQuery();
}
if (PayTotal == model.Total && result_payTotal > PayTotal)
{
//提交审批通过后 第一条预付款记录的剩余预付款字段金额更新 为 （原所有预付款记录的剩余金额合计值-此发票金额），
objCommand.CommandText = string.Format("select id,Total,DaoKuanDate from TB_ToInvoice where BusType=1 and PoNo='{0}' and State='通过' order by AppleDate ", model.PONo);
List <TB_ToInvoice> invoList = new List <TB_ToInvoice>();
using (SqlDataReader dataReader = objCommand.ExecuteReader())
{
while (dataReader.Read())
{
TB_ToInvoice invM = new TB_ToInvoice();
invM.Id = (int)dataReader["id"];
invM.Total = Convert.ToDecimal(dataReader["Total"]);
try
{
invM.DaoKuanDate = Convert.ToDateTime(dataReader["DaoKuanDate"]);
}
catch (Exception)
{
}
invoList.Add(invM);
if (invoList.Sum(t => t.Total) > PayTotal)
{
break;
}
}
}
decimal sum = 0;
for (int i = 0; i < invoList.Count; i++)
{
if (i == invoList.Count - 1)
{
objCommand.CommandText = string.Format("update TB_ToInvoice set Total=Total-{1} where id={0} ",
invoList[i].Id, (PayTotal - sum));
objCommand.ExecuteNonQuery();
}
else
{
objCommand.CommandText = string.Format("update TB_ToInvoice set Total=0 where id={0} ", invoList[i].Id);
objCommand.ExecuteNonQuery();
sum += invoList[i].Total;
}
}
}
tan.Commit();
}
//生成预付款单
TB_ToInvoice toInvoic_model = new TB_ToInvoice();
toInvoic_model.AppleDate = DateTime.Now;
toInvoic_model.CreateUser = "******";
toInvoic_model.DaoKuanDate = daoKuanDate;
toInvoic_model.GuestName = model.GuestName;
toInvoic_model.PoName = model.POName;
toInvoic_model.PoNo = model.PONo;
toInvoic_model.Total = PayTotal;
toInvoic_model.UpAccount = 0;
toInvoic_model.FPNo = model.FPNo;
string sql = string.Format("select top 1 guestDays from TB_GuestTrack where guestName='{0}'", model.GuestName);
object ob = DBHelp.ExeScalar(sql);
toInvoic_model.ZhangQi = ob is DBNull ? 0 : Convert.ToDecimal(ob);
toInvoic_model.FPId = model.Id;
toInvoic_model.BusType = 0;
toInvoic_model.State = "通过";
toInvoic_model.Remark = "";
VAN_OA.Model.EFrom.tb_EForm eform = new tb_EForm();
eform.appPer = 1;
eform.appTime = DateTime.Now;
eform.createPer = 1;
eform.createTime = DateTime.Now;
eform.proId = 27;
eform.state = "通过";
eform.toPer = 0;
eform.toProsId = 0;
if (addTran(toInvoic_model, eform) > 0)
{
new CG_POOrderService().GetOrder_ToInvoiceAndUpdatePoStatus(toInvoic_model.PoNo);
}
return(true);
}
