/// <summary> /// 获得单号 /// </summary> /// <returns>返回获取的单号</returns> public string GetBillID() { string strNewDJH = ""; try { string strDJH = "YXGL" + ServerTime.Time.Year.ToString(); string strSql = "select max(substring(Bill_ID,11,5)) from S_QuarantineBill where Bill_ID like '" + strDJH + "%'"; DataTable dt = GlobalObject.DatabaseServer.QueryInfo(strSql); if (dt.Rows[0][0].ToString() != "") { string strValue = (Convert.ToInt32(dt.Rows[0][0].ToString()) + 1).ToString("D5"); strNewDJH = strDJH + ServerTime.GetMouth() + strValue; } else { strNewDJH = strDJH + ServerTime.GetMouth() + "00001"; } return(strNewDJH); } catch (Exception ex) { return(ex.Message); } }
/// <summary> /// 更新出入库的金额 /// </summary> /// <param name="invoiceTable">需要更新的数据集</param> /// <param name="error">出错时返回错误信息,无错时返回null</param> /// <returns>更新成功True,更新失败False</returns> public bool UpdatePrice(DataTable invoiceTable, out string error) { error = null; try { DepotManagementDataContext dataContxt = CommentParameter.DepotDataContext; OrdinaryInDepotBillServer serverOrdinaryBill = new OrdinaryInDepotBillServer(); MaterialRejectBill serverMaterialRejectBill = new MaterialRejectBill(); DateTime dtStart = new DateTime(); DateTime dtEnd = new DateTime(); //获得当前日期的月结起始日期与结束日期 ServerTime.GetMonthlyBalance(ServerTime.Time, out dtStart, out dtEnd); for (int i = 0; i <= invoiceTable.Rows.Count - 1; i++) { string code = invoiceTable.Rows[i]["GoodsCode"].ToString(); string name = invoiceTable.Rows[i]["GoodsName"].ToString(); string spec = invoiceTable.Rows[i]["Spec"].ToString(); View_F_GoodsPlanCost basicGoods = m_basicGoodsServer.GetGoodsInfo(code, name, spec, out error); if (!GlobalObject.GeneralFunction.IsNullOrEmpty(error)) { return(false); } #region 改变入库表的单价(普通入库或者报检入库) var varCheckOutInDepot = from a in dataContxt.S_CheckOutInDepotBill where a.Bill_ID == invoiceTable.Rows[i]["Bill_ID"].ToString() && a.GoodsID == basicGoods.序号 && a.BatchNo == invoiceTable.Rows[i]["BatchNo"].ToString() select a; //报检入库单单价修改 if (varCheckOutInDepot.Count() != 0) { S_CheckOutInDepotBill lnqCheckOutInDepotBill = varCheckOutInDepot.Single(); lnqCheckOutInDepotBill.UnitInvoicePrice = Convert.ToDecimal(invoiceTable.Rows[i]["UnitPrice"]); lnqCheckOutInDepotBill.InvoicePrice = Convert.ToDecimal(invoiceTable.Rows[i]["Price"]); lnqCheckOutInDepotBill.HavingInvoice = true; dataContxt.SubmitChanges(); } else { int intGoodsID = m_basicGoodsServer.GetGoodsID(invoiceTable.Rows[i]["GoodsCode"].ToString(), invoiceTable.Rows[i]["GoodsName"].ToString(), invoiceTable.Rows[i]["Spec"].ToString()); var varOrdinaryGoods = from a in dataContxt.S_OrdinaryInDepotGoodsBill where a.Bill_ID == invoiceTable.Rows[i]["Bill_ID"].ToString() && a.GoodsID == intGoodsID && a.BatchNo == invoiceTable.Rows[i]["BatchNo"].ToString() select a; //普通入库单单价修改 if (varOrdinaryGoods.Count() != 0) { S_OrdinaryInDepotGoodsBill lnqOrdinaryGoods = varOrdinaryGoods.Single(); lnqOrdinaryGoods.InvoiceUnitPrice = Convert.ToDecimal(invoiceTable.Rows[i]["UnitPrice"]); lnqOrdinaryGoods.InvoicePrice = Convert.ToDecimal(invoiceTable.Rows[i]["Price"]); lnqOrdinaryGoods.HavingInvoice = true; dataContxt.SubmitChanges(); int intFlag = serverOrdinaryBill.GetHavingInvoice(invoiceTable.Rows[i]["Bill_ID"].ToString(), out error); if (intFlag == 4) { return(false); } else { var varOrdinaryBill = from a in dataContxt.S_OrdinaryInDepotBill where a.Bill_ID == invoiceTable.Rows[i]["Bill_ID"].ToString() select a; if (varOrdinaryBill.Count() != 0) { S_OrdinaryInDepotBill lnqOrdinaryBill = varOrdinaryBill.Single(); lnqOrdinaryBill.InvoiceStatus = intFlag; dataContxt.SubmitChanges(); } } }//采购退货单单价修改 else { intGoodsID = m_basicGoodsServer.GetGoodsID(invoiceTable.Rows[i]["GoodsCode"].ToString(), invoiceTable.Rows[i]["GoodsName"].ToString(), invoiceTable.Rows[i]["Spec"].ToString()); var varRejectList = from a in dataContxt.S_MaterialListRejectBill where a.Bill_ID == invoiceTable.Rows[i]["Bill_ID"].ToString() && a.GoodsID == intGoodsID && a.BatchNo == invoiceTable.Rows[i]["BatchNo"].ToString() select a; if (varRejectList.Count() != 0) { S_MaterialListRejectBill lnqMaterialList = varRejectList.Single(); lnqMaterialList.InvoiceUnitPrice = Convert.ToDecimal(invoiceTable.Rows[i]["UnitPrice"]); lnqMaterialList.InvoicePrice = Convert.ToDecimal(invoiceTable.Rows[i]["Price"]); lnqMaterialList.HavingInvoice = true; dataContxt.SubmitChanges(); int intFlag = serverMaterialRejectBill.SetHavingInvoiceReturn(invoiceTable.Rows[i]["Bill_ID"].ToString(), out error); if (intFlag == 4) { return(false); } else { var varReject = from a in dataContxt.S_MaterialRejectBill where a.Bill_ID == invoiceTable.Rows[i]["Bill_ID"].ToString() select a; if (varReject.Count() != 0) { S_MaterialRejectBill lnqMaterialBill = varReject.Single(); lnqMaterialBill.InvoiceFlag = intFlag; dataContxt.SubmitChanges(); } } } else { intGoodsID = m_basicGoodsServer.GetGoodsID(invoiceTable.Rows[i]["GoodsCode"].ToString(), invoiceTable.Rows[i]["GoodsName"].ToString(), invoiceTable.Rows[i]["Spec"].ToString()); var varOutsourcing = from a in dataContxt.S_CheckOutInDepotForOutsourcingBill where a.Bill_ID == invoiceTable.Rows[i]["Bill_ID"].ToString() && a.GoodsID == intGoodsID && a.BatchNo == invoiceTable.Rows[i]["BatchNo"].ToString() select a; //委外报检入库单单价修改 if (varOutsourcing.Count() != 0) { S_CheckOutInDepotForOutsourcingBill lnqOutsourcing = varOutsourcing.Single(); lnqOutsourcing.UnitInvoicePrice = Convert.ToDecimal(invoiceTable.Rows[i]["UnitPrice"]); lnqOutsourcing.InvoicePrice = Convert.ToDecimal(invoiceTable.Rows[i]["Price"]); lnqOutsourcing.HavingInvoice = true; dataContxt.SubmitChanges(); } } } } #endregion #region 改变入库明细表金额 var varInDepotBill = from b in dataContxt.S_InDepotDetailBill where b.GoodsID == basicGoods.序号 && b.InDepotBillID == invoiceTable.Rows[i]["Bill_ID"].ToString() && b.BatchNo == invoiceTable.Rows[i]["BatchNo"].ToString() select b; if (varInDepotBill.Count() == 1) { S_InDepotDetailBill lnqInDepotBill = varInDepotBill.Single(); lnqInDepotBill.InvoiceUnitPrice = Convert.ToDecimal(invoiceTable.Rows[i]["UnitPrice"]); lnqInDepotBill.InvoicePrice = Convert.ToDecimal(invoiceTable.Rows[i]["Price"]); if (lnqInDepotBill.FactPrice != Convert.ToDecimal(invoiceTable.Rows[i]["Price"])) { //当查询的记录不在当月的结算日期范围内,插入红冲单据与对冲单据 if (lnqInDepotBill.BillTime < dtStart || lnqInDepotBill.BillTime > dtEnd) { var varDetail = from d in dataContxt.S_InDepotDetailBill where d.GoodsID == basicGoods.序号 && d.InDepotBillID.Contains(invoiceTable.Rows[i]["Bill_ID"].ToString()) && d.BatchNo == invoiceTable.Rows[i]["BatchNo"].ToString() && d.BillTime >= dtStart && d.BillTime <= dtEnd select d; //判断是否已经在当前结算日期范围内插入了红冲与对冲数据 if (varDetail.Count() != 0) { foreach (var item in varDetail) { //针对已经插入的对冲数据进行修改 if (item.InDepotBillID.Contains("(对冲单据)")) { item.FactPrice = Convert.ToDecimal(invoiceTable.Rows[i]["Price"]); item.FactUnitPrice = Convert.ToDecimal(invoiceTable.Rows[i]["UnitPrice"]); } } }//对没有插入的红冲与对冲的记录进行插入 else { //插一条原始的负记录(红冲单据) S_InDepotDetailBill lnqOldInDepotBill = new S_InDepotDetailBill(); lnqOldInDepotBill.ID = Guid.NewGuid(); lnqOldInDepotBill.InDepotBillID = lnqInDepotBill.InDepotBillID + "(红冲单据)"; lnqOldInDepotBill.BatchNo = lnqInDepotBill.BatchNo; lnqOldInDepotBill.BillTime = ServerTime.Time; lnqOldInDepotBill.Department = lnqInDepotBill.Department; lnqOldInDepotBill.FactUnitPrice = lnqInDepotBill.FactUnitPrice; lnqOldInDepotBill.FactPrice = -lnqInDepotBill.FactPrice; lnqOldInDepotBill.FillInPersonnel = lnqInDepotBill.FillInPersonnel; lnqOldInDepotBill.GoodsID = lnqInDepotBill.GoodsID; lnqOldInDepotBill.InDepotCount = -lnqInDepotBill.InDepotCount; lnqOldInDepotBill.Price = -lnqInDepotBill.Price; lnqOldInDepotBill.OperationType = (int)GlobalObject.CE_SubsidiaryOperationType.财务红冲; lnqOldInDepotBill.Provider = lnqInDepotBill.Provider; lnqOldInDepotBill.Remark = lnqInDepotBill.Remark; lnqOldInDepotBill.StorageID = lnqInDepotBill.StorageID; lnqOldInDepotBill.UnitPrice = lnqInDepotBill.UnitPrice; lnqOldInDepotBill.FillInDate = lnqInDepotBill.FillInDate; lnqOldInDepotBill.AffrimPersonnel = lnqInDepotBill.AffrimPersonnel; IFinancialDetailManagement serverDetail = ServerModule.ServerModuleFactory.GetServerModule <IFinancialDetailManagement>(); serverDetail.ProcessInDepotDetail(dataContxt, lnqOldInDepotBill, null); //插一条新的正记录(对冲单据) S_InDepotDetailBill lnqNewInDepotBill = new S_InDepotDetailBill(); lnqNewInDepotBill.ID = Guid.NewGuid(); lnqNewInDepotBill.InDepotBillID = lnqInDepotBill.InDepotBillID + "(对冲单据)"; lnqNewInDepotBill.BatchNo = lnqInDepotBill.BatchNo; lnqNewInDepotBill.BillTime = ServerTime.Time; lnqNewInDepotBill.Department = lnqInDepotBill.Department; lnqNewInDepotBill.FactUnitPrice = Convert.ToDecimal(invoiceTable.Rows[i]["UnitPrice"]); lnqNewInDepotBill.FactPrice = Convert.ToDecimal(invoiceTable.Rows[i]["Price"]); lnqNewInDepotBill.FillInPersonnel = lnqInDepotBill.FillInPersonnel; lnqNewInDepotBill.GoodsID = lnqInDepotBill.GoodsID; lnqNewInDepotBill.InDepotCount = lnqInDepotBill.InDepotCount; lnqNewInDepotBill.Price = lnqInDepotBill.Price; lnqNewInDepotBill.OperationType = (int)GlobalObject.CE_SubsidiaryOperationType.财务对冲; lnqNewInDepotBill.Provider = lnqInDepotBill.Provider; lnqNewInDepotBill.Remark = lnqInDepotBill.Remark; lnqNewInDepotBill.StorageID = lnqInDepotBill.StorageID; lnqNewInDepotBill.UnitPrice = lnqInDepotBill.UnitPrice; lnqNewInDepotBill.FillInDate = lnqInDepotBill.FillInDate; lnqNewInDepotBill.AffrimPersonnel = lnqInDepotBill.AffrimPersonnel; serverDetail.ProcessInDepotDetail(dataContxt, lnqNewInDepotBill, null); } } else { lnqInDepotBill.FactPrice = Convert.ToDecimal(invoiceTable.Rows[i]["Price"]); lnqInDepotBill.FactUnitPrice = Convert.ToDecimal(invoiceTable.Rows[i]["UnitPrice"]); } } dataContxt.SubmitChanges(); } #endregion } return(true); } catch (Exception ex) { error = ex.Message; return(false); } }
/// <summary> /// 由EXCEL表导入且对挂账表填入实挂数量 /// </summary> /// <param name="communicate">主机厂编码</param> /// <param name="excle">EXCLE表</param> /// <param name="error">出错时返回错误信息,无错时返回null</param> /// <returns>导入成功返回True,导入失败返回False</returns> public bool UpdateSignTheBill(string communicate, DataTable excle, out string error) { error = null; try { DepotManagementDataContext dataContext = CommentParameter.DepotDataContext; decimal dcPrice = 0; string strNy = ServerTime.GetMonthlyString(ServerTime.Time); for (int i = 0; i < excle.Rows.Count; i++) { decimal dcOnePrice = 0; int intGoodsID = GetGoodsID( excle.Rows[i]["图号型号"].ToString(), excle.Rows[i]["物品名称"].ToString(), communicate); if (intGoodsID != 0) { DateTime dtNext = Convert.ToDateTime(strNy.Substring(0, 4) + "-" + strNy.Substring(4, 2) + "-01").AddMonths(1); //对本月的挂账以及下月的期初 数量进行变更 string strNextNy = dtNext.Year.ToString() + dtNext.Month.ToString("D2"); var varNext = from a in dataContext.YX_SignTheBill where a.Communicate == communicate && a.YearAndMonth == strNextNy && a.GoodsID == intGoodsID select a; var varData = from a in dataContext.YX_SignTheBill where a.Communicate == communicate && a.YearAndMonth == strNy && a.GoodsID == intGoodsID select a; if (varData.Count() == 0) { YX_SignTheBill lnqSign = new YX_SignTheBill(); lnqSign.Communicate = communicate; lnqSign.CurrentPeriodRealHangCount = Convert.ToDecimal(excle.Rows[i]["实挂数量"]); lnqSign.GoodsID = intGoodsID; lnqSign.PriorPeriodBalanceCount = 0; lnqSign.UnitPrice = Convert.ToDecimal(excle.Rows[i]["协议单价"]); lnqSign.YearAndMonth = strNy; dataContext.YX_SignTheBill.InsertOnSubmit(lnqSign); lnqSign.Communicate = communicate; lnqSign.CurrentPeriodRealHangCount = 0; lnqSign.GoodsID = intGoodsID; lnqSign.PriorPeriodBalanceCount = GetCommunicateGoodsOperationPrice(strNy, communicate, intGoodsID) - Convert.ToDecimal(excle.Rows[i]["实挂数量"]); lnqSign.UnitPrice = 0; lnqSign.YearAndMonth = strNextNy; dataContext.YX_SignTheBill.InsertOnSubmit(lnqSign); } else if (varData.Count() == 1) { YX_SignTheBill lnqSign = varData.Single(); lnqSign.CurrentPeriodRealHangCount = Convert.ToDecimal(excle.Rows[i]["实挂数量"]); lnqSign.UnitPrice = Convert.ToDecimal(excle.Rows[i]["协议单价"]); if (varNext.Count() == 1) { varNext.Single().PriorPeriodBalanceCount = lnqSign.PriorPeriodBalanceCount + GetCommunicateGoodsOperationPrice(strNy, communicate, intGoodsID) - Convert.ToDecimal(excle.Rows[i]["实挂数量"]);; } } else { error = "数据不唯一,请重新核对后再导入"; return(false); } } else { error = "图号为【" + excle.Rows[i]["图号型号"].ToString() + "】,名称为【" + excle.Rows[i]["物品名称"].ToString() + "】未匹配系统零件,请匹配后再导入"; return(false); } dcOnePrice = Math.Round(Convert.ToDecimal(excle.Rows[i]["实挂数量"]) * Convert.ToDecimal(excle.Rows[i]["协议单价"]), 2); dcPrice = dcPrice + dcOnePrice; } var varReturnMoney = from a in dataContext.YX_CommunicateReturnedMoneyBill where a.Communicate == communicate && a.YearAndMonth == strNy select a; if (varReturnMoney.Count() != 1) { error = "数据为空或者不唯一"; return(false); } else { varReturnMoney.Single().BillingPrice = dcPrice; } dataContext.SubmitChanges(); return(true); } catch (Exception ex) { error = ex.Message; return(false); } }
/// <summary> /// 批量插入CVT客户基础信息 /// </summary> /// <param name="cvtCustomerInfomation">CVT客户信息列表</param> /// <param name="error">出错时返回错误信息,无错时返回null</param> /// <returns>插入成功返回True,插入失败返回False</returns> public bool BatchInsertCVTCustomerInformation(DataTable cvtCustomerInfomation, out string error) { error = null; string strTemp = ""; int intFlag = 0; ProductListServer serverProductList = new ProductListServer(); try { DepotManagementDataContext dataContext = CommentParameter.DepotDataContext; for (int i = 0; i < cvtCustomerInfomation.Rows.Count; i++) { strTemp = cvtCustomerInfomation.Rows[i]["车架号"].ToString().Trim(); if (strTemp == "") { continue; } YX_CVTCustomerInformation lnqCVTCustomerInfo = new YX_CVTCustomerInformation(); int intGoodsID = serverProductList.GetProductGoodsID( cvtCustomerInfomation.Rows[i]["CVT型号"].ToString().Trim(), 0, false); if (intGoodsID == 0) { error = "[CVT型号]不符合标准,车架号为[" + strTemp + "]"; return(false); } if (cvtCustomerInfomation.Rows[i]["销售日期"].ToString().Trim() == "") { error = error + "[" + cvtCustomerInfomation.Rows[i]["车架号"].ToString().Trim() + "]"; } var varData = from a in dataContext.YX_CVTCustomerInformation where a.VehicleShelfNumber == strTemp select a; if (varData.Count() == 0) { lnqCVTCustomerInfo.ClientName = cvtCustomerInfomation.Rows[i]["客户名称"].ToString().Trim(); lnqCVTCustomerInfo.CVTNumber = cvtCustomerInfomation.Rows[i]["CVT编号"].ToString().Trim(); lnqCVTCustomerInfo.DealerName = cvtCustomerInfomation.Rows[i]["经销商名称"].ToString().Trim(); lnqCVTCustomerInfo.FullAddress = cvtCustomerInfomation.Rows[i]["详细地址"].ToString().Trim(); lnqCVTCustomerInfo.PhoneNumber = cvtCustomerInfomation.Rows[i]["联系电话"].ToString().Trim(); lnqCVTCustomerInfo.ProductID = intGoodsID; lnqCVTCustomerInfo.Remark = cvtCustomerInfomation.Rows[i]["备注"].ToString().Trim(); lnqCVTCustomerInfo.SellDate = ServerTime.ConvertToDateTime( cvtCustomerInfomation.Rows[i]["销售日期"].ToString().Trim()); lnqCVTCustomerInfo.SiteCity = cvtCustomerInfomation.Rows[i]["车辆所在地"].ToString().Trim(); lnqCVTCustomerInfo.SiteProvince = cvtCustomerInfomation.Rows[i]["省份"].ToString().Trim(); lnqCVTCustomerInfo.VehicleShelfNumber = strTemp; lnqCVTCustomerInfo.PY = UniversalFunction.GetPYWBCode(lnqCVTCustomerInfo.ClientName, "PY"); lnqCVTCustomerInfo.WB = UniversalFunction.GetPYWBCode(lnqCVTCustomerInfo.ClientName, "WB"); lnqCVTCustomerInfo.CarModelID = serverProductList.GetMotorcycleType( cvtCustomerInfomation.Rows[i]["车型"].ToString().Trim()); lnqCVTCustomerInfo.ProofNo = cvtCustomerInfomation.Rows[i]["三包凭证号"].ToString().Trim(); dataContext.YX_CVTCustomerInformation.InsertOnSubmit(lnqCVTCustomerInfo); } else { lnqCVTCustomerInfo = varData.Single(); lnqCVTCustomerInfo.SiteCity = cvtCustomerInfomation.Rows[i]["车辆所在地"].ToString().Trim(); lnqCVTCustomerInfo.DealerName = cvtCustomerInfomation.Rows[i]["经销商名称"].ToString().Trim(); lnqCVTCustomerInfo.SiteProvince = cvtCustomerInfomation.Rows[i]["省份"].ToString().Trim(); lnqCVTCustomerInfo.ClientName = cvtCustomerInfomation.Rows[i]["客户名称"].ToString().Trim(); lnqCVTCustomerInfo.SellDate = ServerTime.ConvertToDateTime(cvtCustomerInfomation.Rows[i]["销售日期"].ToString().Trim()); lnqCVTCustomerInfo.FullAddress = cvtCustomerInfomation.Rows[i]["详细地址"].ToString().Trim(); lnqCVTCustomerInfo.PhoneNumber = cvtCustomerInfomation.Rows[i]["联系电话"].ToString().Trim(); } dataContext.SubmitChanges(); strTemp = ""; } return(true); } catch (Exception ex) { error = ex.Message + ",车架号为[" + strTemp + "]" + intFlag; return(false); } }
/// <summary> /// 批量插入装车信息 /// </summary> /// <param name="loadingInfo">装车信息列表</param> /// <param name="error">出错时返回错误信息,无错时返回null</param> /// <returns>批量插入成功返回True,批量插入失败返回False</returns> public bool BatchInsertLoadingInfo(DataTable loadingInfo, out string error) { error = null; string strTemp = ""; try { DepotManagementDataContext dataContext = CommentParameter.DepotDataContext; ProductListServer serverProductList = new ProductListServer(); for (int i = 0; i < loadingInfo.Rows.Count; i++) { strTemp = loadingInfo.Rows[i]["车架号"].ToString().Trim(); if (strTemp == "") { continue; } YX_LoadingInfo lnqLoadingInfo = new YX_LoadingInfo(); int intGoodsID = serverProductList.GetProductGoodsID( loadingInfo.Rows[i]["CVT型号"].ToString().Trim(), 0, false); if (intGoodsID == 0) { error = "[CVT型号]不符合标准,车架号为[" + strTemp + "]"; return(false); } int intCarModle = serverProductList.GetMotorcycleType(loadingInfo.Rows[i]["车型号"].ToString().Trim()); if (intCarModle == 0) { error = "[车型号]不符合标准,车架号为[" + strTemp + "]"; return(false); } if (loadingInfo.Rows[i]["装车日期"].ToString().Trim() == "") { error = error + "[" + loadingInfo.Rows[i]["车架号"].ToString().Trim() + "]"; } lnqLoadingInfo.CarModelID = intCarModle; lnqLoadingInfo.CVTNumber = loadingInfo.Rows[i]["CVT编号"] == DBNull.Value ? "" : loadingInfo.Rows[i]["CVT编号"].ToString().Trim(); lnqLoadingInfo.Date = ServerTime.ConvertToDateTime( loadingInfo.Rows[i]["装车日期"].ToString()); lnqLoadingInfo.ProductID = intGoodsID; lnqLoadingInfo.Remark = loadingInfo.Rows[i]["备注"].ToString().Trim(); lnqLoadingInfo.VehicleShelfNumber = loadingInfo.Rows[i]["车架号"].ToString().Trim(); dataContext.YX_LoadingInfo.InsertOnSubmit(lnqLoadingInfo); strTemp = ""; } dataContext.SubmitChanges(); return(true); } catch (Exception ex) { error = ex.Message + ",车架号为[" + strTemp + "]"; return(false); } }