public static void UpdateKcxq(int cjid, string ypxq, string ypph, long deptid, RelationalDatabase _DataBase)
{
string table_kcph = Yp.Seek_kcph_table(deptid, _DataBase);
string ssql = "update " + table_kcph + " set ypxq='" + ypxq + "' where cjid=" + cjid + " and deptid=" + deptid + " and ypph='" + ypph + "'";
int nrow = _DataBase.DoCommand(ssql);
if (nrow != 1)
{
throw new System.Exception("错误，数据影响了多行或零行，请和管理员联系");
}
}
//根据数量自动分配批号库存 注意拆零的处理 2014-06-06 添加业务类型处理
public static DataTable FpKcph(int cjid, decimal sl, string zxdw, int dwbl, int deptid, RelationalDatabase db, string ywlx)
{
string t_kcph = Yp.Seek_kcph_table(Convert.ToInt32(deptid), db);
bool byk = false;
if (t_kcph.Trim() == "yk_kcph")
{
byk = true;
}
else
{
if (t_kcph.Trim() == "yf_kcph")
{
byk = false;
}
else
{
throw new Exception("非药房药库！");
}
}
string ssql = string.Format(@" select
jgbm,ggid,cjid,kwid,ypph,
ypxq,
cast(jhj/{3} as decimal(15,4) ) jhj ,
cast(kcl*{3}/dwbl as decimal(15,3) ) kcl ,
{4} zxdw,
{3} dwbl,
djsj,bdelete,deptid,ykbdelete,yppch,
id kcid,rkdh
from {0} where cjid ={1} and kcl>0 and deptid={2} ", t_kcph, cjid, deptid, dwbl, zxdw); //库存量大于0的批次库存
if (t_kcph.Trim() == "yk_kcph")
{
ssql += " and (bdelete=0 or (bdelete=1 and kcl<>0) )";
}
if (t_kcph.Trim() == "yf_kcph")
{
ssql += " and (bdelete=0 or (bdelete=1 and kcl<>0) )";
}
if (byk)
{
switch (ywlx)
{
case "003":
ssql += " and bdelete = 0";
break;
}
}
//modify by jchl
SystemCfg cfg = new SystemCfg(8051);//批号排序及自动分配批号库存规则，0-先进先出 1-按效期先出
ssql += cfg.Config.Trim().Equals("0") ? " order by DJSJ asc,yppch asc" : " order by ypxq asc";
DataTable tb_kcph = db.GetDataTable(ssql);
if (tb_kcph.Rows.Count <= 0)
{
throw new Exception("找不到批次库存记录！");
}
//分配批号库存
List <DataRow> lstRow = new List <DataRow>();
for (int i = 0; i < tb_kcph.Rows.Count; i++)
{
decimal _sl = Convert.ToDecimal(tb_kcph.Rows[i]["kcl"]); //批次库存数量
if (sl > 0 && _sl > 0)
{
if (_sl > sl)
{
tb_kcph.Rows[i]["kcl"] = sl;
sl = 0;
}
else
{
sl -= _sl;
}
}
else
{
lstRow.Add(tb_kcph.Rows[i]);
}
}
foreach (DataRow row in lstRow)
{
tb_kcph.Rows.Remove(row);
}
return(tb_kcph);
}
public static DataTable SelectDmcx(string ypdm, int yplx, long deptid, RelationalDatabase _DataBase)
{
DataTable tb;
string ssql = "select 0 序号,yppm 品名,ypgg 规格,s_sccj 厂家," +
" pfj 批发价,lsj 零售价,dbo.fun_yp_kwmc(b.ggid,deptid) 库位,ypph 批号,ypxq 效期," +
" kcl 库存量,dbo.fun_yp_ypdw(zxdw) 单位,cast(round(pfj*kcl/dwbl,2) as decimal(15,2)) 批发金额," +
" cast(round(lsj*kcl/dwbl,2) as decimal(15,2)) 零售金额,a.cjid,shh 货号 from " + Yp.Seek_kcph_table(deptid, _DataBase) + " a,vi_yp_ypcd b " +////yk_kcph 修改成 " + Yp.Seek_kcph_table(deptid, _DataBase) + "
"where a.cjid=b.cjid and deptid=" + deptid + " and b.ggid in " +
" (select ggid from yp_ypbm where upper(pym) like '" + ypdm.Trim().ToUpper() + "%' or upper(wbm) like '" + ypdm.Trim().ToUpper() + "%' or ypbm like '%" + ypdm.Trim() + "%') ";
if (yplx > 0)
{
ssql = ssql + " and yplx=" + yplx + " order by a.ggid";
}
return(tb = _DataBase.GetDataTable(ssql));
}
public static DataTable SelectDqYp(int xqtqts, string xq2, int yplx, long deptid, string rq1, string rq2, RelationalDatabase _DataBase, string xq1)//增加xq1
{
DataTable tb;
string _xq2 = Convert.ToString(Convert.ToDateTime(xq2).AddDays(xqtqts).ToString("yyyy-MM-dd"));
string ssql = "select 0 序号,yppm 品名,ypgg 规格,s_sccj 厂家," +
" pfj 批发价,lsj 零售价,dbo.fun_yp_kwmc(b.ggid,deptid) 库位,ypph 批号,ypxq 效期," +
" kcl 库存量,dbo.fun_yp_ypdw(zxdw) 单位,cast(round(pfj*kcl/dwbl,2) as decimal(15,2)) 批发金额," +
" cast(round(lsj*kcl/dwbl,2) as decimal(15,2)) 零售金额,a.cjid,shh 货号 from " + Yp.Seek_kcph_table(deptid, _DataBase) + " a,vi_yp_ypcd b " +
"where a.cjid=b.cjid and kcl<>0 and cast(a.ypxq as datetime) between '" + xq1 + " 00:00:00' and '" + _xq2 + " 23:59:59'" + ////<='" + _xq.ToString() + "'
" and deptid=" + deptid + " and rtrim(a.ypxq)<>'' ";
if (yplx > 0)
{
ssql = ssql + " and yplx=" + yplx + " ";
}
if (rq1 != "")
{
ssql = ssql + " and a.djsj>='" + rq1 + " 00:00:00' and a.djsj<='" + rq2 + " 23:59:59' ";
}
ssql = ssql + " order by a.ggid";
return(tb = _DataBase.GetDataTable(ssql));
}