/// <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) { //组织所有的交易所平台ID 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); }