//处理ID,AUTOID,CCODE public retCode getCode(string cVouchType, int sl, string CardNumber, SqlTransaction tran) { //cVouchType:采购入库单 rd,其他入库单 re,销售发货单 DISPATCH //sl: 子表数量 //CardNumber:采购入库单 24,其他出库单 0302,销售发货单 01 retCode model = new retCode(); string sqlid = @"exec ZZp_sp_GetID_VS '" + cVouchType + "','" + sl + "','" + CardNumber + "'"; DataTable dtid = U8DataHelper.GetDataTable(sqlid, tran); if (dtid.Rows.Count > 0) { model.ID = int.Parse(dtid.Rows[0]["id"].ToString()); model.AUTOID = int.Parse(dtid.Rows[0]["autoid"].ToString()); model.CCODE = dtid.Rows[0]["ccode"].ToString(); } else { throw new Exception("获取U8 id 错误"); } return(model); }
//检查写入 current public void CurrentStockCheckAdd(string cinvcode, string cwhcode, string constr) { DataTable dt = U8DataHelper.GetDataTable(constr, @"select * from currentstock where cWhCode = '" + cwhcode + "' and cInvCode = '" + cinvcode + "'"); if (dt.Rows.Count < 1)//如果没有,写入 { DataTable dtitem = U8DataHelper.GetDataTable(constr, @"select * from SCM_Item where cinvcode = '" + cinvcode + "'"); CurrentStock m = new CurrentStock(); m.cWhCode = cwhcode; m.cInvCode = cinvcode; m.ItemId = int.Parse(dtitem.Rows[0]["Id"].ToString()); m.cBatch = ""; m.cVMIVenCode = ""; m.iSoType = 0; m.iSodid = ""; m.iQuantity = 0; m.iNum = 0; m.cFree1 = ""; m.cFree2 = ""; m.fOutQuantity = 0; m.fOutNum = 0; m.fInQuantity = 0; m.fInNum = 0; m.cFree3 = ""; m.cFree4 = ""; m.cFree5 = ""; m.cFree6 = ""; m.cFree7 = ""; m.cFree8 = ""; m.cFree9 = ""; m.cFree10 = ""; m.bStopFlag = false; m.fTransInQuantity = 0; m.fTransInNum = 0; m.fTransOutQuantity = 0; m.fTransOutNum = 0; m.fPlanQuantity = 0; m.fPlanNum = 0; m.fDisableQuantity = 0; m.fDisableNum = 0; m.fAvaQuantity = 0; m.fAvaNum = 0; m.BGSPSTOP = false; m.fStopQuantity = 0; m.fStopNum = 0; m.cCheckState = ""; m.iExpiratDateCalcu = 0; m.ipeqty = 0; m.ipenum = 0; new CurrentStockDal().Add2(m, constr); } }
//写入 scm public void SCM_ItemCheckAdd(string cinvcode, string constr) { DataTable dt = U8DataHelper.GetDataTable(constr, @"select * from SCM_Item where cinvcode = '" + cinvcode + "'"); if (dt.Rows.Count < 1)//如果没有,写入 { U8DataHelper.ExcuteNonQuery(constr, @"insert into SCM_Item(cInvCode, PartId,cFree1,cFree2,cFree3,cFree4,cFree5,cFree6,cFree7,cFree8,cFree9,cFree10) values( '" + cinvcode + "',0, '', '', '', '', '', '', '', '', '', '')"); } }
//翻译一些字段 //翻译采购入库 public List <MyRdRecords01> TransFieldMyRd01(List <MyRdRecords01> list, string constr) { string sql = ""; DataTable dt = new DataTable(); foreach (var i in list) { //存货编码 sql = @"select * from Inventory where cInvMnemCode = '" + i.goods_code + "'"; dt = U8DataHelper.GetDataTable(constr, sql); if (dt.Rows.Count == 0) { return(null); } i.goods_code = dt.Rows[0]["cInvCode"].ToString(); //客户 sql = @"select * from Vendor where cVenName = '" + i.supplier_name + "'"; dt = U8DataHelper.GetDataTable(constr, sql); if (dt.Rows.Count == 0) { return(null); } i.u8_cVenCode = dt.Rows[0]["cVenCode"].ToString(); //仓库 sql = @"select * from Warehouse where cWhName = '" + i.storage_name + "'"; dt = U8DataHelper.GetDataTable(constr, sql); if (dt.Rows.Count == 0) { return(null); } i.u8_cWhCode = dt.Rows[0]["cWhCode"].ToString(); //部门 sql = @"select * from Department where cDepName like '%" + i.dept_name + "%'"; dt = U8DataHelper.GetDataTable(constr, sql); if (dt.Rows.Count == 0) { return(null); } i.u8_cDepCode = dt.Rows[0]["cDepCode"].ToString(); } return(list); }
//处理库存 #region 库存增加 public void CurrentStockAdd(RdRecord01 mymodel, List <RdRecords01> mylist, SqlTransaction tran, string constr) { //处理 scm_item,currentstock foreach (var i in mylist) { SCM_ItemCheckAdd(i.cInvCode, constr); } foreach (var i in mylist) { CurrentStockCheckAdd(i.cInvCode, mymodel.cWhCode, constr); } //写入 现存量 CurrentStockDal dal_curr = new CurrentStockDal(); foreach (var i in mylist) { U8DataHelper.ExcuteNonQuery(@"update currentstock set iQuantity = iQuantity + " + i.iQuantity + @" where cInvCode = '" + i.cInvCode + "' and cWhCode = '" + mymodel.cWhCode + "'", tran); } }
//检测是不是已经结账 public retmsg CheckGL_mend(string date, string constr) { retmsg ret = new retmsg(); string year = date.Substring(0, 4); int month = int.Parse(date.Substring(5, 2)); try { string sql = @" select bflag_st from GL_mend where iyear = '" + year + @"' and iperiod = '" + month + @"'"; DataTable dt = U8DataHelper.GetDataTable(constr, sql); if (dt.Rows.Count == 0) { ret.flag = "error"; return(ret); } string bflag_st = dt.Rows[0][0].ToString(); if (bflag_st == "True") { ret.flag = "error"; ret.message = year + "年" + month + "月已经结账"; return(ret); } ret.flag = "ok"; return(ret); } catch (Exception ex) { ret.flag = "error"; ret.flag = ex.Message; return(ret); } }
//创建生成U8ID的存储过程 public void CreateProGetID(string constr, string accout) { string sql = @" CREATE PROCEDURE [dbo].[ZZp_sp_GetID_VS] ( @cVouchType nvarchar(50) =N'' , @iAmount int =0, @CardNumber varchar(10)=N'' ) as begin --获取远程号 SET NOCOUNT ON; declare @RemoteId varchar(2),@cAcc_Id varchar(10) , @iFatherId int, @iChildId int, @ccode varchar(50) EXEC Usp_BAS_GetRemoteId @RemoteId OUTPUT --获取UFDATA数据库ACC号 --SELECT @cAcc_Id=SUBSTRING(DB_NAME(dbid),8,3) FROM master.dbo.sysprocesses WHERE status='runnable' set @cAcc_Id = '" + accout + @"' exec sp_GetID @RemoteId,@cAcc_Id,@cVouchType,@iAmount,@iFatherId OUTPUT,@iChildId OUTPUT --select @iFatherId as iFatherId,@iChildId as iChildId --获取ccode declare @qz1 varchar(30),@qz2 varchar(30),@qz3 varchar(30) declare @cd1 int,@cd2 int,@cd3 int,@istartnumber int,@err int declare @gz1 varchar(30),@gz2 varchar(30),@gz3 varchar(30) declare @lsyj varchar(30),@lscd int,@lsgz varchar(30),@lsgz2 varchar(30),@inum varchar(30) declare @code1 varchar(30),@code2 varchar(30),@code3 varchar(30),@code4 varchar(30) SELECT @istartnumber=iStartNumber, @qz1 = Prefix1, @cd1 = Prefix1Len,@gz1 = Prefix1Rule,@qz2 = Prefix2, @cd2 = Prefix2Len, @gz2 = Prefix2Rule, @qz3 = Prefix3, @cd3 = Prefix3Len, @gz3 = Prefix3Rule, @lsyj = Glide, @lscd = GlideLen, @lsgz = GlideRule FROM VoucherNumber where CardNumber = @CardNumber SET @err = 0 --前缀1 if isnull(@qz1,'') <> '' begin if @qz1 = '手工输入' set @code1 = @gz1 if @qz1 = '日期' or @qz1 = '单据日期' begin if @gz1 = '年' set @code1 = convert(varchar(4),getdate(),112) if @gz1 = '年月' set @code1 = convert(varchar(6),getdate(),112) if @gz1 = '年月日' set @code1 = convert(varchar(8),getdate(),112) end if @qz1 = '远程号' set @code1 = @RemoteId end --前缀2 if isnull(@qz2,'') <> '' begin if @qz2 = '手工输入' set @code2 = @gz2 if @qz2 = '日期' or @qz2 = '单据日期' begin if @gz2 = '年' set @code2 = convert(varchar(4),getdate(),112) if @gz2 = '年月' set @code2 = convert(varchar(6),getdate(),112) if @gz2 = '年月日' set @code2 = convert(varchar(8),getdate(),112) end if @qz2 = '远程号' set @code2 = @RemoteId end --前缀3 if isnull(@qz3,'') <> '' begin if @qz1 = '手工输入' set @code3 = @gz3 if @qz3 = '日期' or @qz3 = '单据日期' begin if @gz3 = '年' set @code3 = convert(varchar(4),getdate(),112) if @gz3 = '年月' set @code3 = convert(varchar(6),getdate(),112) if @gz3 = '年月日' set @code3 = convert(varchar(8),getdate(),112) end if @qz3 = '远程号' set @code3 = @RemoteId end begin tran t1 --流水号 if isnull(@lsyj,'') = '' begin select @inum = cnumber from VoucherHistory where cardnumber = @CardNumber and isnull(ccontent,'')='' if ISNULL(@inum,'') = '' begin insert into VoucherHistory(cardnumber,cnumber) values(@CardNumber,@istartnumber) set @inum = convert(varchar(30),@istartnumber) if @@ERROR <> 0 begin set @err = @err + @@ERROR end end else begin set @inum = convert(int,@inum) + 1 update VoucherHistory set cnumber = @inum where cardnumber = @CardNumber if @@ERROR <> 0 begin set @err = @err + @@ERROR end end set @code4 = right('00000000000000' + @inum,@lscd) end else begin if @lsyj = '日期' or @lsyj = '单据日期' begin if @lsgz = '年' set @lsgz2 = convert(varchar(4),getdate(),112) if @lsgz = '月' set @lsgz2 = convert(varchar(6),getdate(),112) if @lsgz = '日' set @lsgz2 = convert(varchar(8),getdate(),112) end select @inum = cnumber from VoucherHistory where cardnumber = @CardNumber and (cContent='日期' or cContent='单据日期') and cSeed= @lsgz2 if ISNULL(@inum,'') = '' begin insert into VoucherHistory(cardnumber,cContent,cnumber,cSeed,cContentRule) values(@CardNumber,@lsyj,@istartnumber,@lsgz2,@lsgz) if @@ERROR <> 0 begin set @err = @err + @@ERROR end set @inum = convert(varchar(30),@istartnumber) end else begin set @inum = convert(int,@inum) + 1 update VoucherHistory set cnumber = @inum where cardnumber = @CardNumber and cSeed= @lsgz2 if @@ERROR <> 0 begin set @err = @err + @@ERROR end end set @code4 = right('00000000000000' + @inum,@lscd) end set @ccode = isnull(@code1,'') + isnull(@code2,'') + isnull(@code3,'') + isnull(@code4,'') select @iFatherId id ,@iChildId autoid,@ccode ccode if @err = 0 begin commit tran t1 return 1 end else begin rollback tran t1 return 0 end end"; U8DataHelper.ExcuteNonQuery(constr, sql); }
//组建数据库连接字符串 public retmsg getAccout(string ip, string accout) { retmsg ret = new retmsg(); string str = ""; if (string.IsNullOrEmpty(ip)) { ret.flag = "error"; ret.message = "无效ip"; return(ret); } if (string.IsNullOrEmpty(accout)) { ret.flag = "error"; ret.message = "无效账套号"; return(ret); } Dictionary <string, string> dic = new Dictionary <string, string>(); dic.Add("ip", ip); var list = new Dal.BaseData.t_serverDal(dic, "ip").ToList(); if (list.Count == 0) { ret.flag = "error"; ret.message = "无效ip"; return(ret); } //获取数据库账套的年度 DataTable dtyear = DataHelper.GetDataTable(@"select cAcc_Id,iyear from ufsystem..UA_Account where cAcc_Id = '" + accout + @"' order by iYear desc"); if (dtyear.Rows.Count < 1) { ret.flag = "error"; ret.message = "系统没有查找到该账套号,请检查"; return(ret); } string year = dtyear.Rows[0]["iyear"].ToString(); string uid = list.First().username; string pwd = list.First().pwd; string db = "UFDATA_" + accout + "_" + year; str = "Database=" + db + ";Server=" + ip + ";uid=" + uid + ";pwd=" + pwd + ";"; //检测是不是有获取U8ID的存储过程,没有就创建 string sql = @"select 1 from sys.all_objects where [type]='p' and [name]='ZZp_sp_GetID_VS'"; DataTable dt = U8DataHelper.GetDataTable(str, sql); if (dt.Rows.Count == 0) { CreateProGetID(str, accout); } ret.flag = "ok"; ret.message = str; return(ret); }
public void Add2(dynamic obj, string constr) { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into CurrentStock("); strSql.Append("iNum,cFree1,cFree2,fOutQuantity,fOutNum,fInQuantity,fInNum,cFree3,cFree4,cFree5,cWhCode,cFree6,cFree7,cFree8,cFree9,cFree10,dVDate,bStopFlag,fTransInQuantity,dMdate,fTransInNum,cInvCode,fTransOutQuantity,fTransOutNum,fPlanQuantity,fPlanNum,fDisableQuantity,fDisableNum,fAvaQuantity,fAvaNum,iMassDate,BGSPSTOP,ItemId,cMassUnit,fStopQuantity,fStopNum,dLastCheckDate,cCheckState,dLastYearCheckDate,iExpiratDateCalcu,cExpirationdate,dExpirationdate,ipeqty,cBatch,ipenum,cVMIVenCode,iSoType,iSodid,iQuantity"); strSql.Append(") values ("); strSql.Append("@iNum,@cFree1,@cFree2,@fOutQuantity,@fOutNum,@fInQuantity,@fInNum,@cFree3,@cFree4,@cFree5,@cWhCode,@cFree6,@cFree7,@cFree8,@cFree9,@cFree10,@dVDate,@bStopFlag,@fTransInQuantity,@dMdate,@fTransInNum,@cInvCode,@fTransOutQuantity,@fTransOutNum,@fPlanQuantity,@fPlanNum,@fDisableQuantity,@fDisableNum,@fAvaQuantity,@fAvaNum,@iMassDate,@BGSPSTOP,@ItemId,@cMassUnit,@fStopQuantity,@fStopNum,@dLastCheckDate,@cCheckState,@dLastYearCheckDate,@iExpiratDateCalcu,@cExpirationdate,@dExpirationdate,@ipeqty,@cBatch,@ipenum,@cVMIVenCode,@iSoType,@iSodid,@iQuantity"); strSql.Append(") "); SqlParameter[] parameters = { new SqlParameter("@iNum", SqlDbType.Decimal, 13), new SqlParameter("@cFree1", SqlDbType.NVarChar, 20), new SqlParameter("@cFree2", SqlDbType.NVarChar, 20), new SqlParameter("@fOutQuantity", SqlDbType.Decimal, 13), new SqlParameter("@fOutNum", SqlDbType.Decimal, 13), new SqlParameter("@fInQuantity", SqlDbType.Decimal, 13), new SqlParameter("@fInNum", SqlDbType.Decimal, 13), new SqlParameter("@cFree3", SqlDbType.NVarChar, 20), new SqlParameter("@cFree4", SqlDbType.NVarChar, 20), new SqlParameter("@cFree5", SqlDbType.NVarChar, 20), new SqlParameter("@cWhCode", SqlDbType.NVarChar, 10), new SqlParameter("@cFree6", SqlDbType.NVarChar, 20), new SqlParameter("@cFree7", SqlDbType.NVarChar, 20), new SqlParameter("@cFree8", SqlDbType.NVarChar, 20), new SqlParameter("@cFree9", SqlDbType.NVarChar, 20), new SqlParameter("@cFree10", SqlDbType.NVarChar, 20), new SqlParameter("@dVDate", SqlDbType.DateTime), new SqlParameter("@bStopFlag", SqlDbType.Bit, 1), new SqlParameter("@fTransInQuantity", SqlDbType.Decimal, 13), new SqlParameter("@dMdate", SqlDbType.DateTime), new SqlParameter("@fTransInNum", SqlDbType.Decimal, 13), new SqlParameter("@cInvCode", SqlDbType.NVarChar, 60), new SqlParameter("@fTransOutQuantity", SqlDbType.Decimal, 13), new SqlParameter("@fTransOutNum", SqlDbType.Decimal, 13), new SqlParameter("@fPlanQuantity", SqlDbType.Decimal, 13), new SqlParameter("@fPlanNum", SqlDbType.Decimal, 13), new SqlParameter("@fDisableQuantity", SqlDbType.Decimal, 13), new SqlParameter("@fDisableNum", SqlDbType.Decimal, 13), new SqlParameter("@fAvaQuantity", SqlDbType.Decimal, 13), new SqlParameter("@fAvaNum", SqlDbType.Decimal, 13), new SqlParameter("@iMassDate", SqlDbType.Int, 4), new SqlParameter("@BGSPSTOP", SqlDbType.Bit, 1), new SqlParameter("@ItemId", SqlDbType.Int, 4), new SqlParameter("@cMassUnit", SqlDbType.SmallInt, 2), new SqlParameter("@fStopQuantity", SqlDbType.Decimal, 13), new SqlParameter("@fStopNum", SqlDbType.Decimal, 13), new SqlParameter("@dLastCheckDate", SqlDbType.DateTime), new SqlParameter("@cCheckState", SqlDbType.NVarChar, 4), new SqlParameter("@dLastYearCheckDate", SqlDbType.DateTime), new SqlParameter("@iExpiratDateCalcu", SqlDbType.SmallInt, 2), new SqlParameter("@cExpirationdate", SqlDbType.VarChar, 10), new SqlParameter("@dExpirationdate", SqlDbType.DateTime), new SqlParameter("@ipeqty", SqlDbType.Decimal, 17), new SqlParameter("@cBatch", SqlDbType.NVarChar, 60), new SqlParameter("@ipenum", SqlDbType.Decimal, 17), new SqlParameter("@cVMIVenCode", SqlDbType.NVarChar, 20), new SqlParameter("@iSoType", SqlDbType.Int, 4), new SqlParameter("@iSodid", SqlDbType.NVarChar, 40), new SqlParameter("@iQuantity", SqlDbType.Decimal, 13) }; CurrentStock model = (CurrentStock)obj; parameters[0].Value = SqlNull(model.iNum); parameters[1].Value = SqlNull(model.cFree1); parameters[2].Value = SqlNull(model.cFree2); parameters[3].Value = SqlNull(model.fOutQuantity); parameters[4].Value = SqlNull(model.fOutNum); parameters[5].Value = SqlNull(model.fInQuantity); parameters[6].Value = SqlNull(model.fInNum); parameters[7].Value = SqlNull(model.cFree3); parameters[8].Value = SqlNull(model.cFree4); parameters[9].Value = SqlNull(model.cFree5); parameters[10].Value = SqlNull(model.cWhCode); parameters[11].Value = SqlNull(model.cFree6); parameters[12].Value = SqlNull(model.cFree7); parameters[13].Value = SqlNull(model.cFree8); parameters[14].Value = SqlNull(model.cFree9); parameters[15].Value = SqlNull(model.cFree10); parameters[16].Value = SqlNull(model.dVDate); parameters[17].Value = SqlNull(model.bStopFlag); parameters[18].Value = SqlNull(model.fTransInQuantity); parameters[19].Value = SqlNull(model.dMdate); parameters[20].Value = SqlNull(model.fTransInNum); parameters[21].Value = SqlNull(model.cInvCode); parameters[22].Value = SqlNull(model.fTransOutQuantity); parameters[23].Value = SqlNull(model.fTransOutNum); parameters[24].Value = SqlNull(model.fPlanQuantity); parameters[25].Value = SqlNull(model.fPlanNum); parameters[26].Value = SqlNull(model.fDisableQuantity); parameters[27].Value = SqlNull(model.fDisableNum); parameters[28].Value = SqlNull(model.fAvaQuantity); parameters[29].Value = SqlNull(model.fAvaNum); parameters[30].Value = SqlNull(model.iMassDate); parameters[31].Value = SqlNull(model.BGSPSTOP); parameters[32].Value = SqlNull(model.ItemId); parameters[33].Value = SqlNull(model.cMassUnit); parameters[34].Value = SqlNull(model.fStopQuantity); parameters[35].Value = SqlNull(model.fStopNum); parameters[36].Value = SqlNull(model.dLastCheckDate); parameters[37].Value = SqlNull(model.cCheckState); parameters[38].Value = SqlNull(model.dLastYearCheckDate); parameters[39].Value = SqlNull(model.iExpiratDateCalcu); parameters[40].Value = SqlNull(model.cExpirationdate); parameters[41].Value = SqlNull(model.dExpirationdate); parameters[42].Value = SqlNull(model.ipeqty); parameters[43].Value = SqlNull(model.cBatch); parameters[44].Value = SqlNull(model.ipenum); parameters[45].Value = SqlNull(model.cVMIVenCode); parameters[46].Value = SqlNull(model.iSoType); parameters[47].Value = SqlNull(model.iSodid); parameters[48].Value = SqlNull(model.iQuantity); U8DataHelper.ExcuteNonQuery(constr, strSql.ToString(), parameters, false); }
//检测采购入库单参数 public retmsg CheckMyRd01(MyRdRecord01 model, List <MyRdRecords01> list, string constr) { retmsg ret = new retmsg(); string sql = ""; DataTable dt = new DataTable(); //主表 if (string.IsNullOrEmpty(model.create_by_name)) { ret.flag = "error"; ret.message = "创建者名称为空"; return(ret); } //子表 foreach (var i in list) { if (string.IsNullOrEmpty(i.goods_code)) { ret.flag = "error"; ret.message = "商品编码为空"; return(ret); } else { //检测是不是存在存货档案中 sql = @"select * from Inventory where cInvMnemCode = '" + i.goods_code + "'";//cInvMnemCode 助记码 dt = U8DataHelper.GetDataTable(constr, sql); if (dt.Rows.Count == 0) { ret.flag = "error"; ret.message = "存货档案中查询不到" + i.goods_code; return(ret); } } if (string.IsNullOrEmpty(i.supplier_name)) { ret.flag = "error"; ret.message = "供应商名称为空"; return(ret); } else { //检测是不是存在供应商档案中 sql = @"select * from Vendor where cVenName = '" + i.supplier_name + "'";//cVenMnemCode 助记码 dt = U8DataHelper.GetDataTable(constr, sql); if (dt.Rows.Count == 0) { ret.flag = "error"; ret.message = "供应商档案中查询不到" + i.supplier_name; return(ret); } } if (string.IsNullOrEmpty(i.storage_name)) { ret.flag = "error"; ret.message = "仓库名称为空"; return(ret); } else { //检测是不是存在仓库档案中 sql = @"select * from Warehouse where cWhName = '" + i.storage_name + "'"; dt = U8DataHelper.GetDataTable(constr, sql); if (dt.Rows.Count == 0) { ret.flag = "error"; ret.message = "仓库档案中查询不到" + i.storage_name; return(ret); } } if (string.IsNullOrEmpty(i.dept_name)) { ret.flag = "error"; ret.message = "部门名称为空"; return(ret); } else { //检测是不是存在仓库档案中 部门名称要模糊查询,20190525 sql = @"select * from Department where cDepName like '%" + i.dept_name + "%'"; dt = U8DataHelper.GetDataTable(constr, sql); if (dt.Rows.Count == 0) { ret.flag = "error"; ret.message = "部门档案中模糊查询不到" + i.dept_name; return(ret); } } } return(ret); }