//处理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); }
//检测是不是已经结账 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); } }
//组建数据库连接字符串 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 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); }