/// <summary>
/// 获取医院匹配数据
/// </summary>
/// <param name="erpHisCode"></param>
/// <param name="orgId"></param>
/// <returns></returns>
public string GetCorpMapData(string erpHisCode, string orgId)
{
//string sql = "select ORG_ID from ERP_CORP_MAP p where p.saler_orgid=:orgId and p.CODE = :code and p.PROCESS_FLAG = '1'";
string sql = "select ORG_ID from GPO_CORP_MAP p where p.MAP_ORGID=:orgId and p.CODE = :code and p.MAP_ORGTYPE = '2'";
string result = null;
DbParameter paramBuyer = base.DbFacade.CreateParameter();
paramBuyer.ParameterName = "orgId";
paramBuyer.DbType = DbType.String;
paramBuyer.Value = orgId;
DbParameter paramPlat = base.DbFacade.CreateParameter();
paramPlat.ParameterName = "code";
paramPlat.DbType = DbType.String;
paramPlat.Value = erpHisCode;
try
{
object o = DbFacade.SQLExecuteDataTable(sql.ToString(), paramBuyer, paramPlat);
if (o != null)
{
result = o.ToString();
}
}
catch (Exception e)
{
//throw e;
}
return(result);
}
/// <summary>
/// 备货
/// </summary>
/// <returns></returns>
public DataTable GetStokReportData(string sStockId)
{
StringBuilder sbSql = new StringBuilder();
sbSql.Append("select hs.code,");
sbSql.Append("hs.remark,");
sbSql.Append("hs.sender_name,");
sbSql.Append("case hs.state when '1' then '未发送' when '2' then '已发送' when '3' then '买方已确认' when '4' then '作废' when '5' then '确认中' when '6' then '完成' end as state,");
sbSql.Append("hs.create_user_name,");
sbSql.Append("hs.create_date,"); //
sbSql.Append("hi.product_name,"); //
sbSql.Append("hi.barcode,");
sbSql.Append("hi.manufacture_name,"); //
sbSql.Append("hi.batch_no,");
sbSql.Append("hi.valid_date,");
sbSql.Append("hi.spec,"); //
sbSql.Append("hi.model,"); //
sbSql.Append("hop.price,");
sbSql.Append("hi.num ");
sbSql.Append("from hc_ord_ord_stock hs left join hc_ord_ord_stock_item hi on hs.id = hi.stock_id ");
sbSql.Append(" left join hc_ord_product hop on hi.project_prod_id = hop.id ");
sbSql.AppendFormat(" where hs.id = '{0}'", sStockId);
DataTable dt = new DataTable();
try
{
dt = DbFacade.SQLExecuteDataTable(sbSql.ToString());
}
catch
{
throw;
}
return(dt);
}
/// <summary>
/// 订单退货表数据插入校验
/// </summary>
/// <param name="dr"></param>
/// <param name="strInvalid"></param>
/// <returns></returns>
public bool CheckOrderReturnForInsert(DataRow dr, out string strInvalid)
{
strInvalid = "";
bool flag = true;
string sql = "select FACT_AMOUNT，RETURN_AMOUNT from HC_ORD_ORDER_RECEIVE where ID = :RECEIVE_ID";
DbParameter para = this.DbFacade.CreateParameter();
para.ParameterName = "RECEIVE_ID";
para.DbType = DbType.AnsiString;
para.Value = dr["RECEIVE_ID"];
DataTable dt = DbFacade.SQLExecuteDataTable(sql, para);
if (dt.Rows.Count > 0)
{
//实际到货量
Decimal ReceiveQty = Convert.ToDecimal(dt.Rows[0]["FACT_AMOUNT"].ToString());
Decimal returnAmount = Convert.ToDecimal(dt.Rows[0]["RETURN_AMOUNT"].ToString());
//退货数量
Decimal ReturnQty = Convert.ToDecimal(dr["AMOUNT"].ToString());
//退货数量不能大于实际到货量
if (ReturnQty + returnAmount > ReceiveQty)
{
strInvalid = "table:HC_ORD_ORDER_RECEIVE/id:" + dr["RECEIVE_ID"] + "/中到货数量不得小于新增退货数量，不能修改";
flag = false;
}
}
return(flag);
}
/// <summary>
/// 采购单
/// </summary>
/// <returns></returns>
public DataTable GetPurchaseReportData(string sPurchaseId)
{
StringBuilder sbSql = new StringBuilder();
sbSql.Append(" select hi.product_name,");
sbSql.Append(" hi.common_name,");
sbSql.Append(" hi.brand,");
sbSql.Append(" hi.spec,");
sbSql.Append(" hi.model,");
sbSql.Append(" hi.manufacture_name,");
sbSql.Append(" hi.sender_name,");
sbSql.Append(" hi.trade_price,");
sbSql.Append(" hi.amount,");
sbSql.Append(" hp.code,");
sbSql.Append(" hp.create_user_name,");
sbSql.Append(" hp.create_date,");
sbSql.Append(" hp.total_sum,");
sbSql.Append(" case hp.state when '1' then '准备' when '2' then '送审' when '3' then '拒绝' when '4' then '审核通过' end as state,");
sbSql.Append(" case hp.quicksend_level when '1' then '普通' when '2' then '部分紧急' when '3' then '紧急' end as quicksend_level ");
sbSql.Append(" from hc_ord_purchase hp left join hc_ord_purchase_item hi on hp.id = hi.purchase_id ");
sbSql.AppendFormat(" where hp.id = '{0}'", sPurchaseId);
DataTable dt = new DataTable();
try
{
dt = DbFacade.SQLExecuteDataTable(sbSql.ToString());
}
catch
{
throw;
}
return(dt);
}
/// <summary>
///获得一条HIS产品记录
/// </summary>
/// <param name="productcode"></param>
/// <returns></returns>
public Gpo_Product_MapModel GetGpoMapModelById(string strId)
{
StringBuilder strsql = new StringBuilder("SELECT *");
strsql.Append(" from GPO_PRODUCT_MAP where ID = '" + strId + "'");
DataTable dt = null;
Gpo_Product_MapModel product_MapModel = null;
try
{
dt = DbFacade.SQLExecuteDataTable(strsql.ToString());
if (dt != null && dt.Rows.Count > 0)
{
product_MapModel = GetModelByRow(dt.Rows[0]);
}
}
catch (Exception e)
{
throw e;
return(null);
}
return(product_MapModel);
}
//获取导出产品信息
public DataTable GetExportGpoProductMapList()
{
DataTable dt = new DataTable();
StringBuilder sqlstr = new StringBuilder();
sqlstr.Append("Select ");
sqlstr.Append(" cp.MEDICAL_NAME,cp.TRADE_NAME,ci.DOSEAGE_FORM");
sqlstr.Append(" ,(isnull(ci.SPEC,'-') + '*' + cast( isnull(ci.STAND_RATE,'0') as varchar(10)) + isnull(ci.USE_UNIT,'') + '/' + isnull(ci.SPEC_UNIT,'' ))As unc_spec,ci.FACTORY_NAME,");
sqlstr.Append(" GPM.PRODUCT_ID,GPM.PRODUCT_CODE,GPM.PRODUCT_NAME,GPM.COMMON_NAME,GPM.MODE_NAME,");
sqlstr.Append(" GPM.MEDICAL_SPEC,GPM.SPEC_UNIT,GPM.USE_UNIT,GPM.PACKAGE_RATE,GPM.STAND_RATE,");
sqlstr.Append(" GPM.FACTORY_NAME,GPM.REMARK,GPM.PROCESS_FLAG");
sqlstr.Append(" ,case when GPM.IsMap = '1' then '已匹配' else '未匹配' end as IsMap");
sqlstr.Append(" From Cont_Item ci ,CONT_PRODUCT cp, GPO_PRODUCT_MAP_VIEW GPM");
sqlstr.Append(" Where cp.product_id=ci.product_id And cp.PROJECT_ID=ci.PROJECT_ID");
sqlstr.Append(" And GPM.product_id=ci.product_id");
try
{
dt = DbFacade.SQLExecuteDataTable(sqlstr.ToString());
}
catch (Exception ex)
{
throw ex;
return(null);
}
return(dt);
}
public DataTable GetErpProductByProcode(string erpProductCode, string orgId)
{
DataTable dt = new DataTable();
StringBuilder sql = new StringBuilder();
sql.Append("select * from gpo_product_map");
sql.AppendFormat(" where map_orgid='{0}' and product_code='{1}'", orgId, erpProductCode);
dt = DbFacade.SQLExecuteDataTable(sql.ToString());
return(dt);
}
public DataTable GetOrderReceive(UserInfo user)
{
DataTable dt = new DataTable();
StringBuilder sql = new StringBuilder();
sql.Append("Select '1' AS TYPE,gp.CODE As PURCHASE_CODE,rec.id,oi.TRADE_PRICE As num_unit_price,");
sql.Append("rec.PROJECT_PRODUCT_ID as product_id,"); //产品ID
sql.Append(" oi.SPEC_ID,");
sql.Append(" oi.MODEL_ID,");
sql.Append(" oi.SPEC,");
sql.Append(" oi.MODEL,");
sql.Append("o.Order_Code,"); //订单编号
sql.Append("p.COMMON_NAME As cpmc2,"); //通用名
sql.Append("oi.PRODUCT_NAME As product_name,"); //商品名
sql.Append("oi.SENDER_ID,"); //卖方ID
sql.Append("oi.MANUFACTURE_NAME_ABBR As producer_shortname,"); //生产企业简称
sql.Append("oi.SENDER_NAME As seller_name,");
sql.Append("oi.SENDER_NAME_ABBR As seller_shortname,"); //卖方企业简称
sql.Append(" oi.TRADE_PRICE As Unit_Price,"); //单价
sql.Append("rec.FACT_AMOUNT AS receive_qty,"); //到货数量
sql.Append("(case when rec.FACT_SUM is null then '-' else rec.FACT_SUM end) As rec_amount,"); //到货金额
sql.Append(" ARRIVE_DATE As receive_date_short,"); //到货时间
sql.Append("rec.INSTORE_BATCH_NO AS lot_no,"); //批号
sql.Append("'0' As zbj,"); //中标价
sql.Append("oi.trade_price As gjj,"); //批发价
sql.Append("(case when oi.retail_price is null then '-' else oi.retail_price end) As lsj,"); //零售价
sql.Append("oi.AMOUNT AS gmsl,"); //购买数量
sql.Append(" convert(varchar(10),ARRIVE_DATE,120) As receive_date,"); //到货时间
sql.Append("rec.FACT_AMOUNT AS dhsl,"); //到货数量
sql.Append("rec.INSTORE_BATCH_NO as ypph,"); //产品批号
sql.Append("rec.DESCRIPTIONS as dhbz,"); //到货备注
sql.Append("rec.ID as hhdhid "); //海虹到货ID
sql.Append("From HC_ORD_ORDER_RECEIVE rec, HC_ORD_ORDER o, HC_ORD_ORDER_ITEM oi, HC_ORD_PRODUCT p,HC_ORD_PURCHASE gp ");
sql.Append("Where o.id = oi.order_id ");
sql.Append("AND oi.PROJECT_PROD_ID = p.id ");
sql.Append("AND rec.order_id = oi.order_id ");
sql.Append("AND rec.order_item_id = oi.id ");
sql.Append("AND gp.Id=o.PURCHASE_ID");
try
{
DataTable dttemp = DbFacade.SQLExecuteDataTable(sql.ToString());
dt = SetOrderReceiveDT(user, dttemp);
}
catch (Exception ex)
{
throw ex;
return(null);
}
return(dt);
}
/// <summary>
/// 取得单表主键数据
/// </summary>
/// <param name="tableName"></param>
/// <param name="buyerId"></param>
/// <param name="syncTime"></param>
/// <returns></returns>
public DataTable GetIncrementSyncTableKey(string tableName, LogedInUser CurrentUser, string syncTime)
{
DataTable table = new DataTable();
try
{
StringBuilder sqlWhere = new StringBuilder();
string sql = XmlUtil.GetSyncText(tableName, "keysql");
List <DbParameter> parameters = new List <DbParameter>();
if (!string.IsNullOrEmpty(sql))
{
if (sql.IndexOf(":Id") > 0)
{
DbParameter para1 = DbFacade.CreateParameter();
para1.ParameterName = "Id";
para1.DbType = DbType.Int64;
para1.Value = CurrentUser.UserOrg.Id;
parameters.Add(para1);
}
//else if (sql.IndexOf(":userId") > 0)
//{
// DbParameter para2 = DbFacade.CreateParameter();
// para2.ParameterName = "userId";
// para2.DbType = DbType.String;
// para2.Value = CurrentUser.UserInfo.Id;
// parameters.Add(para2);
//}
//如果查询log表，增加log时间作为查询条件
if (sql.IndexOf(":delDate") > 0)
{
DbParameter para3 = DbFacade.CreateParameter();
para3.ParameterName = "delDate";
para3.DbType = DbType.String;
para3.Value = syncTime;
parameters.Add(para3);
}
table = DbFacade.SQLExecuteDataTable(sql, "key_" + tableName, parameters.ToArray());
}
else
{
string pk = XmlUtil.GetSyncText(tableName, "Pk");
table.TableName = "key_" + tableName;
table.Columns.Add(pk);
}
}
catch (Exception e)
{
throw e;
}
return(table);
}
/// <summary>
/// 取得单表数据
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
public DataTable GetSyncTable(string tableName, LogedInUser CurrentUser)
{
DataTable table;
try
{
List <DbParameter> parameters = new List <DbParameter>();
string tbName;
if (string.IsNullOrEmpty(XmlUtil.GetSyncText(tableName, "timefield")))
{
tbName = "NoIncrement_" + tableName;
}
else
{
tbName = tableName;
}
string sql = XmlUtil.GetSyncText(tableName, "sqlstring");
if (sql.IndexOf(":Id") > 0)
{
DbParameter para1 = DbFacade.CreateParameter();
para1.ParameterName = "Id";
para1.DbType = DbType.String;
para1.Value = CurrentUser.UserOrg.Id;
parameters.Add(para1);
//table = DbFacade.SQLExecuteDataTable(sql, tbName, para);
}
else if (sql.IndexOf(":userId") > 0)
{
DbParameter para2 = DbFacade.CreateParameter();
para2.ParameterName = "userId";
para2.DbType = DbType.String;
para2.Value = CurrentUser.UserInfo.Id;
parameters.Add(para2);
//table = DbFacade.SQLExecuteDataTable(sql, tbName, para);
}
else
{
//table = DbFacade.SQLExecuteDataTable(sql, tbName);
}
table = DbFacade.SQLExecuteDataTable(sql, tbName, parameters.ToArray());
}
catch (Exception e)
{
throw e;
}
return(table);
}
/// <summary>
/// 取得单表主键数据
/// </summary>
/// <param name="tableName"></param>
/// <param name="buyerId"></param>
/// <param name="syncTime"></param>
/// <returns></returns>
public DataTable GetIncrementSyncTableKey(string tableName, LogedInUser CurrentUser)
{
DataTable table;
try
{
StringBuilder sqlWhere = new StringBuilder();
string sql = XmlUtil.GetSyncText(tableName, "keysql");
//if (sql.IndexOf(":buyerId") > 0)
//{
// DbParameter para = DbFacade.CreateParameter();
// para.ParameterName = "buyerId";
// para.DbType = DbType.String;
// para.Value = CurrentUser.UserOrg.Reg_org_id;
// table = DbFacade.SQLExecuteDataTable(sql, "key_" + tableName, para);
//}
//else
//{
// table = DbFacade.SQLExecuteDataTable(sql, "key_" + tableName);
//}
List <DbParameter> parameters = new List <DbParameter>();
if (sql.IndexOf(":Id") > 0)
{
DbParameter para1 = DbFacade.CreateParameter();
para1.ParameterName = "Id";
para1.DbType = DbType.String;
para1.Value = CurrentUser.UserOrg.Reg_org_id;
parameters.Add(para1);
}
else if (sql.IndexOf(":userId") > 0)
{
DbParameter para2 = DbFacade.CreateParameter();
para2.ParameterName = "userId";
para2.DbType = DbType.String;
para2.Value = CurrentUser.UserInfo.Id;
parameters.Add(para2);
}
table = DbFacade.SQLExecuteDataTable(sql, "key_" + tableName, parameters.ToArray());
}
catch (Exception e)
{
throw e;
}
return(table);
}
/// <summary>
/// 获取对接产品对照信息
/// </summary>
/// <param name="ProductID"></param>
/// <param name="user"></param>
/// <returns></returns>
private GpoOutReceiveModel GetProductMapByProductID(UserInfo user, string ProductID, string specid, string modelid)
{
GpoOutReceiveModel Model = new GpoOutReceiveModel();
DataTable dt = new DataTable();
StringBuilder sql = new StringBuilder();
sql.Append("Select");
sql.Append(" p.HIS_PRODUCT_ID as product_code,");
sql.Append(" p.common_name as common_name,");
sql.Append(" p.product_name as product_name,");
sql.Append(" p.mode_name as mode_name,");
sql.Append(" p.spec as medicalspec,");
sql.Append(" p.BASE_MEASURE as BASE_MEASURE,");
sql.Append(" p.BASE_MEASURE_SPEC as BASEMEASURESPEC,");
sql.Append(" (case when p.stand_rate Is Null then '0' else p.stand_rate end) as stand_rate,");
sql.Append(" (case when p.factory_code Is Null then ' ' else p.factory_code end) as factory_code,");
sql.Append(" p.factory_name as factory_name,");
sql.Append(" p.stock_id as stock_id,");
sql.Append(" p.stock_name as stock_name");
sql.Append(" From HC_PRODUCT_MAP p ");
string sql1 = string.Format(" Where PROJECT_PROD_ID = {0} And hh_SPEC_ID = {1} And hh_MODE_ID = {2}", ProductID, specid, modelid);
//string sql2 = string.Format(",GPO_REG_PRODUCT_PUBLIC grpp Where p.DATA_PRODUCT_ID = grpp.DATA_PRODUCT_ID and grpp.ID = '{0}' And p.MAP_ORGID = '{1}' ", ProductID, user.OrgId);
try
{
dt = DbFacade.SQLExecuteDataTable(sql + sql1.ToString());
//if (dt == null || dt.Rows.Count == 0)
//{
// dt = DbFacade.SQLExecuteDataTable(sql + sql2.ToString());
//}
if (dt != null && dt.Rows.Count > 0)
{
Model = GetGpoProductMapModelByRow(dt.Rows[0]);
}
}
catch (Exception ex)
{
throw ex;
return(null);
}
return(Model);
}
public DataTable GetCommList()
{
DataTable dt = new DataTable();
try
{
dt = DbFacade.SQLExecuteDataTable(GetCommListSql());
}
catch (Exception ex)
{
throw ex;
return(null);
}
return(dt);
}
public DataTable GetProjectTypeDt()
{
DataTable dt = null;
string sqlstr = "Select * From PROJECT_TYPE";
try
{
dt = DbFacade.SQLExecuteDataTable(sqlstr);
}
catch (Exception e)
{
throw e;
return(null);
}
return(dt);
}
public DataTable GetGpoMapList(string porductID)
{
DataTable dt = new DataTable();
try
{
dt = DbFacade.SQLExecuteDataTable(GetGpoMapSql(porductID));
}
catch (Exception ex)
{
throw ex;
return(null);
}
return(dt);
}
//获得新增产品编码匹配的采购供应目录列表
public DataTable GetGpoHitCommList()
{
DataTable dt = new DataTable();
try
{
dt = DbFacade.SQLExecuteDataTable(GetGpoHitCommListSql());//HitCommAndContClientDao.GetOftenPurchaseListSql()
}
catch (Exception ex)
{
throw ex;
return(null);
}
return(dt);
}
/// <summary>
/// 取得当前用户的区域列表字符串
/// </summary>
/// <param name="strUserID"></param>
/// <returns></returns>
public string GetUserAreaListString(string strUserID)
{
StringBuilder sql = new StringBuilder();
StringBuilder ids = new StringBuilder();
sql.Append("SELECT T.AREA_ID FROM GPO_USR_USER_AREA T WHERE T.USER_ID = '").Append(strUserID);
sql.Append("' AND EXISTS (SELECT 'X' FROM GPO_REG_AREA A WHERE A.ID = T.AREA_ID)");
DataTable dt = DbFacade.SQLExecuteDataTable(sql.ToString());
foreach (DataRow dr in dt.Rows)
{
ids.Append("'").Append(dr[0].ToString()).Append("',");
}
if (ids.Length > 0)
{
ids.Remove(ids.Length - 1, 1);
}
return(ids.ToString());
}
/// <summary>
///获得一条HIS产品记录
/// </summary>
/// <param name="productcode"></param>
/// <returns></returns>
public Gpo_Product_MapModel GetGpoMapModel(string productcode)
{
DataTable dt = null;
Gpo_Product_MapModel product_MapModel = null;
try
{
dt = DbFacade.SQLExecuteDataTable(GetHISItemByProductCode(productcode));
if (dt != null && dt.Rows.Count > 0)
{
product_MapModel = GetModelByRow(dt.Rows[0]);
}
}
catch (Exception e)
{
throw e;
return(null);
}
return(product_MapModel);
}
/// <summary>
/// 订单
/// </summary>
/// <param name="sOrderId"></param>
/// <returns></returns>
public DataTable GetOrderReportData(string sOrderId)
{
StringBuilder sbSql = new StringBuilder();
sbSql.Append(" select ho.id,");
sbSql.Append(" ho.order_code,");
sbSql.Append(" ho.buyer_name,");
sbSql.Append(" ho.sender_name,");
sbSql.Append(" ho.total_sum,");
sbSql.Append(" ho.over_sum,");
sbSql.Append(" ho.saler_approver_name,");
sbSql.Append(" case ho.state when '1' then '未阅读' when '2' then '已阅读' when '3' then '确认' when '4' then '处理中'when '5' then '完成' when '6' then '作废' end as state,");
sbSql.Append(" ho.purchase_date,");
sbSql.Append(" case ho.quicksend_level when '1' then '普通' when '2' then '部分紧急' when '3' then '紧急' end as quicksend_level,");
sbSql.Append(" ho.saler_descriptions,");
sbSql.Append(" ho.buyer_descriptions,");
sbSql.Append(" ho.create_date,");
sbSql.Append(" hi.product_name,");
sbSql.Append(" hi.buyer_name,");
sbSql.Append(" hi.manufacture_name,");
sbSql.Append(" hi.sender_name,");
sbSql.Append(" hi.retail_price,");
sbSql.Append(" hi.spec,");
sbSql.Append(" hi.model,");
sbSql.Append(" hi.amount ");
sbSql.Append(" from hc_ord_order ho left join hc_ord_order_item hi on ho.id = hi.order_id ");
sbSql.AppendFormat(" where ho.id = '{0}'", sOrderId);
DataTable dt = new DataTable();
try
{
dt = DbFacade.SQLExecuteDataTable(sbSql.ToString());
}
catch
{
throw;
}
return(dt);
}
//"导出采购订单"判断业务流程("进销存"企业对接功能)，shangfu 2007-8-29
public bool GetContItemInfo(string productId, string buyerorgid)
{
bool flag = false;
StringBuilder sql = new StringBuilder();
sql.Append("select ci.id from cont_item ci where ci.product_id='").Append(productId).Append("'").Append(" and ci.saler_id='").Append(buyerorgid).Append("'");
try
{
DataTable dt = DbFacade.SQLExecuteDataTable(sql.ToString());
if (dt.Rows.Count > 0)
{
flag = true;
}
}
catch (Exception e)
{
//throw e;
}
return(flag);
}
/// <summary>
/// 对接企业对照表信息
/// </summary>
/// <param name="ProductID"></param>
/// <param name="user"></param>
/// <returns></returns>
private GpoOutReceiveModel GetCorp_MapByProductID(UserInfo user, string SalerID)
{
GpoOutReceiveModel Model = new GpoOutReceiveModel();
DataTable dt = new DataTable();
StringBuilder sql = new StringBuilder();
sql.Append("Select");
sql.Append(" c.HIS_ORG_ID as psqybm,");
sql.Append(" c.EASY_NAME as psqymc");
sql.Append(" From HC_CORP_MAP c ");
string sql1 = string.Format(" Where c.ORG_ID = '{0}'", SalerID);
//string sql2 = string.Format(",GPO_REG_BUYER grb Where c.DATA_ORG_ID = grb.DATA_BUYER_ID and grb.ID = '{0}'", SalerID);
try
{
dt = DbFacade.SQLExecuteDataTable((sql + sql1).ToString());
//if (dt == null || dt.Rows.Count == 0)
//{
// dt = DbFacade.SQLExecuteDataTable((sql + sql2).ToString());
//}
if (dt != null && dt.Rows.Count > 0)
{
Model = GetGpoCorp_MapModelByRow(dt.Rows[0]);
}
}
catch (Exception ex)
{
//throw ex;
return(null);
}
return(Model);
}
/// <summary>
/// 发货
/// </summary>
/// <returns></returns>
public DataTable GetInvoiceReportData(string sInvoiceId)
{
StringBuilder sbSql = new StringBuilder();
sbSql.Append("select hf.invoice_code,");
sbSql.Append("case hf.state when '1' then '未发送' when '2' then '已发送' when '3' then '买方处理中' when '4' then '作废' when '5' then '买方处理完成' end as state,");
sbSql.Append("hf.buyer_name,");
sbSql.Append("hf.sender_name,");
sbSql.Append("hf.total_sum,");
sbSql.Append("hf.over_sum,");
sbSql.Append("hf.create_user_name,");
sbSql.Append("hf.buyer_descriptions,");
sbSql.Append("hf.create_date,");
sbSql.Append("hi.product_name,");
sbSql.Append("hi.common_name,");
sbSql.Append("case hi.state when '1' then '未确认' when '2' then '已确认' when '3' then '作废' end as item_state,");
sbSql.Append("hi.manufacture_name,");
sbSql.Append("hi.over_amount,");
sbSql.Append("hi.trade_price,");
sbSql.Append("hi.spec,");
sbSql.Append("hi.model,");
sbSql.Append("hi.amount ");
sbSql.Append("from hc_ord_invoice_from hf left join hc_ord_invoice_from_item hi on hf.id = hi.invoice_from_id ");
sbSql.AppendFormat(" where hf.id = '{0}'", sInvoiceId);
DataTable dt = new DataTable();
try
{
dt = DbFacade.SQLExecuteDataTable(sbSql.ToString());
}
catch
{
throw;
}
return(dt);
}
/// <summary>
/// 获取订单导出数据
/// </summary>
/// <param name="erpProductCode"></param>
/// <param name="orgId"></param>
/// <returns></returns>
public DataTable GetOrderExpData(string sOderId, string orgid, string clientPlat)
{
DataTable dt = null;
StringBuilder sql = new StringBuilder();
//"导出采购订单"判断业务流程("进销存"企业对接功能)，shangfu 2007-8-29
if ("1".Equals(clientPlat))
{
sql.Append(" select goi.order_id as orderid,");
sql.Append(" ghc.product_id,");
sql.Append(" goi.record_id as recordid,");
sql.Append(" ghc.buyer_id as buyerorgid,");
sql.Append(" ghc.buyer_fullname as buyername,");
sql.Append(" ghc.buyer_shortname as buyereasy,");
sql.Append(" ghc.medical_code as medicalid,");
sql.Append(" ghc.medical_name as productname,");
sql.Append(" ghc.product_id as productid,");
sql.Append(" ghc.medical_name as commonname,");
sql.Append(" '' as medicalmodeid,");
sql.Append(" ghc.doseage_form as medicalmode,");
sql.Append(" '' as medicalspecid,");
//sql.Append(" isnull(ghc.spec, '-') + '×' +");
//sql.Append(" isnull(convert(varchar(20), ghc.stand_rate), '-') + ");
//sql.Append(" isnull(ghc.use_unit, '') + '/' + isnull(ghc.spec_unit, '')");
//sql.Append(" case ghc.wrap_code");
//sql.Append(" when '01' then");
//sql.Append(" '' ");
//sql.Append(" else");
//sql.Append(" (case ghc.wrap_name");
//sql.Append(" when null then");
//sql.Append(" '' ");
//sql.Append(" else");
//sql.Append(" '(' + ghc.wrap_name + ')'");
//sql.Append(" end) as medicalspec,");
sql.Append(" ghc.spec as medicalspec,");
sql.Append(" '' as metricnameid,");
sql.Append(" ghc.use_unit as metricname,");
sql.Append(" ghc.spec_unit as specunitname,");
sql.Append(" '' as fatoryid,");
sql.Append(" ghc.producer_fullname as factoryname,");
sql.Append(" ghc.stand_rate as stand_rate,");
sql.Append(" goi.request_qty as requestqty,");
sql.Append(" goi.create_date as createdate,");
sql.Append(" goi.unit_price as price,");
sql.Append(" goi.modify_date as modifydate,");
sql.Append(" goi.degree_flag as degree,");
sql.Append(" goi.unit_price * goi.request_qty as requesttotal,");
sql.Append(" goi.remark as Remark");
sql.Append(" from gpo_purchase_item gpi,");
sql.Append(" gpo_order gor,");
sql.Append(" gpo_order_item goi,");
sql.Append(" gpo_hit_comm ghc");
sql.Append(" where gpi.id = goi.purchase_item_id");
sql.Append(" and gor.order_id = goi.order_id");
sql.Append(" and goi.hit_comm_id = ghc.record_id");
sql.Append(" and ghc.saler_id = '").Append(orgid).Append("'");
sql.Append(" and gor.order_id = '").Append(sOderId).Append("'");
}
else
{
sql.Append(" select goi.order_id as orderid,");
sql.Append(" ghc.product_id ,");
sql.Append(" goi.record_id as recordid,"); //ddmxbm
sql.Append(" gcm.code as buyerorgid,"); //yybm
sql.Append(" gcm.full_name as buyername,"); //yymc
sql.Append(" gcm.easy_name as buyereasy,"); //yyjc
sql.Append(" gpm.medical_code as medicalid,");
sql.Append(" gpm.product_name as productname,");
sql.Append(" gpm.product_code as productid,"); //cpbh
sql.Append(" gpm.common_name as commonname,"); //cpmc
sql.Append(" gpm.mode_id as medicalmodeid,");
sql.Append(" gpm.mode_name as medicalmode,"); //jxmc
sql.Append(" gpm.medical_spec_id as medicalspecid,");
sql.Append(" gpm.medical_spec as medicalspec,"); //ggbzmc
sql.Append(" gpm.use_unit_id as metricnameid,");
sql.Append(" gpm.use_unit as metricname,");
sql.Append(" gpm.spec_unit as specunitname,"); //bzdw
sql.Append(" gpm.factory_code as fatoryid,"); //scqybm
sql.Append(" gpm.factory_name as factoryname,"); //scqymc
sql.Append(" gpm.stand_rate as stand_rate,"); //bzzhb
sql.Append(" goi.request_qty as requestqty,"); //dgsl
sql.Append(" goi.create_date as createdate,"); //dgrq
sql.Append(" goi.unit_price as price,"); //dgj
sql.Append(" goi.modify_date as modifydate,");
sql.Append(" goi.degree_flag as degree,");
sql.Append(" goi.unit_price*goi.request_qty as requesttotal,"); //dgje
sql.Append(" goi.remark as Remark "); //yybz
sql.Append(" from gpo_order_item goi left outer join gpo_order go");
sql.Append(" on goi.order_id = go.order_id collate Chinese_PRC_CI_AI_WS ");
sql.Append(" left outer join gpo_hit_comm ghc ");
sql.Append(" on ghc.record_id = goi.hit_comm_id collate Chinese_PRC_CI_AI_WS ");
sql.Append(" left outer join (SELECT ORG_ID, max(CODE) as CODE, max(FULL_NAME) as FULL_NAME, max(EASY_NAME) as EASY_NAME FROM GPO_CORP_MAP group by ORG_ID) gcm ");
sql.Append(" on go.buyer_orgid = gcm.org_id collate Chinese_PRC_CI_AI_WS ");
sql.Append(" left outer join ");
sql.Append(" (select product_id,max(medical_code) as medical_code, max(common_name) as common_name,max(product_code) as product_code, ");
sql.Append(" max(product_name) as product_name, max(mode_id) as mode_id, max(mode_name) as mode_name, max(medical_spec_id) as medical_spec_id,");
sql.Append(" max(medical_spec) as medical_spec, max(use_unit_id) as use_unit_id, max(use_unit) as use_unit,");
sql.Append(" max(spec_unit) as spec_unit, max(factory_code) as factory_code, max(factory_name) as factory_name,");
sql.Append(" max(stand_rate) as stand_rate from ");
sql.Append(" gpo_product_map group by product_id) gpm ");
sql.Append(" on ghc.product_id = gpm.product_id collate Chinese_PRC_CI_AI_WS ");
sql.AppendFormat(" where goi.order_id = '{0}'", sOderId);
}
try
{
dt = DbFacade.SQLExecuteDataTable(sql.ToString());
}
catch (Exception e)
{
//throw e;
}
return(dt);
}
/// <summary>
/// 取得单表增量数据
/// </summary>
/// <param name="tableName"></param>
/// <param name="buyerId"></param>
/// <param name="syncTime"></param>
/// <returns></returns>
public DataTable GetIncrementSyncTable(string tableName, LogedInUser CurrentUser, string syncTime)
{
DataTable table;
try
{
StringBuilder sqlWhere = new StringBuilder();
string sql = XmlUtil.GetSyncText(tableName, "sqlstring");
string temp = XmlUtil.GetSyncText(tableName, "timefield");
List <DbParameter> parameters = new List <DbParameter>();
if (!string.IsNullOrEmpty(temp))
{
string[] timeField = temp.Split(new char[] { ',' });
if (timeField.Length == 1)
{
sqlWhere.Append(" and ").Append(timeField[0]);
sqlWhere.Append(" >= to_date(:syncTime,'yyyy-mm-dd hh24:mi:ss') ");
//sqlWhere.Append(" >= to_date('").Append(syncTime).Append("# ");
DbParameter syncTimePara = DbFacade.CreateParameter();
syncTimePara.ParameterName = "syncTime";
syncTimePara.DbType = DbType.String;
syncTimePara.Value = syncTime;
parameters.Add(syncTimePara);
}
if (timeField.Length == 2)
{
sqlWhere.Append(" and (").Append(timeField[0]);
sqlWhere.Append(" >= to_date(:syncTime1,'yyyy-mm-dd hh24:mi:ss') or ");
sqlWhere.Append(timeField[1]).Append(" >= to_date(:syncTime2,'yyyy-mm-dd hh24:mi:ss'))");
//sqlWhere.Append(" >= #").Append(syncTime).Append("# or ");
//sqlWhere.Append(timeField[1]).Append(" >= #").Append(syncTime).Append("#) ");
DbParameter syncTimePara1 = DbFacade.CreateParameter();
syncTimePara1.ParameterName = "syncTime1";
syncTimePara1.DbType = DbType.String;
syncTimePara1.Value = syncTime;
parameters.Add(syncTimePara1);
DbParameter syncTimePara2 = DbFacade.CreateParameter();
syncTimePara2.ParameterName = "syncTime2";
syncTimePara2.DbType = DbType.String;
syncTimePara2.Value = syncTime;
parameters.Add(syncTimePara2);
}
}
else
{
sqlWhere.Append(" and 1=1 ");
}
if (sql.IndexOf(":Id") > 0)
{
DbParameter para1 = DbFacade.CreateParameter();
para1.ParameterName = "Id";
para1.DbType = DbType.Int64;
para1.Value = CurrentUser.UserOrg.Id;
parameters.Add(para1);
table = DbFacade.SQLExecuteDataTable(sql + sqlWhere.ToString(), tableName, parameters.ToArray());
}
else if (sql.ToLower().IndexOf("where") > 0)
{
table = DbFacade.SQLExecuteDataTable(sql + sqlWhere.ToString(), tableName, parameters.ToArray());
}
else
{
table = DbFacade.SQLExecuteDataTable(sql + " where " + sqlWhere.Remove(0, 4).ToString(), tableName, parameters.ToArray());
}
}
catch (Exception e)
{
throw e;
}
return(table);
}
//已到货列表的检索
public DataSet getArrivedList(UserInfo ui, string type, string id, BuyerOrderModel input)
{
StringBuilder sql = new StringBuilder();
//sql.Append(" SELECT ");
//sql.Append(" H.MEDICAL_NAME, "); //--通用名
//sql.Append(" H.TRADE_NAME, "); //--商品名
//sql.Append(" H.MEDICAL_WUBI, "); //--商品名
//sql.Append(" H.MEDICAL_PINYIN, "); //--商品名
//sql.Append(" H.SPELL_ABBR, "); //--商品名
//sql.Append(" H.NAME_WB, "); //--商品名
//sql.Append(" iif(h.Spec is null,'-',h.spec) & '×' & iif(h.Stand_Rate Is Null, '-',h.Stand_Rate) & iif(h.Use_Unit Is Null, '',h.Use_Unit) & '/' & iif(h.Spec_Unit Is Null, '',h.Spec_Unit) & Switch(h.Wrap_Name Is Null, '', h.Wrap_Name='空', '',True,'(' & h.Wrap_Name & ')') As Ggbz,");//规格包装
//sql.Append(" H.DOSEAGE_FORM, ");
//sql.Append(" Switch(h.Province_Max_Price Is Null,'-',True,Trim(Format(h.Province_Max_Price, 'Standard'))) As Province_Max_Price,");
//sql.Append(" Switch(h.Province_Insurance_Flag='0','非国家基本医疗保险产品',h.Province_Insurance_Flag='1', '甲类', h.Province_Insurance_Flag='2', '乙类', h.Province_Insurance_Flag='3', '民族药') As Province_Insurance_Flag,");
//sql.Append(" H.DEALER_FULLNAME, "); //--经销企业
//sql.Append(" L.NAME, "); //--合同名称
//sql.Append(" H.LAST_ORDER_DATE, "); //--最后制单日期
//sql.Append(" CInt(H.LAST_ORDER_QTY) as LAST_ORDER_QTY, "); //--最后制单数量
//sql.Append(" Switch(Oi.Con_Type='1', '招标', Oi.Con_Type='2', '竞价', Oi.Con_Type='3', '询价', Oi.Con_Type='4', '备案', Oi.Con_Type='7', '浏览', Oi.Con_Type='9', '临时', Oi.Con_Type='c','GPO直销', Oi.Con_Type='d', 'GPO自主合同') as Con_Type,");//--合同类型
//sql.Append(" format(OI.UNIT_PRICE,'Standard') As UNIT_PRICE, ");//单价
//sql.Append(" CInt(OI.REQUEST_QTY) as REQUEST_QTY, "); //--定购量
//sql.Append(" '完成' AS STATUS, "); //--状态
//sql.Append(" iif(REC.LOT_NO is null,'-',REC.LOT_NO) AS LOT_NO, "); //--批号
//sql.Append(" CInt(REC.RECEIVE_QTY) as RECEIVE_QTY, "); //--确认到货量
//sql.Append(" OI.REMARK, "); //--备注
//sql.Append(" OI.BUYER_DESC, "); //--买方备注
//sql.Append(" REC.RECEIVE_REMARK, "); //--到货接收备注
//sql.Append(" REC.INVOICE_NO, "); //--发票号
//sql.Append(" REC.AMOUNT, "); //--发票金额
//sql.Append(" format(REC.TRADE_PRICE,'Standard') As TRADE_PRICE,");//--批发价
//sql.Append(" format(REC.RETAIL_PRICE,'Standard') As RETAIL_PRICE,");//--零售价
//sql.Append(" REC.DISCOUNT, "); //--扣率
//sql.Append(" format(REC.INVOICE_DATE,'yyyy-mm-dd') As INVOICE_DATE,");//--开票日期
//sql.Append(" format(REC.INVOICE_EXPIRE_DATE,'yyyy-mm-dd') As INVOICE_EXPIRE_DATE ");//--有效期
//sql.Append(" FROM GPO_ORDER_RECEIVE REC, GPO_ORDER_ITEM OI, GPO_HIT_COMM H ,CONT_LIST L ");
//sql.Append(" WHERE REC.ORDER_ITEM_ID = OI.RECORD_ID ");
//sql.Append(" AND OI.HIT_COMM_ID = H.RECORD_ID ");
//sql.Append(" AND H.CONTRACT_ID = L.ID ");
//sql.Append(" AND REC.ORDER_ID = :ID");
sql.Append(" SELECT h.id as Product_Id,h.MANU_NAME,h.MANU_NAME_ABBR, h.COMMERCE_NAME as ");
sql.Append(" Trade_Name,h.product_name ,h.CODE, h.common_name,h.ABBR_PY,h.ABBR_WB,");
sql.Append(" OI.model,(case when OI.Spec is null then '-' else OI.spec end) as Spec ,");
sql.Append(" oi.Id, oi.Order_Id, ");
sql.Append(" (case when oi.trade_Price Is Null then '-' else oi.trade_Price end) As ");
sql.Append(" trade_Price,oi.sender_Name,oi.sender_Name_Abbr,");
sql.Append(" (case oi.STATE when '1' then '发送' when '2' then '已阅读' when '3' then '作废' when '4' then '已确认' when '5' then '完成' end) As status, ");
sql.Append(" (case when oi.RETAIL_PRICE Is Null then '-' else oi.RETAIL_PRICE end) ");
sql.Append(" As RETAIL_PRICE,oi.SUM AS total,");
sql.Append(" cast(oi.AMOUNT as int) As Request_Qty, oi.SALER_DESCRIPTIONS as Saler_Desc,");
sql.Append(" oi.BUYER_DESCRIPTIONS as Buyer_Desc, '已送货' As Status,");
sql.Append(" cast(oi.OVER_AMOUNT as int) as OVER_AMOUNT,");
sql.Append(" cast(oi.OVER_SUM as int) as OVER_SUM,(case when oi.Send_measure is null then '-' else oi.Send_measure end ) as Send_measure,");
sql.Append(" (case when REC.INSTORE_BATCH_NO is null then '-' else REC.INSTORE_BATCH_NO end) ");
sql.Append(" AS LOT_NO, ");
sql.Append(" cast(REC.FACT_AMOUNT as int) as RECEIVE_QTY, ");
sql.Append(" REC.DESCRIPTIONS, ");
sql.Append(" REC.INVOICE_ID, ");
sql.Append(" REC.PRICE As TRADE_PRICE");
sql.Append(" FROM HC_ORD_ORDER_RECEIVE REC, HC_ORD_ORDER_ITEM OI, HC_ORD_PRODUCT H ");
sql.Append(" WHERE REC.ORDER_ITEM_ID = OI.ID AND OI.PROJECT_PROD_ID = H.ID ");
sql.Append(" AND REC.ORDER_ID = @ID and OI.state <> '3'");
List <DbParameter> parameters = new List <DbParameter>();
DbParameter idPara = DbFacade.CreateParameter();
idPara.ParameterName = "ID";
idPara.DbType = DbType.String;
idPara.Value = id;
parameters.Add(idPara);
DataSet ds = new DataSet();
DataTable newsTable = DbFacade.SQLExecuteDataTable(sql.ToString(), "ARRIVESALER", idPara);
ds.Tables.Add(newsTable);
return(ds);
}
/// <summary>
/// 查询退货单列表
/// </summary>
/// <param name="plats">受管理的平台集合</param>
/// <param name="input">页面输入参数</param>
/// <param name="pageParam">分业参数</param>
/// <param name="rows">数据行数</param>
/// <returns></returns>
public DataTable findDealList(string[] plats, SalerReturnModel input, PagedParameter pageParam, out int rows)
{
//组织所有的交易所平台ＩＤ
rows = 0;
DataTable dt = null;
StringBuilder sqlPlat = new StringBuilder();
for (int i = 0; i < plats.Length; i++)
{
// PltPlat p1 = (PltPlat)it.next();
if (i == 0)
{
sqlPlat.Append("'" + plats[i] + "'");
}
else
{
sqlPlat.Append(",'" + plats[i] + "'");
}
i++;
}
if (sqlPlat.Length == 0)
{
return(null);
}
//根据查询类别，组织查询条件
StringBuilder sb = new StringBuilder();
List <DbParameter> parameters = new List <DbParameter>();
DbParameter param = null;
if (input.StrType != null && !input.StrKeyValue.Equals(""))
{
if (input.StrType.Equals("1"))
{
sb.Append(" and (item.bak_medical_name like :StrKeyValue");
sb.Append(" or item.bak_product_name like :StrKeyValue)");
}
else if (input.StrType.Equals("2"))
{
sb.Append(" and (item.bak_medical_fast like :StrKeyValue");
sb.Append(" or item.bak_product_fast like :StrKeyValue)");
}
else if (input.StrType.Equals("3"))
{
sb.Append(" and (item.bak_medical_wubi like :StrKeyValue");
sb.Append(" or item.bak_product_wubi like :StrKeyValue)");
}
else if (input.StrType.Equals("4"))
{
sb.Append(" and (ord.bak_buyer_easy like :StrKeyValue");
sb.Append(" or ord.bak_buyer_name like :StrKeyValue)");
}
else
{
return(null);
}
param = this.DbFacade.CreateParameter();
param.ParameterName = "StrKeyValue";
param.DbType = DbType.String;
if (input.StrType.Equals("1") || input.StrType.Equals("4"))
{
param.Value = CommonFunction.GetLike(input.StrKeyValue);
}
else
{
param.Value = CommonFunction.GetLike(input.StrKeyValue.ToUpper());
}
parameters.Add(param);
}
StringBuilder strSql = new StringBuilder();
strSql.Append(" select rownum, r.id receive_id, r.receive_qty receive_qty_pre, t.id,item.bak_medical_name,item.bak_product_name,item.BAK_MASS_ASSIGNMENT,item.bak_medical_mode,item.bak_product_spec"
+ " ,ord.bak_buyer_easy,ord.bak_buyer_name "
+ " ,item.unit_price,t.lot_no,w.warehouse_name,r.receive_date,r.receive_qty,t.return_qty,t.buyer_remark"
+ " ,t.create_date,to_char(t.create_date,'yyyy-mm-dd') create_date_display,t.confirm_date,to_char(t.confirm_date,'yyyy-mm-dd') confirm_date_display,t.saler_remark Remark "
+ ", ord.order_code, ord.create_date order_date, t.create_userid, item.unit_price * t.return_qty return_money"
+ ", item.product_id"
+ " from ord_order_receive r,ord_order ord,ord_order_item item, ord_warehouse w,ord_order_return t"
+ " where t.order_receive_id=r.id and t.order_id=ord.order_id and t.order_item_id =item.record_id "
+ " and r.order_item_id = item.record_id and ord.order_id=item.order_id and item.repository_id =w.warehouse_id(+)"
+ " and t.sender_orgid = :orgid"
+ " and t.return_state = :returnState"
+ " and t.create_date >= to_date(:startDate,'yyyy-mm-dd hh24:mi:ss')"
+ " and t.create_date<= to_date(:endDate,'yyyy-mm-dd hh24:mi:ss') + 1"
+ " and t.plat_id in ("
+ sqlPlat.ToString()
+ ")"
+ sb.ToString());
param = this.DbFacade.CreateParameter();
param.ParameterName = "orgid";
param.DbType = DbType.String;
param.Value = input.CurOrgId;
parameters.Add(param);
param = this.DbFacade.CreateParameter();
param.ParameterName = "returnState";
param.DbType = DbType.String;
param.Value = input.ReturnState;
parameters.Add(param);
param = this.DbFacade.CreateParameter();
param.ParameterName = "startDate";
param.DbType = DbType.String;
param.Value = input.StartDate;
parameters.Add(param);
param = this.DbFacade.CreateParameter();
param.ParameterName = "endDate";
param.DbType = DbType.String;
param.Value = input.EndDate;
parameters.Add(param);
try
{
rows = base.GetRowCount(strSql.ToString(), parameters.ToArray());
//该sql需要用户自己绑定:highRowNum(该页的最大记录行数)和:lowRowNum(该页的最小记录行数)参数.
DbParameter highIndexPara = DbFacade.CreateParameter();
highIndexPara.ParameterName = "highRowNum";
highIndexPara.DbType = DbType.Int32;
highIndexPara.Value = PageUtils.GetHighIndexOfPage(int.Parse(pageParam.PageNum), int.Parse(pageParam.PageSize));
parameters.Add(highIndexPara);
DbParameter lowIndexPara = DbFacade.CreateParameter();
lowIndexPara.ParameterName = "lowRowNum";
lowIndexPara.DbType = DbType.Int32;
lowIndexPara.Value = PageUtils.GetLowIndexOfPage(int.Parse(pageParam.PageNum), int.Parse(pageParam.PageSize));
parameters.Add(lowIndexPara);
dt = DbFacade.SQLExecuteDataTable(GetPagedSql(strSql.ToString()), parameters.ToArray());
}
catch (Exception e)
{
throw e;
}
return(dt);
}
/// <summary>
/// 查询 产品编码对照列表信息
/// </summary>
/// <param name="input">产品编码对照表实体类</param>
/// <returns></returns>
public DataTable ProductCodeCompareList(Gpo_Product_MapModel input)
{
DataTable dt = new DataTable();
List <DbParameter> parameters = new List <DbParameter>();
DbParameter param;
if (!string.IsNullOrEmpty(input.IsMap))
{
//匹配状态
param = base.DbFacade.CreateParameter();
param.ParameterName = "IsMap";
param.DbType = DbType.String;
param.Value = input.IsMap;
parameters.Add(param);
}
if (!string.IsNullOrEmpty(input.ProcessFlag))
{
//处理状态
param = base.DbFacade.CreateParameter();
param.ParameterName = "PROCESS_FLAG";
param.DbType = DbType.String;
param.Value = input.ProcessFlag;
parameters.Add(param);
}
if (!string.IsNullOrEmpty(input.MedicaName))
{
//药品名称
param = base.DbFacade.CreateParameter();
param.ParameterName = "MedicaName";
param.DbType = DbType.String;
param.Value = '%' + input.MedicaName + '%';
parameters.Add(param);
}
if (!string.IsNullOrEmpty(input.FactoryName))
{
//生产企业
param = base.DbFacade.CreateParameter();
param.ParameterName = "FACTORY_NAME";
param.DbType = DbType.String;
param.Value = '%' + input.FactoryName + '%';
parameters.Add(param);
}
if (!string.IsNullOrEmpty(input.ProductCode))
{
//产品名称
param = base.DbFacade.CreateParameter();
param.ParameterName = "PRODUCT_CODE";
param.DbType = DbType.String;
param.Value = '%' + input.ProductCode + '%';
parameters.Add(param);
}
try
{
dt = DbFacade.SQLExecuteDataTable(GetGpo_Produce_MapListSql(input), parameters.ToArray());
}
catch (Exception ex)
{
throw ex;
return(null);
}
return(dt);
}
/// <summary>
/// 获取到货信息
/// </summary>
/// <param name="ui">用户对象</param>
/// <param name="startDate">开始时间</param>
/// <param name="endDate">结束时间</param>
/// <returns></returns>
public DataTable GetOrderReceive(UserInfo user, string startDate, string endDate, string medicalName, string salerName)
{
DataTable dt = new DataTable();
StringBuilder sql = new StringBuilder();
sql.Append("Select '1' AS TYPE,gp.CODE As PURCHASE_CODE,rec.id,oi.TRADE_PRICE As num_unit_price,");
sql.Append("rec.PROJECT_PRODUCT_ID as product_id,"); //产品ID
sql.Append(" oi.SPEC_ID,");
sql.Append(" oi.MODEL_ID,");
sql.Append(" oi.SPEC,");
sql.Append(" oi.MODEL,");
sql.Append("o.Order_Code,"); //订单编号
sql.Append("p.COMMON_NAME As cpmc2,"); //通用名
sql.Append("oi.PRODUCT_NAME As product_name,"); //商品名
sql.Append("oi.SENDER_ID,"); //卖方ID
sql.Append("oi.MANUFACTURE_NAME_ABBR As producer_shortname,"); //生产企业简称
//sql.Append("hit.producer_fullname As producer_fullname,"); //生产企业全称
//sql.Append("hit.dealer_shortname As dealer_shortname,"); //经销企业简称
//sql.Append("hit.dealer_fullname As dealer_fullname,"); //经销企业全称
//sql.Append("o.bak_buyer_easy As buyer_shortname,"); //买方企业简称
//sql.Append("o.bak_buyer_name As buyer_fullname,"); //买方企业全称
sql.Append("oi.SENDER_NAME As seller_name,");
sql.Append("oi.SENDER_NAME_ABBR As seller_shortname,"); //卖方企业简称
//sql.Append("o.saler_name As seller_fullname,"); //卖方企业全称
sql.Append(" oi.TRADE_PRICE As Unit_Price,"); //单价
sql.Append("rec.FACT_AMOUNT AS receive_qty,"); //到货数量
//sql.Append("oi.request_qty AS request_qty,"); //订购数量
sql.Append("(case when rec.FACT_SUM is null then '-' else rec.FACT_SUM end) As rec_amount,");//到货金额
//sql.Append("Format(iif(hit.temp_price Is Null,hit.province_max_price,hit.temp_price),'Standard') As retail_price,");//零售价
//sql.Append("rec.receive_date As receive_date,"); //到货时间
sql.Append("convert(varchar(10),ARRIVE_DATE,120) As receive_date_short,");//到货时间
//sql.Append("rec.invoice_no AS invoice_no,"); //发票号
sql.Append("rec.INSTORE_BATCH_NO AS lot_no,"); //批号
//sql.Append("rec.retail_price AS invoice_retail_price,"); //零售价
//sql.Append("rec.discount AS invoice_discount,"); //扣率
//sql.Append("rec.invoice_date AS invoice_date,"); //开票日期
//sql.Append("format(rec.invoice_date, 'yyyy-mm-dd') As invoice_date_short,");//开票日期
//sql.Append("rec.invoice_expire_date AS invoice_expire_date,"); //发票有效期
//sql.Append("rec.RECEIVE_REMARK AS receive_remark, "); //到货接收备注
sql.Append("'0' As zbj,"); //中标价
sql.Append("oi.trade_price As gjj,"); //批发价
sql.Append("(case when oi.retail_price is null then '-' else oi.retail_price end) As lsj,"); //零售价
sql.Append("oi.AMOUNT AS gmsl,"); //购买数量
sql.Append(" convert(varchar(10),ARRIVE_DATE,120) As receive_date,"); //到货时间
//sql.Append("rec.invoice_no AS fphm,"); //发票号码
//sql.Append("rec.invoice_total As fpzje,"); //发票总金额
//sql.Append("iif(rec.amount is null,'-',rec.amount) As fpje,"); //发票金额
//sql.Append("iif(rec.invoice_date is null,'-',rec.invoice_date) AS fprq,"); //开票日期
sql.Append("rec.FACT_AMOUNT AS dhsl,"); //到货数量
sql.Append("rec.INSTORE_BATCH_NO as ypph,"); //产品批号
//sql.Append("rec.invoice_expire_date as ypxq,"); //产品效期
sql.Append("rec.DESCRIPTIONS as dhbz,"); //到货备注
sql.Append("rec.ID as hhdhid "); //海虹到货ID
sql.Append("From HC_ORD_ORDER_RECEIVE rec, HC_ORD_ORDER o, HC_ORD_ORDER_ITEM oi, HC_ORD_PRODUCT p,HC_ORD_PURCHASE gp ");
sql.Append("Where o.id = oi.order_id ");
sql.Append("AND oi.PROJECT_PROD_ID = p.id ");
sql.Append("AND rec.order_id = oi.order_id ");
sql.Append("AND rec.order_item_id = oi.id ");
sql.Append("AND gp.Id=o.PURCHASE_ID");
//sql.AppendFormat(" AND o.BUYER_ORGID='{0}'", user.OrgId);
//if (!string.IsNullOrEmpty(startDate))
//{
// sql.AppendFormat(" AND FORMAT(rec.RECEIVE_DATE,'YYYY/MM/DD')>='{0}'", startDate);
//}
//if (!string.IsNullOrEmpty(endDate))
//{
// sql.AppendFormat(" AND FORMAT(rec.RECEIVE_DATE,'YYYY/MM/DD')<='{0}'", endDate);
//}
if (!string.IsNullOrEmpty(startDate))
{
sql.AppendFormat(" AND rec.ARRIVE_DATE >= '{0}'", startDate + " 00:00:00");
}
//结束时间
if (!string.IsNullOrEmpty(endDate))
{
sql.AppendFormat(" AND rec.ARRIVE_DATE <= '{0}'", endDate + " 23:59:59");
}
//品名
if (!string.IsNullOrEmpty(medicalName))
{
sql.AppendFormat(" AND (p.PRODUCT_NAME Like '%{0}%' or p.COMMERCE_NAME Like '%{0}%' or p.COMMON_NAME Like '%{0}%' or p.CODE Like '%{0}%' or p.ABBR_PY Like '%{0}%' or p.ABBR_WB Like '%{0}%') ", medicalName);
}
//卖方企业
if (!string.IsNullOrEmpty(salerName))
{
sql.AppendFormat(" AND (oi.SALER_NAME Like '%{0}%' or oi.SALER_NAME_ABBR Like '%{0}%')", salerName);
}
try
{
DataTable dttemp = DbFacade.SQLExecuteDataTable(sql.ToString());
dt = SetOrderReceiveDT(user, dttemp);
}
catch (Exception ex)
{
throw ex;
return(null);
}
return(dt);
}