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); }