//由盘存明细数量生成盘点明细 并插入单据 及单据明细 public static void Insert_dj_djmx_kcmx(Guid djid_pd, RelationalDatabase db, out Guid djid) { try { int day = -90; //盘盈逆推天数 string pcglfs = "0"; //批次管理方式 0-先进先出 1-效期优先 pcglfs = (new SystemCfg(8051)).Config; string ssql = string.Format(@" select * from yf_pd where id='{0}'", djid_pd); DataTable tbTemp = db.GetDataTable(ssql); if (tbTemp.Rows.Count <= 0) { throw new Exception("找不到盘存录入表"); } int deptid = Convert.ToInt32(tbTemp.Rows[0]["deptid"]);//科室id int uid = Convert.ToInt32(tbTemp.Rows[0]["djy"]); int jgbm = Convert.ToInt32(tbTemp.Rows[0]["jgbm"]); bool byk = YpConfig.是否药库(deptid, db);//是否药库 string strTbKcph = "yk_kcph"; string strTbKcmx = "yk_kcmx"; if (!byk) { strTbKcph = "yf_kcph"; strTbKcmx = "yf_kcmx"; } ssql = string.Format(@" select b.kcl,a.zcs*b.dwbl/a.ydwbl zcsl,a.pcs*b.dwbl/a.ydwbl pcsl, a.cjid,c.ggid,b.zxdw zxdw, dbo.fun_yp_ypdw(b.zxdw) ypdw,b.dwbl dwbl, c.shh,c.yppm,c.ypspm,c.ypgg, c.s_sccj sccj,c.pfj/b.dwbl pfj,c.lsj/b.dwbl lsj from YF_PDMX_KCMX a inner join {0} b on a.cjid=b.cjid inner join vi_yp_ypcd c on a.cjid=c.cjid inner join YF_PD d on d.ID=a.djid and d.deptid=b.deptid where a.djid='{1}' and d.deptid={2}", strTbKcmx, djid_pd, deptid); DataTable tb = db.GetDataTable(ssql); DataRow[] rows = tb.Select(" zcsl<>pcsl "); //DataRow[] rowsTemp = tb.Select(" ( zcsl-pcsl-kcl>0)");//帐存数量-盘存数量-库存量 //if ( rowsTemp.Length > 0 ) //{ // throw new Exception( "库存量小于盘亏数量" ); //} object objZcsl = tb.Compute("sum(zcsl)", ""); object objPcsl = tb.Compute("sum(pcsl)", ""); object objKcl = tb.Compute("sum(kcl)", ""); decimal yks = Convert.ToDecimal(Convertor.IsNull(objZcsl, "0")) - Convert.ToDecimal(Convertor.IsNull(objPcsl, "0")); decimal _kcl = Convert.ToDecimal(Convertor.IsNull(objKcl, "0")); if (yks < 0 && Math.Abs(yks) > _kcl) { throw new Exception("盘亏数大于当前库存量"); } #region 保存盘点盈亏单据头表 Guid _djid; string _err_text = ""; int _err_code = 0; string ywlx = "008"; DateTime serverTime = Convert.ToDateTime(db.GetDataResult(db.GetServerTimeString()).ToString()); decimal sumpfje = 0; //批发金额 decimal sumlsje = 0; //零售金额 decimal sumjhje = 0; //进货金额 long djh = Yp.SeekNewDjh(ywlx, deptid, db); //单据流水号 string sdjh = Yp.SeekNewDjh_Str(ywlx, deptid, db); if (byk) { Yk_dj_djmx.SaveDJ(Guid.Empty, djh, deptid, ywlx, deptid, 0, serverTime.ToShortDateString(), uid, serverTime.ToShortDateString(), serverTime.ToString(), "", "", "", "", 0, 0, sumjhje, sumpfje, sumlsje, sdjh, out _djid, out _err_code, out _err_text, jgbm, db); if (_err_code != 0) { throw new Exception("插入盈亏单据明细失败!" + _err_text); } } else { YF_DJ_DJMX.SaveDJ(Guid.Empty, djh, deptid, ywlx, deptid, 0, serverTime.ToShortDateString(), uid, serverTime.ToShortDateString(), serverTime.ToString(), "", "", "", "", 0, 0, sumjhje, sumpfje, sumlsje, out _djid, out _err_code, out _err_text, jgbm, db); if (_err_code != 0) { throw new Exception("插入盈亏单据明细失败!" + _err_text); } } #endregion #region 保存盘点盈亏单据明细表 for (int i = 0; i < rows.Length; i++) { DataRow row = rows[i]; string yppm = row["yppm"].ToString().Trim(); //品名 string ypspm = row["ypspm"].ToString().Trim(); //商品名 string ypgg = row["ypgg"].ToString().Trim(); //规格 int ggid = Convert.ToInt32(row["ggid"]); //ggid string sccj = row["sccj"].ToString(); //sccj decimal jhj = 0; //进货价 decimal pfj = Convert.ToDecimal(row["pfj"]); //批发价 decimal lsj = Convert.ToDecimal(row["lsj"]); //零售价 string ypph = ""; //批号 string ypxq = ""; //效期 string yppch = ""; //批次号 Guid kcid = Guid.Empty; //kcid decimal zcsl = Convert.ToDecimal(row["zcsl"]); //帐存数量 decimal pcsl = Convert.ToDecimal(row["pcsl"]); //盘存数量 int cjid = Convert.ToInt32(row["cjid"]); decimal slcz = zcsl - pcsl; int pxxh = 0; //排序序号 if (slcz > 0) //数量差值 大于0 帐存数>盘存数 盘亏 { #region 盘亏 if (pcglfs == "0")//先进先出 { ssql = string.Format(" select kcl,jhj,ypph,ypxq,yppch, id kcid from {0} where cjid={1} and deptid= {2} and kcl>0 order by djsj asc", strTbKcph, cjid, deptid); } if (pcglfs == "1") { ssql = string.Format(" select kcl,jhj,ypph,ypxq,yppch, id kcid from {0} where cjid={1} and deptid= {2} and kcl>0 order by ypxq asc", strTbKcph, cjid, deptid); } DataTable tbkcph = db.GetDataTable(ssql); for (int j = 0; j < tbkcph.Rows.Count; j++) { DataRow row1 = tbkcph.Rows[j]; decimal kcl = Convert.ToDecimal(row1["kcl"]); // decimal cks = 0; if (kcl >= slcz) { cks = slcz; slcz = 0; } else { cks = kcl; slcz -= cks; } ypph = row1["ypph"].ToString(); ypxq = row1["ypxq"].ToString(); yppch = row1["yppch"].ToString(); kcid = new Guid(row1["kcid"].ToString()); jhj = Convert.ToDecimal(row1["jhj"]); #region 保存单据明细 if (byk) { Yk_dj_djmx.SaveDJMX(Guid.Empty, _djid, cjid, 0, row["shh"].ToString(), yppm, ypspm, ypgg, sccj, ypph, ypxq, 0, 0, Convert.ToDecimal(cks * (-1)), row["ypdw"].ToString(), Convert.ToInt32(row["zxdw"]), //zxdw Convert.ToInt32(row["dwbl"]), //dwbl jhj, pfj, lsj, Convert.ToDecimal(jhj * cks * (-1)), Convert.ToDecimal(pfj * cks * (-1)), Convert.ToDecimal(lsj * cks * (-1)), djh, deptid, ywlx, "",//备注 "", 0, out _err_code, out _err_text, db, 0, yppch, kcid); if (_err_code != 0) { throw new Exception("插入单据明细失败!"); } } else { YF_DJ_DJMX.SaveDJMX(Guid.Empty, _djid, cjid, 0, row["shh"].ToString(), yppm, ypspm, ypgg, sccj, ypph, ypxq, 0, 0, Convert.ToDecimal(cks * (-1)), row["ypdw"].ToString(), Convert.ToInt32(row["zxdw"]),// Convert.ToInt32(row["dwbl"]), jhj, pfj, lsj, Convert.ToDecimal(jhj * cks * (-1)), Convert.ToDecimal(pfj * cks * (-1)), Convert.ToDecimal(lsj * cks * (-1)), djh, deptid, ywlx, "", "", out _err_code, out _err_text, db, pxxh, yppch, kcid); if (_err_code != 0) { throw new Exception("插入单据明细失败!"); } } #endregion sumjhje += Math.Round(Convert.ToDecimal(cks * jhj * (-1)), 3); sumpfje += Math.Round(Convert.ToDecimal(cks * pfj * (-1)), 3); sumlsje += Math.Round(Convert.ToDecimal(cks * lsj * (-1)), 3); if (slcz == 0) { break; } } if (slcz > 0) { throw new Exception("库存量小于盘亏数量"); } #endregion } else//帐存数<盘存数 盘盈 { #region 盘盈 string strOrder = ""; if (pcglfs == "0") { strOrder = " order by djsj desc "; } else { strOrder = " order by ypxq desc "; } string vi_name = "vi_yf_kcph"; string strYwlx = " ('001','002','003','004','009','015','016','019') "; string tbYwlx = "yf_ywlx"; string tbDjmx = "yf_djmx"; //药房业务:001-采购入库 002-采购退货 003-药品调出 004-药房退库 009-期初录入 // 015-药品调入 016-药库出库单 019-其他入库 if (byk) { vi_name = "vi_yk_kcph"; tbYwlx = "yk_ywlx"; tbDjmx = "yk_djmx"; strYwlx = " ('001','002') "; //药库业务: 001-采购入库 002-药品退货 } ssql = string.Format(@" select * from ( select sum(a.YPSL*m.DWBL/a.YDWBL*(case y.YWFX when '+' then 1 else -1 end) )-sum(m.kcl) kcl, m.jhj,m.ypph,m.ypxq,m.yppch,m.kcid,m.djsj_kc djsj from {6} a inner join {7} y on a.ywlx=y.YWLX inner join {3} m on m.cjid=a.CJID and a.KCID=m.KCID and a.deptid=m.deptid where a.YWLX in {5} and m.djsj_kc>DATEADD(day,{0},GETDATE()) and m.cjid={1} and m.deptid={2} group by m.jhj,m.ypph,m.ypxq,m.yppch,m.kcid,m.djsj_kc ,m.ypph,m.ypxq,m.yppch ) z where z.kcl > 0 {4}", day, cjid, deptid, vi_name, strOrder, strYwlx, tbDjmx, tbYwlx); DataTable tb_py = db.GetDataTable(ssql); decimal sysl = Convert.ToDecimal(slcz * (-1)); for (int j = 0; j < tb_py.Rows.Count; j++) { DataRow row_py = tb_py.Rows[j]; decimal kcl = Convert.ToDecimal(row_py["kcl"]); decimal cks = 0; ypph = row_py["ypph"].ToString(); yppch = row_py["yppch"].ToString(); ypxq = row_py["ypxq"].ToString(); kcid = new Guid(row_py["kcid"].ToString()); jhj = Convert.ToDecimal(row_py["jhj"]); if (kcl >= sysl) { cks = sysl; sysl -= cks; } else { cks = kcl; sysl -= kcl; } decimal sl = cks; #region 保存单据明细 if (byk) { Yk_dj_djmx.SaveDJMX(Guid.Empty, _djid, cjid, 0, row["shh"].ToString(), yppm, ypspm, ypgg, sccj, ypph, ypxq, 0, 0, sl, row["ypdw"].ToString(), Convert.ToInt32(row["zxdw"]), //zxdw Convert.ToInt32(row["dwbl"]), //ypdw jhj, pfj, lsj, Convert.ToDecimal(jhj * sl), Convert.ToDecimal(pfj * sl), Convert.ToDecimal(lsj * sl), djh, deptid, ywlx, "",//备注 "", 0, out _err_code, out _err_text, db, 0, yppch, kcid); } else { YF_DJ_DJMX.SaveDJMX(Guid.Empty, _djid, cjid, 0, row["shh"].ToString(), yppm, ypspm, ypgg, sccj, ypph, ypxq, 0, 0, sl, row["ypdw"].ToString(), Convert.ToInt32(row["zxdw"]),// Convert.ToInt32(row["dwbl"]), jhj, pfj, lsj, Convert.ToDecimal(jhj * sl), Convert.ToDecimal(pfj * sl), Convert.ToDecimal(lsj * sl), djh, deptid, ywlx, "", "", out _err_code, out _err_text, db, pxxh, yppch, kcid); } #endregion sumjhje += Math.Round(Convert.ToDecimal(jhj * sl), 3); sumpfje += Math.Round(Convert.ToDecimal(pfj * sl), 3); sumlsje += Math.Round(Convert.ToDecimal(lsj * sl), 3); ypph = row_py["ypph"].ToString(); yppch = row_py["yppch"].ToString(); ypxq = row_py["ypxq"].ToString(); kcid = new Guid(row_py["kcid"].ToString()); jhj = Convert.ToDecimal(row_py["jhj"]); if (sysl == 0) { break; } } if (sysl > 0) //存在未能分配的记录,用最新的批次 { ssql = string.Format(@" select top 1 a.kcl,a.jhj,a.ypph,a.ypxq,a.yppch,a.id kcid from {0} a where a.cjid={1} and a.deptid={2} {3} ", strTbKcph, cjid, deptid, strOrder); DataTable tb_sysl = db.GetDataTable(ssql); DataRow row_py = tb_sysl.Rows[0]; ypph = row_py["ypph"].ToString(); yppch = row_py["yppch"].ToString(); ypxq = row_py["ypxq"].ToString(); kcid = new Guid(row_py["kcid"].ToString()); jhj = Convert.ToDecimal(row_py["jhj"]); decimal sl = Convert.ToDecimal(sysl); #region 保存单据明细 if (byk) { Yk_dj_djmx.SaveDJMX(Guid.Empty, _djid, cjid, 0, row["shh"].ToString(), yppm, ypspm, ypgg, sccj, ypph, ypxq, 0, 0, sl, row["ypdw"].ToString(), Convert.ToInt32(row["zxdw"]), //zxdw Convert.ToInt32(row["dwbl"]), //ypdw jhj, pfj, lsj, Convert.ToDecimal(jhj * sl), Convert.ToDecimal(pfj * sl), Convert.ToDecimal(lsj * sl), djh, deptid, ywlx, "",//备注 "", 0, out _err_code, out _err_text, db, 0, yppch, kcid); } else { YF_DJ_DJMX.SaveDJMX(Guid.Empty, _djid, cjid, 0, row["shh"].ToString(), yppm, ypspm, ypgg, sccj, ypph, ypxq, 0, 0, sl, row["ypdw"].ToString(), Convert.ToInt32(row["zxdw"]),// Convert.ToInt32(row["dwbl"]), jhj, pfj, lsj, Convert.ToDecimal(jhj * sl), Convert.ToDecimal(pfj * sl), Convert.ToDecimal(lsj * sl), djh, deptid, ywlx, "", "", out _err_code, out _err_text, db, pxxh, yppch, kcid); } #endregion sumjhje += Math.Round(Convert.ToDecimal(jhj * sl), 3); sumpfje += Math.Round(Convert.ToDecimal(pfj * sl), 3); sumlsje += Math.Round(Convert.ToDecimal(lsj * sl), 3); } #region // if (pcglfs == "0")//先进先出 盘盈到最新的批次上 // { // } // else //效期优先 盘盈到效期最大的批次上 // { // ssql = string.Format(@" select top 1 a.kcl,a.jhj,a.ypph,a.ypxq,a.yppch,a.id kcid from {0} a // where a.cjid={1} and a.deptid={2} order by ypxq desc ", strTbKcph, cjid, deptid); // } // DataTable tbkcph = db.GetDataTable(ssql); // if (tbkcph.Rows.Count <= 0) // { // throw new Exception("找不到库存批次记录!"); // } // DataRow row1 = tbkcph.Rows[0]; // decimal cks = slcz; // ypph = row1["ypph"].ToString(); // ypxq = row1["ypxq"].ToString(); // yppch = row1["yppch"].ToString(); // kcid = new Guid(row1["kcid"].ToString()); // jhj = Convert.ToDecimal(row1["jhj"]); // #region 保存单据明细 // if (byk) // { // Yk_dj_djmx.SaveDJMX(Guid.Empty, _djid, cjid, 0, // row["shh"].ToString(), // yppm, ypspm, ypgg, sccj, // ypph, ypxq, 0, 0, // Convert.ToDecimal(cks*(-1)), // row["ypdw"].ToString(), // Convert.ToInt32(row["zxdw"]),//zxdw // Convert.ToInt32(row["dwbl"]),//ypdw // jhj, // pfj, // lsj, // Convert.ToDecimal(jhj * cks*(-1)), // Convert.ToDecimal(pfj * cks*(-1)), // Convert.ToDecimal(lsj * cks*(-1)), // djh, deptid, // ywlx, // "",//备注 // "", // out _err_code, // out _err_text, // db, 0, yppch, kcid); // } // else // { // YF_DJ_DJMX.SaveDJMX(Guid.Empty, _djid, cjid, 0, // row["shh"].ToString(), // yppm, ypspm, ypgg, sccj, // ypph, ypxq, 0, 0, // Convert.ToDecimal(cks*(-1)), // row["ypdw"].ToString(), // Convert.ToInt32(row["zxdw"]),// // Convert.ToInt32(row["dwbl"]) , // jhj, // pfj, // lsj, // Convert.ToDecimal(jhj * cks * (-1)), // Convert.ToDecimal(pfj * cks * (-1)), // Convert.ToDecimal(lsj * cks * (-1)), // djh, // deptid, ywlx, // "", // "", // out _err_code, // out _err_text, db, // pxxh, // yppch, // kcid); // } // #endregion // sumjhje += Math.Round(Convert.ToDecimal(cks * jhj*(-1)), 3); // sumpfje += Math.Round(Convert.ToDecimal(cks * pfj*(-1)), 3); // sumlsje += Math.Round(Convert.ToDecimal(cks * lsj*(-1)), 3); #endregion #endregion } } #endregion #region 回填盘点盈亏单据头表金额 if (byk) { ssql = string.Format(" update yk_dj set sumjhje={0} where id='{1}'", sumjhje, _djid); } else { ssql = string.Format(" update yf_dj set sumjhje={0} where id='{1}'", sumjhje, _djid); } if (db.DoCommand(ssql) <= 0) { throw new Exception("更新进货金额失败!"); } #endregion djid = _djid; } catch (Exception err) { throw new Exception(err.ToString()); } }