/// <summary>
/// 获取补药计划内容
/// </summary>
/// <param name="page">页号</param>
/// <param name="order">排序规则</param>
/// <returns></returns>
public DataSet getFillPlan(int page)
{
page = page < 1 ? 1 : page;
FillPlan.G_PAGE = page;
DataSet fDataSet = new DataSet();
DataTable table = new DataTable();
table.TableName = "MyData";
table.Columns.Add("YPMC");
table.Columns.Add("YPGG");
table.Columns.Add("SCCJ");
table.Columns.Add("SL");
table.Columns.Add("KW");
MySqlPool pool = MySqlPool.getInstance();
MySqlConnection conn = null;
try
{
conn = pool.getConnection();
string sql = "";
if (ORDER == 1)
{
sql = "select Drug_Name,Drug_Spec,Manufactory,StockFillQty,Package_Unit,storageloc from v_f_stock2 order by storageloc limit " + (page - 1) * G_PAGE_ITEM + "," + G_PAGE_ITEM;
}
else
{
sql = "select Drug_Name,Drug_Spec,Manufactory,StockFillQty,Package_Unit,storageloc from v_f_stock2 limit " + (page - 1) * G_PAGE_ITEM + "," + G_PAGE_ITEM;
}
MySqlCommand cmd = new MySqlCommand(sql, conn);
MySqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
DataRow row = table.NewRow();
row["YPMC"] = dr.IsDBNull(0) ? "" : dr.GetString(0);
row["YPGG"] = dr.IsDBNull(1) ? "" : dr.GetString(1);
row["SCCJ"] = dr.IsDBNull(2) ? "" : dr.GetString(2);
row["SL"] = (dr.IsDBNull(3) ? "" : dr.GetString(3)) + (dr.IsDBNull(4) ? "" : dr.GetString(4));
row["KW"] = dr.IsDBNull(5) ? "" : dr.GetString(5);
table.Rows.Add(row);
}
dr.Close();
}
catch (System.Exception ex)
{
//
}
finally
{
pool.releaseConnection(conn);
}
fDataSet.Tables.Add(table);
return(fDataSet);
}
/// <summary>
/// 检测登录用户是否合法
/// </summary>
/// <param name="userCode">用户工号条码</param>
/// <returns>true 验证成功；false 验证失败</returns>
public bool check(string userCode)
{
bool flg = false;
MySqlPool pool = MySqlPool.getInstance();
MySqlConnection conn = null;
try
{
conn = pool.getConnection();
string sql = "select user_id,user_name from user_list where id_code='" + userCode + "'";
MySqlCommand cmd = new MySqlCommand(sql, conn);
MySqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
flg = true;
Paramters.globalUserID = dr.IsDBNull(0) ? "" : dr.GetString(0);
Paramters.globalUserName = dr.IsDBNull(1) ? "" : dr.GetString(1);
}
dr.Close();
}
catch (System.Exception ex)
{
//
}
finally
{
pool.releaseConnection(conn);
}
return(flg);
}
public bool updateStockList(VOStockList stockList)
{
MySqlPool pool = MySqlPool.getInstance();
MySqlConnection conn = null;
try
{
conn = pool.getConnection();
//if (stockList.State.Equals("0"))
//{
string sql = "update stock_list set stock_length=" + stockList.StockLength + ",stock_width=" + stockList.StockWidth + ",stock_height=" + stockList.StockHeight + ",stock_hor=" + stockList.StockHor + ",stock_ver=" + stockList.StockVer + ",state=" + stockList.State + ",Stock_Hor_correct='" + stockList.Stock_Hor_correct + "',Stock_Ver_correct='" + stockList.Stock_Ver_correct + "' where stock_id=" + stockList.StockID;
new MySqlCommand(sql, conn).ExecuteNonQuery();
//}
//else if (stockList.State.Equals("1"))
//{
// string sql = "update stock_list set eqp_id = 'F1',stock_length=" + stockList.StockLength + ",stock_width=" + stockList.StockWidth + ",stock_height=" + stockList.StockHeight + ",stock_hor=" + stockList.StockHor + ",stock_ver=" + stockList.StockVer + ",state=" + stockList.State + " where stock_id=" + stockList.StockID;
// new MySqlCommand(sql, conn).ExecuteNonQuery();
//}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.ToString());
return(false);
}
finally
{
pool.releaseConnection(conn);
}
return(true);
}
/// <summary>
/// 清空药槽
/// </summary>
/// <param name="stockID">药槽编码</param>
/// <returns></returns>
public bool clearChannel(string stockID)
{
MySqlPool pool = MySqlPool.getInstance();
MySqlConnection conn = null;
try
{
conn = pool.getConnection();
string sql = "delete from stock_detail where Stock_ID=" + stockID;
new MySqlCommand(sql, conn).ExecuteNonQuery();
sql = "update stock_list set Drug_Code=null where Stock_ID=" + stockID;
new MySqlCommand(sql, conn).ExecuteNonQuery();
}
catch (System.Exception ex)
{
return(false);
}
finally
{
pool.releaseConnection(conn);
}
return(true);
}
public bool updateStock(int stockID, int quantity, string bnotp, string expdate)
{
MySqlPool pool = MySqlPool.getInstance();
MySqlConnection conn = null;
try
{
conn = pool.getConnection();
string sGUID = "";
string sql = "select stock_guid from stock_detail where stock_id=" + stockID + " and bnotp='" + bnotp + "' order by pos_no desc limit 0,1";
MySqlCommand cmd = new MySqlCommand(sql, conn);
MySqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
sGUID = dr.IsDBNull(0) ? "" : dr.GetString(0);
}
dr.Close();
if ("".Equals(sGUID))
{
int posno = 1;
string sql2 = "select ifnull(max(pos_no),0)+1 from stock_detail where eqp_id='F1' and stock_id=" + stockID + "";
MySqlCommand cmd2 = new MySqlCommand(sql2, conn);
MySqlDataReader dr2 = cmd.ExecuteReader();
while (dr2.Read())
{
posno = dr2.GetInt32(0);
}
dr2.Close();
sql = "insert into stock_detail(stock_guid,stock_id,eqp_id,row_no,col_no,pos_no,drug_code,quantity) select uuid(),stock_id,eqp_id,row_no,col_no," + posno + ",drug_code," + quantity + " from stock_list where stock_id=" + stockID;
}
else
{
sql = "update stock_detail set quantity=quantity+" + quantity + " where stock_guid='" + sGUID + "'";
}
// Console.WriteLine(sql);
new MySqlCommand(sql, conn).ExecuteNonQuery();
}
catch (System.Exception ex)
{
MessageBox.Show(ex.ToString());
return(false);
}
finally
{
pool.releaseConnection(conn);
}
return(true);
}
public DataTable getOutInData()
{
DataTable table = new DataTable();
table.Columns.Add("序号");
table.Columns.Add("药品名称");
table.Columns.Add("规格");
table.Columns.Add("厂家");
table.Columns.Add("出库数量");
table.Columns.Add("入库数量");
table.Columns.Add("二级库位");
MySqlPool pool = MySqlPool.getInstance();
MySqlConnection conn = null;
try
{
conn = pool.getConnection();
string sql = "select Drug_Name,Drug_Spec,Manufactory,ck,rk,storageloc from v_stock_inout9";
MySqlCommand cmd = new MySqlCommand(sql, conn);
MySqlDataReader dr = cmd.ExecuteReader();
int i = 1;
while (dr.Read())
{
DataRow row = table.NewRow();
row["序号"] = i++;
row["药品名称"] = dr.IsDBNull(0) ? "" : dr.GetString(0);
row["规格"] = dr.IsDBNull(1) ? "" : dr.GetString(1);
row["厂家"] = dr.IsDBNull(2) ? "" : dr.GetString(2);
row["出库数量"] = dr.IsDBNull(3) ? "" : dr.GetString(3);
row["入库数量"] = dr.IsDBNull(4) ? "" : dr.GetString(4);
row["二级库位"] = dr.IsDBNull(5) ? "" : dr.GetString(5);
table.Rows.Add(row);
}
dr.Close();
}
catch (System.Exception ex)
{
//
}
finally
{
pool.releaseConnection(conn);
}
return(table);
}
public bool getChart()
{
MySqlPool pool = MySqlPool.getInstance();
MySqlConnection conn = null;
try
{
conn = pool.getConnection();
xValues = new string[8];
yValues1 = new Double[8];
yValues2 = new Double[8];
string sql = @"select DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 7 DAY),'%m月%d日'),out_qty,in_qty from task_log WHERE count_date=DATE_SUB(CURDATE(),INTERVAL 7 DAY)
union select DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 6 DAY),'%m月%d日'),out_qty,in_qty from task_log WHERE count_date=DATE_SUB(CURDATE(),INTERVAL 6 DAY)
union select DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 5 DAY),'%m月%d日'),out_qty,in_qty from task_log WHERE count_date=DATE_SUB(CURDATE(),INTERVAL 5 DAY)
union select DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 4 DAY),'%m月%d日'),out_qty,in_qty from task_log WHERE count_date=DATE_SUB(CURDATE(),INTERVAL 4 DAY)
union select DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 3 DAY),'%m月%d日'),out_qty,in_qty from task_log WHERE count_date=DATE_SUB(CURDATE(),INTERVAL 3 DAY)
union select DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 2 DAY),'%m月%d日'),out_qty,in_qty from task_log WHERE count_date=DATE_SUB(CURDATE(),INTERVAL 2 DAY)
union select DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 1 DAY),'%m月%d日'),out_qty,in_qty from task_log WHERE count_date=DATE_SUB(CURDATE(),INTERVAL 1 DAY)
union select DATE_FORMAT(CURDATE(),'%m月%d日'),(select sum(quantity) from equipment_out where eqp_id='" + Paramters.G_EQP_ID + "' and datediff(now(),out_time)=0),(select sum(quantity) from equipment_in where eqp_id='" + Paramters.G_EQP_ID + "' and datediff(now(),in_time)=0)";
MySqlCommand cmd = new MySqlCommand(sql, conn);
MySqlDataReader dr = cmd.ExecuteReader();
int i = 0;
while (dr.Read())
{
xValues[i] = dr.GetString(0);
yValues1[i] = dr.IsDBNull(1) ? 0 : dr.GetDouble(1);
yValues2[i] = dr.IsDBNull(2) ? 0 : dr.GetDouble(2);
i++;
}
dr.Close();
}
catch (System.Exception ex)
{
//
}
finally
{
pool.releaseConnection(conn);
}
return(true);
}
public DataTable getDrugBnotp(string drugCode)
{
DataTable table = new DataTable();
table.Columns.Add("批次");
table.Columns.Add("效期");
MySqlPool pool = MySqlPool.getInstance();
MySqlConnection conn = null;
try
{
conn = pool.getConnection();
string sql = @"select BNOTP as 批次,DATE_FORMAT(Exp_Date,'%Y-%m-%d') as 有效期,Use_Flag as 状态 from drug_bnotp_list where Drug_Code='" + drugCode + "' order by Exp_Date desc limit 0,50";
MySqlCommand cmd = new MySqlCommand(sql, conn);
MySqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
DataRow row = table.NewRow();
row["批次"] = dr.IsDBNull(0) ? "" : dr.GetString(0);
row["效期"] = dr.IsDBNull(1) ? "" : dr.GetString(1);
table.Rows.Add(row);
}
dr.Close();
}
catch (System.Exception ex)
{
//
}
finally
{
pool.releaseConnection(conn);
}
return(table);
}
public DataTable getChannelList(string h, string l)
{
DataTable table = new DataTable();
table.Columns.Add(" 药槽编码");
table.Columns.Add(" 设备编号");
table.Columns.Add(" 行号");
table.Columns.Add(" 列号");
table.Columns.Add(" 药槽长度");
table.Columns.Add(" 药槽宽度");
table.Columns.Add(" 药槽高度");
table.Columns.Add(" 上药横向坐标");
table.Columns.Add(" 上药纵向坐标");
table.Columns.Add(" 状态");
table.Columns.Add(" 备注");
table.Columns.Add(" 水平偏移");
table.Columns.Add(" 垂直偏移");
MySqlPool pool = MySqlPool.getInstance();
MySqlConnection conn = null;
try
{
conn = pool.getConnection();
string sql = "";
if ("".Equals(h))
{
sql = @"select Stock_ID,Eqp_ID,Row_NO,Col_NO,Stock_Length,Stock_Width,Stock_Height,Stock_Hor,Stock_Ver,State,Remark,Stock_Hor_correct,Stock_Ver_correct from Stock_list
where Eqp_ID='" + NEIni.ReadValue("./config.ini", "params", "equipmentid", "F1") + "' and Col_NO=" + l;
}
else if ("".Equals(l))
{
sql = @"select Stock_ID,Eqp_ID,Row_NO,Col_NO,Stock_Length,Stock_Width,Stock_Height,Stock_Hor,Stock_Ver,State,Remark,Stock_Hor_correct,Stock_Ver_correct from Stock_list
where Eqp_ID='" + NEIni.ReadValue("./config.ini", "params", "equipmentid", "F1") + "' and Row_NO=" + h;
}
else if (!h.Equals("") && !l.Equals("") && !h.Equals("0") && !l.Equals("0"))
{
sql = @"select Stock_ID,Eqp_ID,Row_NO,Col_NO,Stock_Length,Stock_Width,Stock_Height,Stock_Hor,Stock_Ver,State,Remark,Stock_Hor_correct,Stock_Ver_correct from Stock_list
where Eqp_ID='" + NEIni.ReadValue("./config.ini", "params", "equipmentid", "F1") + "' and Row_NO=" + h + " and Col_NO=" + l;
}
else if (h.Equals("0") && l.Equals("0"))
{
sql = @"select Stock_ID,Eqp_ID,Row_NO,Col_NO,Stock_Length,Stock_Width,Stock_Height,Stock_Hor,Stock_Ver,State,Remark,Stock_Hor_correct,Stock_Ver_correct from Stock_list
where Eqp_ID='" + NEIni.ReadValue("./config.ini", "params", "equipmentid", "F1") + "' and state='2'";
}
MySqlCommand cmd = new MySqlCommand(sql, conn);
MySqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
DataRow row = table.NewRow();
row[" 药槽编码"] = dr.IsDBNull(0) ? "" : dr.GetString(0);
row[" 设备编号"] = dr.IsDBNull(1) ? "" : dr.GetString(1);
row[" 行号"] = dr.IsDBNull(2) ? "" : dr.GetString(2);
row[" 列号"] = dr.IsDBNull(3) ? "" : dr.GetString(3);
row[" 药槽长度"] = dr.IsDBNull(4) ? "" : dr.GetString(4);
row[" 药槽宽度"] = dr.IsDBNull(5) ? "" : dr.GetString(5);
row[" 药槽高度"] = dr.IsDBNull(6) ? "" : dr.GetString(6);
row[" 上药横向坐标"] = dr.IsDBNull(7) ? "" : dr.GetString(7);
row[" 上药纵向坐标"] = dr.IsDBNull(8) ? "" : dr.GetString(8);
if (dr.GetString(9).Equals("2"))
{
row[" 状态"] = "已停用";
}
else
{
row[" 状态"] = dr.IsDBNull(9) ? "" : dr.GetString(9);
}
row[" 备注"] = dr.IsDBNull(10) ? "" : dr.GetString(10);
row[" 水平偏移"] = dr.IsDBNull(11) ? "" : dr.GetString(11);
row[" 垂直偏移"] = dr.IsDBNull(12) ? "" : dr.GetString(12);
table.Rows.Add(row);
}
dr.Close();
}
catch (System.Exception ex)
{
//
}
finally
{
pool.releaseConnection(conn);
}
return(table);
}
/// <summary>
/// 盘点正确
/// </summary>
/// <returns></returns>
public bool stockChecked(string sid, int qty, int cqty)
{
MySqlPool pool = MySqlPool.getInstance();
MySqlConnection conn = null;
MySqlConnection conn2 = null;
try
{
//conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["strCon"].ToString());
pool.getConnection();
//conn.Open();
if (qty == cqty)
{
//
}
else if (cqty == 0)
{
string sql = "delete from stock_detail where Stock_ID='" + sid + "'";
new MySqlCommand(sql, conn).ExecuteNonQuery();
}
else if (qty < cqty)
{
if (qty == 0)
{
// 老库存为0，插入一条新库存，批次信息暂无法获取
string sql = "insert into stock_detail(stock_guid,stock_id,eqp_id,row_no,col_no,pos_no,drug_code,quantity) select uuid(),stock_id,eqp_id,row_no,col_no,1,drug_code," + cqty + " from stock_list where Stock_ID='" + sid + "'";
new MySqlCommand(sql, conn).ExecuteNonQuery();
}
else
{
// 更新最后一条库存记录
string stockGuid = "";
string sql = "select stock_guid from stock_detail where Stock_ID='" + sid + "' order by Pos_NO desc";
MySqlCommand cmd = new MySqlCommand(sql, conn);
MySqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
stockGuid = dr.GetString(0);
}
dr.Close();
sql = "update stock_detail set quantity=quantity+" + (cqty - qty) + " where stock_guid='" + stockGuid + "'";
new MySqlCommand(sql, conn).ExecuteNonQuery();
}
}
else
{
conn2 = new MySqlConnection(ConfigurationManager.ConnectionStrings["strCon"].ToString());//pool.getConnection();
conn2.Open();
// 实际比当前少，循环减库存
int offQty = qty - cqty;
string sql = "select stock_guid,quantity from stock_detail where Stock_ID='" + sid + "' order by Pos_NO";
MySqlCommand cmd = new MySqlCommand(sql, conn);
MySqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
string stockGuid = dr.GetString(0);
int sqty = dr.GetInt32(1);
if (sqty <= offQty)
{
string sql1 = "delete from stock_detail where stock_guid='" + stockGuid + "'";
new MySqlCommand(sql1, conn2).ExecuteNonQuery();
}
else
{
string sql1 = "update stock_detail set quantity=quantity-" + offQty + " where stock_guid='" + stockGuid + "'";
new MySqlCommand(sql1, conn2).ExecuteNonQuery();
}
offQty = offQty - sqty;
if (offQty < 1)
{
break;
}
}
dr.Close();
}
}
catch (System.Exception ex)
{
MessageBox.Show("更新异常2：" + ex.ToString());
return(false);
}
finally
{
pool.releaseConnection(conn);
pool.releaseConnection(conn2);
//conn.Close();
//conn2.Close();
}
return(true);
}
public DataTable getCheckList(string code, int flag)
{
DataTable table = new DataTable();
table.Columns.Add(" 设备");
table.Columns.Add("药槽编码");
table.Columns.Add(" 行号");
table.Columns.Add(" 列号");
table.Columns.Add(" 库位状态");
table.Columns.Add(" 备注");
table.Columns.Add(" 药品名称");
table.Columns.Add(" 规格");
table.Columns.Add(" 库存数量");
table.Columns.Add("药品长度");
table.Columns.Add("库位长度");
table.Columns.Add("库位宽度");
table.Columns.Add("HOR");
table.Columns.Add("VER");
//table.Columns.Add("药槽编码");
//table.Columns.Add(" 设备");
//table.Columns.Add(" 行号");
//table.Columns.Add(" 列号");
//table.Columns.Add(" 药品名称");
//table.Columns.Add(" 规格");
//table.Columns.Add(" 库存数量");
//table.Columns.Add(" 库位状态");
//table.Columns.Add("药品长度");
//table.Columns.Add("库位长度");
//table.Columns.Add("库位宽度");
//table.Columns.Add("HOR");
//table.Columns.Add("VER");
MySqlPool pool = MySqlPool.getInstance();
MySqlConnection conn = null;
try
{
conn = pool.getConnection();
string sql = "";
if (flag == 1)
{
sql = @"select a.stock_id,a.Eqp_ID,a.Row_NO,a.Col_NO,drug_name,drug_spec,ifnull(sum(quantity),0) as qty,state,package_length,stock_length,stock_width,stock_hor,stock_ver,remark from stock_list a left join drug_list b on a.Drug_Code=b.Drug_Code
left join stock_detail c on a.Stock_ID=c.Stock_ID where a.eqp_id='" + Paramters.G_EQP_ID + "' GROUP BY a.stock_id order by a.Row_NO,a.Col_NO";
}
else if (flag == 2)
{
sql = @"select a.stock_id,a.Eqp_ID,a.Row_NO,a.Col_NO,drug_name,drug_spec,ifnull(sum(quantity),0) as qty,state,package_length,stock_length,stock_width,stock_hor,stock_ver,remark from stock_list a left join drug_list b on a.Drug_Code=b.Drug_Code
left join stock_detail c on a.Stock_ID=c.Stock_ID where a.eqp_id='" + Paramters.G_EQP_ID + "' and (drug_name like '%" + code + "%' or short_code like '%" + code + "%' or drug_barcode like '%" + code + "%') and state=0 GROUP BY a.stock_id order by a.Row_NO,a.Col_NO";
}
else if (flag == 3)
{
sql = @"select a.stock_id,a.Eqp_ID,a.Row_NO,a.Col_NO,drug_name,drug_spec,ifnull(sum(quantity),0) as qty,state,package_length,stock_length,stock_width,stock_hor,stock_ver,remark from stock_list a left join drug_list b on a.Drug_Code=b.Drug_Code
left join stock_detail c on a.Stock_ID=c.Stock_ID where a.eqp_id='" + Paramters.G_EQP_ID + "' and (drug_name like '%" + code + "%' or short_code like '%" + code + "%' or drug_barcode like '%" + code + "%') and a.Drug_Code>'' GROUP BY a.stock_id order by a.Row_NO,a.Col_NO";
}
else
{
}
Console.WriteLine(sql);
MySqlCommand cmd = new MySqlCommand(sql, conn);
MySqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
DataRow row = table.NewRow();
row["药槽编码"] = dr.GetString(0);
row[" 设备"] = dr.GetString(1);
row[" 行号"] = dr.GetString(2);
row[" 列号"] = dr.GetString(3);
row[" 药品名称"] = dr.IsDBNull(4) ? "" : dr.GetString(4);
row[" 规格"] = dr.IsDBNull(5) ? "" : dr.GetString(5);
row[" 库存数量"] = dr.IsDBNull(6) ? "" : dr.GetString(6);
if (dr.GetString(7).Equals("1"))
{
row[" 库位状态"] = "正常";
}
else
{
row[" 库位状态"] = "锁定";
}
//row[" 库位状态"] = dr.IsDBNull(7) ? "" : dr.GetString(7);
row["药品长度"] = dr.IsDBNull(8) ? "100" : dr.GetString(8);
row["库位长度"] = dr.IsDBNull(9) ? "" : dr.GetString(9);
row["库位宽度"] = dr.IsDBNull(10) ? "" : dr.GetString(10);
row["HOR"] = dr.IsDBNull(11) ? "" : dr.GetString(11);
row["VER"] = dr.IsDBNull(12) ? "" : dr.GetString(12);
row[" 备注"] = dr.IsDBNull(13) ? "" : dr.GetString(13);
table.Rows.Add(row);
}
dr.Close();
}
catch (System.Exception ex)
{
//
}
finally
{
pool.releaseConnection(conn);
}
return(table);
}
public DataTable getStockList(string drugCode)
{
DataTable table = new DataTable();
table.Columns.Add("库位编码");
table.Columns.Add(" 行号");
table.Columns.Add(" 列号");
table.Columns.Add(" 库存量");
table.Columns.Add(" 可补量");
table.Columns.Add("hor");
table.Columns.Add("ver");
table.Columns.Add("len");
table.Columns.Add("wid");
table.Columns.Add("xl");
table.Columns.Add("有效期");
table.Columns.Add("批号");
MySqlPool pool = MySqlPool.getInstance();
MySqlConnection conn = null;
try
{
conn = pool.getConnection();
string sql = "select a.Stock_ID,Row_NO,Col_NO,ifnull(quantity,0),FLOOR(Stock_Length/Package_Length) as cb,Stock_Hor,Stock_Ver,Stock_length,Stock_width,Stock_Limit,TO_DAYS(exp_date)-TO_DAYS(NOW()) as days,bnotp from stock_list a left join (select stock_id,sum(quantity) as quantity,Exp_Date,bnotp from stock_detail where Drug_Code='" + drugCode + "' GROUP BY Stock_ID) b on a.Stock_ID=b.stock_id left join drug_list c on a.Drug_Code=c.Drug_Code where a.state=1 and a.Drug_Code='" + drugCode + "'";
MySqlCommand cmd = new MySqlCommand(sql, conn);
MySqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
if (dr.GetInt32(4) - dr.GetInt32(3) < 1)
{
continue;
}
DataRow row = table.NewRow();
row["库位编码"] = dr.IsDBNull(0) ? "" : dr.GetString(0);
row[" 行号"] = dr.IsDBNull(1) ? "" : dr.GetString(1);
row[" 列号"] = dr.IsDBNull(2) ? "" : dr.GetString(2);
row[" 库存量"] = dr.IsDBNull(3) ? "" : dr.GetString(3);
//添加单库位限制量
//if (!dr.GetString(9).Equals("0"))
//{
// row[" 可补量"] = (dr.GetInt32(9) - dr.GetInt32(3)) + "";
//}
//else
//{
row[" 可补量"] = (dr.GetInt32(4) - dr.GetInt32(3)) + "";
//}
row["hor"] = dr.IsDBNull(5) ? "0" : dr.GetString(5);
row["ver"] = dr.IsDBNull(6) ? "0" : dr.GetString(6);
row["len"] = dr.IsDBNull(7) ? "0" : dr.GetString(7);
row["wid"] = dr.IsDBNull(8) ? "0" : dr.GetString(8);
row["xl"] = dr.IsDBNull(4) ? "0" : dr.GetString(4);
row["有效期"] = dr.IsDBNull(10) ? "0" : dr.GetString(10);
row["批号"] = dr.IsDBNull(11) ? "0" : dr.GetString(11);
table.Rows.Add(row);
}
dr.Close();
}
catch (System.Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
pool.releaseConnection(conn);
}
return(table);
}
//public static int ORDER = 1;
public DataTable getFillData(string sql)
{
DataTable table = new DataTable();
table.Columns.Add("药品编码");
table.Columns.Add("药品名称");
table.Columns.Add("规格");
table.Columns.Add("厂家");
table.Columns.Add("可补量");
table.Columns.Add("库位码");
table.Columns.Add("当前量");
table.Columns.Add("最大库存");
table.Columns.Add("库位限量");
table.Columns.Add("药长");
table.Columns.Add("药宽");
//table.Columns.Add("槽数");
MySqlPool pool = MySqlPool.getInstance();
MySqlConnection conn = null;
try
{
conn = pool.getConnection();
//string sql = "";
//if (ORDER == 1)
//{
// //sql = "select drug_code,Drug_Name,Drug_Spec,Manufactory,StockFillQty,storageloc,stockqty,stockmaxqty,Stock_Limit,Package_Length,Package_Width from v_f_stock2 where stockfillqty>0 and (short_code like '%" + queryCode + "%' or drug_barcode='" + queryCode + "') ";
// //sql = "select drug_code,Drug_Name,Drug_Spec,Manufactory,StockFillQty,storageloc,stockqty,stockmaxqty,Stock_Limit,Package_Length,Package_Width from v_f_stock2 where stockfillqty>0 and (short_code like '%" + queryCode + "%' or drug_barcode='" + queryCode + "') order by stockfillqty desc";
//}
//else
//{
// sql = "select drug_code,Drug_Name,Drug_Spec,Manufactory,StockFillQty,storageloc,stockqty,stockmaxqty,Stock_Limit,Package_Length,Package_Width from v_f_stock2 where stockfillqty>0 and (short_code like '%" + queryCode + "%' or drug_barcode='" + queryCode + "')";
//}
MySqlCommand cmd = new MySqlCommand(sql, conn);
MySqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
DataRow row = table.NewRow();
row["药品编码"] = dr.IsDBNull(0) ? "" : dr.GetString(0);
row["药品名称"] = dr.IsDBNull(1) ? "" : dr.GetString(1);
row["规格"] = dr.IsDBNull(2) ? "" : dr.GetString(2);
row["厂家"] = dr.IsDBNull(3) ? "" : dr.GetString(3);
//if (dr.GetInt32(8) == 0)
//{
row["可补量"] = dr.IsDBNull(4) ? "" : dr.GetString(4);
//}
//else
//{
// row["可补量"] = (int.Parse(dr.GetString(8)) * int.Parse(dr.GetString(11)) - int.Parse(dr.GetString(6))).ToString();
//}
row["库位码"] = dr.IsDBNull(5) ? "" : dr.GetString(5);
row["当前量"] = dr.IsDBNull(6) ? "" : dr.GetString(6);
//if (dr.GetInt32(8) == 0)
//{
row["最大库存"] = dr.IsDBNull(7) ? "" : dr.GetString(7);
//}
//else
//{
// row["最大库存"] = (int.Parse(dr.GetString(8)) * int.Parse(dr.GetString(11))).ToString();
//}
if (dr.IsDBNull(8) || "0".Equals(dr.GetString(8)))
{
row["库位限量"] = "" + 1100 / int.Parse(dr.GetString(9));
}
else
{
row["库位限量"] = dr.IsDBNull(8) ? "" : dr.GetString(8);
}
row["药长"] = dr.IsDBNull(9) ? "" : dr.GetString(9);
row["药宽"] = dr.IsDBNull(10) ? "" : dr.GetString(10);
//row["槽数"] = dr.IsDBNull(11) ? "" : dr.GetString(11);
table.Rows.Add(row);
}
dr.Close();
}
catch (System.Exception ex)
{
//
}
finally
{
pool.releaseConnection(conn);
}
return(table);
}
public DataTable getWaitingList(VODrugInfo drugInfo)
{
DataTable table = new DataTable();
table.Columns.Add("药品名称");
table.Columns.Add("规格");
table.Columns.Add("厂家");
table.Columns.Add("可补量");
table.Columns.Add("库位码");
table.Columns.Add("当前量");
table.Columns.Add("最大库存");
MySqlPool pool = MySqlPool.getInstance();
MySqlConnection conn = null;
try
{
conn = pool.getConnection();
string sql = "select Drug_Name,Drug_Spec,Manufactory,StockFillQty,storageloc,stockqty,stockmaxqty,drug_code,Stock_Limit,StockCount from v_f_stock2 where StockFillQty>0 order by StockFillQty desc";
MySqlCommand cmd = new MySqlCommand(sql, conn);
MySqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
try
{
if (drugInfo.DrugCode.Equals(dr.GetString(7)))
{
continue;
}
}
catch (System.Exception ex)
{
//
}
DataRow row = table.NewRow();
row["药品名称"] = dr.IsDBNull(0) ? "" : dr.GetString(0);
row["规格"] = dr.IsDBNull(1) ? "" : dr.GetString(1);
row["厂家"] = dr.IsDBNull(2) ? "" : dr.GetString(2);
//添加单库位限制量
//if (!dr.GetString(8).Equals("0"))
//{
// row[" 可补量"] = dr.GetString(9) + "";
//}
//else
//{
row[" 可补量"] = dr.IsDBNull(3) ? "" : dr.GetString(3);
//}
row["库位码"] = dr.IsDBNull(4) ? "" : dr.GetString(4);
row["当前量"] = dr.IsDBNull(5) ? "" : dr.GetString(5);
row["最大库存"] = dr.IsDBNull(6) ? "" : dr.GetString(6);
table.Rows.Add(row);
}
dr.Close();
}
catch (System.Exception ex)
{
//
}
finally
{
pool.releaseConnection(conn);
}
return(table);
}