maqiao
/
HuNan_NOSqlSugar
1
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
HuNan_NOSqlSugar/DM_Dal/MysqlHelperDal.cs

420 lines
26 KiB
C#
Raw Permalink Normal View History

添加项目文件。
2024-07-06 10:01:30 +08:00
using DM_Dal.Models;
using Newtonsoft.Json;
using SCHelper.Database;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Xml;
namespace DM_Dal
{
public class MysqlHelperDal
{
static string machine_id = ConfigurationManager.AppSettings["machineId"].ToString();
#region 公共
////获取抽屉数量
public int FindDrawerCount()
{
int count = 0;
string sql = $@"SELECT COUNT(1) FROM (SELECT COUNT(row_no) FROM channel_list WHERE ( drawer_type <> 3 ) AND (machine_id = '{machine_id}') GROUP BY row_no ) CountTable";
DataSet ds = DBHelper.GetInstance().ExecuteQuery(sql);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
count = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
}
return count;
}
#endregion
#region 用户登录
//指纹登录
public UserList CheckUserByFingerPrinter(int fingerPrinterId)
{
UserList? user = null;
int id = fingerPrinterId;
string sql = $@"select ul.id as id,ul.User_name as userName,r.id as roleId,r.role_name,r.permissions,r.machine_id from user_list ul
INNER JOIN role r on ul.machine_role_id=r.id where ul.Id={fingerPrinterId} and ul.machine_id='{machine_id}' and r.machine_id='{machine_id}'";
DataSet ds = DBHelper.GetInstance().ExecuteQuery(sql);
if (ds.Tables[0].Rows.Count > 0)
{
user = new UserList();
user.Id = ds.Tables[0].Rows[0]["id"] is DBNull ? 0 : Convert.ToInt32(ds.Tables[0].Rows[0]["id"]);
user.UserName = ds.Tables[0].Rows[0]["userName"] is DBNull ? "" : ds.Tables[0].Rows[0]["userName"].ToString();
user.Nickname = ds.Tables[0].Rows[0]["userName"] is DBNull ? "" : ds.Tables[0].Rows[0]["userName"].ToString();
user.MachineId = ds.Tables[0].Rows[0]["machine_id"] is DBNull ? "" : ds.Tables[0].Rows[0]["machine_id"].ToString();
RoleDm role = new RoleDm();
role.Id = ds.Tables[0].Rows[0]["roleId"] is DBNull ? 0 : Convert.ToInt32(ds.Tables[0].Rows[0]["roleId"]);
role.RoleName = ds.Tables[0].Rows[0]["role_name"] is DBNull ? "" : ds.Tables[0].Rows[0]["role_name"].ToString();
role.Permissions = JsonConvert.DeserializeObject<List<PremissionDm>>(ds.Tables[0].Rows[0]["permissions"] is DBNull ? "" : ds.Tables[0].Rows[0]["permissions"].ToString()); // JsonConvert.SerializeObject(reader.GetString("permissions")).ToList();
user.Role = role;
}
return user;
}
//用户名密码登录
public UserList CheckUserForLogin(string userName)
{
UserList user = null;
string sql = $@"select ul.id as id,ul.User_name as userName,ul.pass_word,r.id as roleId,r.role_name,r.permissions,r.machine_id from user_list ul
INNER JOIN role r on ul.machine_role_id=r.id where ul.user_ID='{userName}' and ul.machine_id='{machine_id}' and r.machine_id='{machine_id}'";
DataSet ds = DBHelper.GetInstance().ExecuteQuery(sql);
if (ds.Tables[0].Rows.Count > 0)
{
user = new UserList();
user.Id = ds.Tables[0].Rows[0]["id"] is DBNull ? 0 : Convert.ToInt32(ds.Tables[0].Rows[0]["id"]);
user.UserName = ds.Tables[0].Rows[0]["userName"] is DBNull ? "" : ds.Tables[0].Rows[0]["userName"].ToString();
user.Nickname = ds.Tables[0].Rows[0]["userName"] is DBNull ? "" : ds.Tables[0].Rows[0]["userName"].ToString();
user.MachineId = ds.Tables[0].Rows[0]["machine_id"] is DBNull ? "" : ds.Tables[0].Rows[0]["machine_id"].ToString();
RoleDm role = new RoleDm();
role.Id = ds.Tables[0].Rows[0]["roleId"] is DBNull ? 0 : Convert.ToInt32(ds.Tables[0].Rows[0]["roleId"]);
role.RoleName = ds.Tables[0].Rows[0]["role_name"] is DBNull ? "" : ds.Tables[0].Rows[0]["role_name"].ToString();
role.Permissions = JsonConvert.DeserializeObject<List<PremissionDm>>(ds.Tables[0].Rows[0]["permissions"] is DBNull ? "" : ds.Tables[0].Rows[0]["permissions"].ToString()); // JsonConvert.SerializeObject(reader.GetString("permissions")).ToList();
user.Role = role;
user.PassWord = ds.Tables[0].Rows[0]["pass_word"] is DBNull ? "" : ds.Tables[0].Rows[0]["pass_word"].ToString();
}
return user;
}
#endregion
#region 处方取药
/// <summary>
/// 处方取药
/// </summary>
/// <param name="OrderDate"></param>
/// <param name="pageNum"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public List<OrderInfo> GetOrderInfos(string Code, string SearchValue, string OrderDate, int pageNum, int pageSize, ref int totalCount)
{
List<OrderInfo> orderInfos = new List<OrderInfo>();
totalCount = 0;
pageNum = (pageNum - 1) * pageSize;
string countSql = $@" SELECT COUNT(1) FROM (SELECT oi.order_id FROM order_info oi Inner JOIN order_detail od ON ( oi.order_no = od.order_no )
Inner JOIN (SELECT chnguid,machine_id,row_no,col_no,pos_no,drug_id,drawer_type,board_type,state FROM channel_list
WHERE ( drawer_type = 1 ) AND (machine_id = '{machine_id}') GROUP BY drug_id ) t
ON ( od.drug_id = t.drug_id ) WHERE ( oi.dm_status = 0 )
AND ( oi.his_disp_flag = 0 ) AND ( oi.cancel_flag = 0 ) ";
if (!string.IsNullOrEmpty(OrderDate))
{
countSql += $" AND (DATE_FORMAT(oi.recv_date, '%Y-%m-%d') = '{OrderDate}' ) ";
}
if (!string.IsNullOrEmpty(Code) && !string.IsNullOrEmpty(SearchValue))
{
if (Code.Equals("OrderNo"))
countSql += $" AND ( oi.order_no = '{SearchValue}' ) ";
else
countSql += $" AND ( oi.patient_id = '{SearchValue}' ) ";
}
countSql += " GROUP BY oi.order_no ) CountTable ";
DataSet ds = DBHelper.GetInstance().ExecuteQuery(countSql);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
totalCount = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
}
string sql = $@"SELECT oi.order_id,oi.pharmacy,oi.order_no,oi.patient_id,oi.p_name,oi.sex,oi.age,oi.id_number,
oi.patient_card, oi.invoice_no,oi.patient_no,oi.doctor_name,oi.order_date,oi.charge_date,oi.recv_date,
oi.dept_name,oi.disease,oi.order_type,oi.charge_type,oi.state,oi.his_disp_flag,oi.cancel_flag,oi.dm_status,oi.costs FROM order_info oi Inner JOIN order_detail od ON ( oi.order_no = od.order_no ) Inner JOIN
(SELECT chnguid,machine_id,row_no,col_no,pos_no,drug_id,drawer_type,board_type,state FROM channel_list
WHERE ( drawer_type = 1 ) AND (machine_id = '{machine_id}') GROUP BY drug_id ) t
ON ( od.drug_id = t.drug_id )
WHERE ( oi.dm_status = 0 ) AND ( oi.his_disp_flag = 0 ) AND ( oi.cancel_flag = 0 ) ";
if (!string.IsNullOrEmpty(OrderDate))
{
sql += $" AND (DATE_FORMAT(oi.recv_date, '%Y-%m-%d') = '{OrderDate}' ) ";
}
if (!string.IsNullOrEmpty(Code) && !string.IsNullOrEmpty(SearchValue))
{
if (Code.Equals("OrderNo"))
sql += $" AND ( oi.order_no = '{SearchValue}' ) ";
else
sql += $" AND ( oi.patient_id = '{SearchValue}' ) ";
}
sql += $" GROUP BY oi.order_no LIMIT {pageNum},{pageSize}";
DataSet dsTable = DBHelper.GetInstance().ExecuteQuery(sql);
if (dsTable != null && dsTable.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dsTable.Tables[0].Rows.Count; i++)
{
OrderInfo order = new OrderInfo();
order.OrderId = Convert.ToInt32(dsTable.Tables[0].Rows[i]["order_id"]);
order.Pharmacy = dsTable.Tables[0].Rows[i]["pharmacy"] is DBNull ? "" : dsTable.Tables[0].Rows[i]["pharmacy"].ToString();
order.OrderNo = dsTable.Tables[0].Rows[i]["order_no"] is DBNull ? "" : dsTable.Tables[0].Rows[i]["order_no"].ToString();
order.PatientId = dsTable.Tables[0].Rows[i]["patient_id"] is DBNull ? "" : dsTable.Tables[0].Rows[i]["patient_id"].ToString();
order.PName = dsTable.Tables[0].Rows[i]["p_name"] is DBNull ? "" : dsTable.Tables[0].Rows[i]["p_name"].ToString();
order.Sex = dsTable.Tables[0].Rows[i]["sex"] is DBNull ? "" : dsTable.Tables[0].Rows[i]["sex"].ToString();
order.Age = dsTable.Tables[0].Rows[i]["age"] is DBNull ? "" : dsTable.Tables[0].Rows[i]["age"].ToString();
order.IdNumber = dsTable.Tables[0].Rows[i]["id_number"] is DBNull ? "" : dsTable.Tables[0].Rows[i]["id_number"].ToString();
order.PatientCard = dsTable.Tables[0].Rows[i]["patient_card"] is DBNull ? "" : dsTable.Tables[0].Rows[i]["patient_card"].ToString();
order.InvoiceNo = dsTable.Tables[0].Rows[i]["invoice_no"] is DBNull ? "" : dsTable.Tables[0].Rows[i]["invoice_no"].ToString();
order.PatientNo = dsTable.Tables[0].Rows[i]["patient_no"] is DBNull ? "" : dsTable.Tables[0].Rows[i]["patient_no"].ToString();
order.DoctorName = dsTable.Tables[0].Rows[i]["doctor_name"] is DBNull ? "" : dsTable.Tables[0].Rows[i]["doctor_name"].ToString();
order.OrderDate = dsTable.Tables[0].Rows[i]["order_date"] is DBNull ? DateTime.Now.AddYears(-1) : Convert.ToDateTime(dsTable.Tables[0].Rows[i]["order_date"]);
order.ChargeDate = dsTable.Tables[0].Rows[i]["charge_date"] is DBNull ? DateTime.Now.AddYears(-1) : Convert.ToDateTime(dsTable.Tables[0].Rows[i]["charge_date"]);
order.RecvDate = dsTable.Tables[0].Rows[i]["recv_date"] is DBNull ? DateTime.Now.AddYears(-1) : Convert.ToDateTime(dsTable.Tables[0].Rows[i]["recv_date"]);
order.DeptName = dsTable.Tables[0].Rows[i]["dept_name"] is DBNull ? "" : dsTable.Tables[0].Rows[i]["dept_name"].ToString();
order.Disease = dsTable.Tables[0].Rows[i]["disease"] is DBNull ? "" : dsTable.Tables[0].Rows[i]["disease"].ToString();
order.OrderType = dsTable.Tables[0].Rows[i]["order_type"] is DBNull ? "" : dsTable.Tables[0].Rows[i]["order_type"].ToString();
order.ChargeType = dsTable.Tables[0].Rows[i]["charge_type"] is DBNull ? "" : dsTable.Tables[0].Rows[i]["charge_type"].ToString();
order.State = Convert.ToInt32(dsTable.Tables[0].Rows[i]["state"]);
order.HisDispFlag = Convert.ToInt32(dsTable.Tables[0].Rows[i]["his_disp_flag"]);
order.CancelFlag = Convert.ToInt32(dsTable.Tables[0].Rows[i]["cancel_flag"]);
order.DmStatus = Convert.ToInt32(dsTable.Tables[0].Rows[i]["dm_status"]);
order.Costs = dsTable.Tables[0].Rows[i]["costs"] is DBNull ? 0 : Convert.ToInt32(dsTable.Tables[0].Rows[i]["costs"]);
orderInfos.Add(order);
}
}
return orderInfos;
}
/// <summary>
/// 根据处方号获取处方详情
/// </summary>
/// <param name="orderNo"></param>
/// <returns></returns>
public List<OrderDetail> GetOrderDetails(string orderNo)
{
List<OrderDetail> orderDetails = new List<OrderDetail>();
string strSql = $@"SELECT od.id,od.patient_id,od.order_no,od.charge_date,od.drug_id,od.quantity,od.order_unit,
od.unit_convercoef,od.set_manu_no,od.set_eff_date,od.use_discrip,od.use_frequ,od.use_once,od.use_by,
od.use_self,od.use_dosage,D.drug_name
FROM order_detail od
INNER JOIN DRUG_INFO D ON od.DRUG_ID=D.DRUG_ID
Inner JOIN
(SELECT chsguid,chnguid,machine_id,row_no,col_no,pos_no,drug_id,manu_no,eff_date,quantity,drawer_type,board_type,state
FROM channel_stock WHERE ( drawer_type = 1 ) AND (machine_id = '{machine_id}') GROUP BY drug_id ) t
ON ( od.drug_id = t.drug_id ) WHERE ( od.order_no = '{orderNo}' )";
DataSet dsTable = DBHelper.GetInstance().ExecuteQuery(strSql);
if (dsTable != null && dsTable.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dsTable.Tables[0].Rows.Count; i++)
{
OrderDetail od = new OrderDetail();
DrugInfo oi = new DrugInfo();
od.Id = Convert.ToInt32(dsTable.Tables[0].Rows[i]["id"]);
od.PatientId = dsTable.Tables[0].Rows[i]["patient_id"] is DBNull ? "" : dsTable.Tables[0].Rows[i]["patient_id"].ToString();
od.OrderNo = dsTable.Tables[0].Rows[i]["order_no"] is DBNull ? "" : dsTable.Tables[0].Rows[i]["order_no"].ToString();
od.ChargeDate = dsTable.Tables[0].Rows[i]["charge_date"] is DBNull ? DateTime.Now.AddYears(-1) : Convert.ToDateTime(dsTable.Tables[0].Rows[i]["charge_date"]);
od.DrugId = dsTable.Tables[0].Rows[i]["drug_id"] is DBNull ? "" : dsTable.Tables[0].Rows[i]["drug_id"].ToString();
od.Quantity = dsTable.Tables[0].Rows[i]["quantity"] is DBNull ? 0 : Convert.ToInt32(dsTable.Tables[0].Rows[i]["quantity"]);
od.OrderUnit = dsTable.Tables[0].Rows[i]["order_unit"] is DBNull ? "" : dsTable.Tables[0].Rows[i]["order_unit"].ToString();
od.SetEffDate = dsTable.Tables[0].Rows[i]["set_eff_date"] is DBNull ? "" : dsTable.Tables[0].Rows[i]["set_eff_date"].ToString();
od.SetManuNo = dsTable.Tables[0].Rows[i]["set_manu_no"] is DBNull ? "" : dsTable.Tables[0].Rows[i]["set_manu_no"].ToString();
oi.DrugName = dsTable.Tables[0].Rows[i]["drug_name"] is DBNull ? "" : dsTable.Tables[0].Rows[i]["drug_name"].ToString();
od.DrugInfo = oi;
orderDetails.Add(od);
}
}
return orderDetails;
}
/// <summary>
/// 获取药品对应库存信息
/// </summary>
/// <returns></returns>
public List<ChannelStock> GetChannelStocks(string drugId)
{
List<ChannelStock> channelStocks = new List<ChannelStock>();
string strSql = $@"SELECT cs.chsguid,cs.chnguid,cs.machine_id,cs.row_no,cs.col_no,cs.pos_no,cs.drug_id,cs.manu_no,
cs.eff_date,cs.quantity,cs.drawer_type,cs.board_type,cs.state,D.Drug_Name,D.drug_spec FROM channel_stock cs Inner JOIN channel_list cl ON (( cs.chnguid = cl.chnguid ) AND ( cs.drug_id = cl.drug_id ))
INNER JOIN DRUG_INFO D ON cs.drug_id=D.drug_id
WHERE ( cs.quantity > 0 ) AND ( cs.drawer_type = 1 ) AND (cs.machine_id = '{machine_id}')
AND ( cs.drug_id = '{drugId}' )ORDER BY cs.eff_date ASC,cs.row_no ASC,cs.manu_no ASC ";
DataSet dsTable = DBHelper.GetInstance().ExecuteQuery(strSql);
if (dsTable != null && dsTable.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dsTable.Tables[0].Rows.Count; i++)
{
ChannelStock channelStock = new ChannelStock();
DrugInfo drugInfo = new DrugInfo();
channelStock.Id = dsTable.Tables[0].Rows[i]["chsguid"] is DBNull ? "" : dsTable.Tables[0].Rows[i]["chsguid"].ToString();
channelStock.Chnguid = dsTable.Tables[0].Rows[i]["chnguid"] is DBNull ? "" : dsTable.Tables[0].Rows[i]["chnguid"].ToString();
channelStock.MachineId = dsTable.Tables[0].Rows[i]["machine_id"] is DBNull ? "" : dsTable.Tables[0].Rows[i]["machine_id"].ToString();
channelStock.DrawerNo = dsTable.Tables[0].Rows[i]["row_no"] is DBNull ? 0 : Convert.ToInt32(dsTable.Tables[0].Rows[i]["row_no"]);
channelStock.ColNo = dsTable.Tables[0].Rows[i]["col_no"] is DBNull ? 0 : Convert.ToInt32(dsTable.Tables[0].Rows[i]["col_no"]);
channelStock.DrugId = dsTable.Tables[0].Rows[i]["drug_id"] is DBNull ? "" : dsTable.Tables[0].Rows[i]["drug_id"].ToString();
channelStock.ManuNo = dsTable.Tables[0].Rows[i]["manu_no"] is DBNull ? "" : dsTable.Tables[0].Rows[i]["manu_no"].ToString();
channelStock.EffDate = dsTable.Tables[0].Rows[i]["eff_date"] is DBNull ? "" : dsTable.Tables[0].Rows[i]["eff_date"].ToString();
channelStock.Quantity = dsTable.Tables[0].Rows[i]["quantity"] is DBNull ? 0 : Convert.ToInt32(dsTable.Tables[0].Rows[i]["quantity"]);
channelStock.DrawerType = dsTable.Tables[0].Rows[i]["drawer_type"] is DBNull ? 0 : Convert.ToInt32(dsTable.Tables[0].Rows[i]["drawer_type"]);
channelStock.BoardType = dsTable.Tables[0].Rows[i]["board_type"] is DBNull ? 0 : Convert.ToInt32(dsTable.Tables[0].Rows[i]["board_type"]);
channelStock.State = dsTable.Tables[0].Rows[i]["state"] is DBNull ? 0 : Convert.ToInt32(dsTable.Tables[0].Rows[i]["state"]);
drugInfo.DrugName = dsTable.Tables[0].Rows[i]["Drug_Name"] is DBNull ? "" : dsTable.Tables[0].Rows[i]["Drug_Name"].ToString();
drugInfo.DrugSpec = dsTable.Tables[0].Rows[i]["drug_spec"] is DBNull ? "" : dsTable.Tables[0].Rows[i]["drug_spec"].ToString();
channelStock.DrugInfo = drugInfo;
channelStocks.Add(channelStock);
}
}
return channelStocks;
}
/// <summary>
/// 完成处方取药
/// </summary>
/// <returns></returns>
public bool SaveOrderTakeInfo(string orderNo, string orderDeptName, List<ChannelStock> record, int? operatorUser, int? reviewerUser)
{
bool bFlag = false;
try
{
string strSql = $"UPDATE order_info SET dm_status=1 WHERE ORDER_NO='{orderNo}'";
int iResult = DBHelper.GetInstance().ExecuteNonQuery(strSql);
if (iResult > 0)
{
for (int i = 0; i < record.Count; i++)
{
ChannelStock it = record[i];
int quantity = it.Quantity - it.TakeQuantity;
// 更新数据 库存信息
strSql = $"UPDATE channel_stock SET quantity={quantity},manu_no='{it.ManuNo}',eff_date='{it.EffDate}' WHERE chsguid='{it.Id}'";
iResult = DBHelper.GetInstance().ExecuteNonQuery(strSql);
if (iResult > 0)
{
// 获取更新完库存后的药品库存
List<ChannelStock> nowChannels = new List<ChannelStock>();
string querySql = $@"SELECT Quantity,Manu_No,drug_id FROM channel_stock WHERE drawer_type=1 and machine_id='{machine_id}' AND drug_id='{it.DrugId}' ";
DataSet ds = DBHelper.GetInstance().ExecuteQuery(querySql);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
for (int j = 0; j < ds.Tables[0].Rows.Count; j++)
{
ChannelStock stock = new ChannelStock();
stock.Quantity = ds.Tables[0].Rows[j]["Quantity"] is DBNull ? 0 : Convert.ToInt32(ds.Tables[0].Rows[j]["Quantity"]); ;
stock.DrugId = ds.Tables[0].Rows[j]["drug_id"] is DBNull ? "" : ds.Tables[0].Rows[j]["drug_id"].ToString();
stock.ManuNo = ds.Tables[0].Rows[j]["Manu_No"] is DBNull ? "" : ds.Tables[0].Rows[j]["Manu_No"].ToString();
nowChannels.Add(stock);
}
}
DateTime EFFDATE = !String.IsNullOrEmpty(it.EffDate) ? DateTime.ParseExact(it.EffDate, "yyyy-MM-dd", System.Globalization.CultureInfo.CurrentCulture) : DateTime.Now;
int stockQuantity = nowChannels.Sum(it => it.Quantity);
int manunoQuantity = nowChannels.FindAll(it2 => it2.ManuNo == it.ManuNo).Sum(it => it.Quantity);
string supplierDept = ConfigurationManager.AppSettings["receiveDept"].ToString();
string insertSql = $"INSERT INTO dm_machine_record " +
$"SET MACHINE_ID='{it.MachineId}',drawer_no='{it.DrawerNo}',COL_NO='{it.ColNo}',DRUG_ID='{it.DrugId}',MANU_NO='{it.ManuNo}'," +
$"EFF_DATE='{EFFDATE}',Operator={operatorUser},reviewer={reviewerUser}," +
$"operation_time=NOW(),quantity={it.TakeQuantity},type=2,invoice_id='{orderNo}',stock_quantity={stockQuantity}," +
$"manunoQuantity={manunoQuantity},supplierDept='{supplierDept}',receiveDept='{orderDeptName}'";
iResult = DBHelper.GetInstance().ExecuteNonQuery(insertSql);
if (iResult > 0)
{
bFlag = true;
}
}
}
}
}
catch (Exception ex)
{
bFlag = false;
}
return bFlag;
}
/// <summary>
/// 根据抽屉号、库位号查询库存信息
/// </summary>
/// <param name="rowNo"></param>
/// <param name="colNo"></param>
/// <returns></returns>
public List<ChannelStock> GetChannelStockList(int rowNo, int colNo)
{
List<ChannelStock> channel = new List<ChannelStock>();
string strSql = $"SELECT ROW_NO,COL_NO,eff_date,quantity FROM CHANNEL_STOCK WHERE MACHINE_ID='{ConfigurationManager.AppSettings["machineId"] ?? "DM1"}' AND ROW_NO={rowNo} AND COL_NO={colNo} ORDER BY EFF_DATE";
DataSet dsTable = DBHelper.GetInstance().ExecuteQuery(strSql);
if (dsTable != null && dsTable.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dsTable.Tables[0].Rows.Count; i++)
{
ChannelStock cs = new ChannelStock();
cs.DrawerNo = dsTable.Tables[0].Rows[0]["row_no"] is DBNull ? 0 : Convert.ToInt32(dsTable.Tables[0].Rows[0]["row_no"]);
cs.ColNo = dsTable.Tables[0].Rows[0]["col_no"] is DBNull ? 0 : Convert.ToInt32(dsTable.Tables[0].Rows[0]["col_no"]);
cs.EffDate = dsTable.Tables[0].Rows[0]["eff_date"] is DBNull ? "" : dsTable.Tables[0].Rows[0]["eff_date"].ToString();
cs.Quantity = dsTable.Tables[0].Rows[0]["quantity"] is DBNull ? 0 : Convert.ToInt32(dsTable.Tables[0].Rows[0]["quantity"]);
channel.Add(cs);
}
}
return channel;
}
#endregion
#region 调拨出库
#endregion
#region 抽屉取药
/// <summary>
/// 通过抽屉号获取库存信息
/// </summary>
/// <returns></returns>
public List<ChannelStock> GetChannelStockByDrawer(int row_no)
{
List<ChannelStock> channelStocks = new List<ChannelStock>();
string strSql = $@"SELECT chsguid,chnguid,machine_id,row_no,col_no,pos_no,drug_id,manu_no,eff_date,quantity,drawer_type,board_type,state FROM channel_stock
WHERE ( row_no = {row_no} ) AND ( drug_id IS NOT NULL ) AND (machine_id = '{machine_id}') AND ( drawer_type = 1 ) AND ( quantity > 0 )ORDER BY col_no ASC,eff_date ASC";
DataSet ds = DBHelper.GetInstance().ExecuteQuery(strSql);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
ChannelStock stock = new ChannelStock();
stock.Id = ds.Tables[0].Rows[i]["chsguid"].ToString();
stock.Chnguid = ds.Tables[0].Rows[i]["chnguid"].ToString();
stock.DrawerNo = row_no;
stock.ColNo = Convert.ToInt32(ds.Tables[0].Rows[i]["col_no"]);
stock.ManuNo = ds.Tables[0].Rows[i]["chnguid"].ToString();
stock.EffDate = ds.Tables[0].Rows[i]["eff_date"].ToString();
stock.Quantity = Convert.ToInt32(ds.Tables[0].Rows[i]["quantity"]);
stock.BoardType = Convert.ToInt32(ds.Tables[0].Rows[i]["board_type"]);
stock.MachineId = ds.Tables[0].Rows[i]["machine_id"].ToString();
channelStocks.Add(stock);
}
}
return channelStocks;
}
#endregion
}
}