/// <summary> /// 取得按企业到货列表数据 /// </summary> public DataSet GetBuyerOrderByOrgList(BuyerOrderModel input, out int rows) { DataSet ds = new DataSet(); StringBuilder sql = new StringBuilder(); try { //sql.Append("select distinct o.saler_id,"); //sql.Append(" o.saler_name as saler_name,"); //sql.Append(" o.saler_easy as saler_easy,"); //sql.Append(" count(1) as order_num, "); //sql.Append(" Trim(Format(Sum(o.Request_Total), 'Standard')) As request_total, "); //sql.Append(" Sum(o.Request_Total) As request_total_num "); //sql.Append(" from gpo_order o"); sql.Append("Select a.*,b.Org_Name As Saler_Name, b.Org_Easy As Saler_Easy"); sql.Append(" From"); sql.Append(" ("); sql.Append(" Select Distinct o.Saler_Id, Count(1) As Order_Num"); sql.Append(" ,Trim(Format(Sum(o.Request_Total), 'Standard')) As Request_Total,"); sql.Append(" Sum(o.Request_Total) As Request_Total_Num"); sql.Append(" From Gpo_Order o"); List <DbParameter> parameters = new List <DbParameter>(); sql.Append(" where 1=1 "); //sql.Append(" and o.order_state = '2' "); sql.Append(" and o.area_id = :areaId"); DbParameter areaIdPara = this.DbFacade.CreateParameter(); areaIdPara.ParameterName = "areaId"; areaIdPara.DbType = DbType.String; areaIdPara.Value = input.AreaId; parameters.Add(areaIdPara); sql.Append(" and o.buyer_orgid = :orgId"); DbParameter orgIdPara = this.DbFacade.CreateParameter(); orgIdPara.ParameterName = "orgId"; orgIdPara.DbType = DbType.String; orgIdPara.Value = input.OrgId; parameters.Add(orgIdPara); //查询条件:订单状态 if (!String.IsNullOrEmpty(input.OrderState)) { sql.Append(" and o.order_state = :state"); DbParameter statePara = this.DbFacade.CreateParameter(); statePara.ParameterName = "state"; statePara.DbType = DbType.String; statePara.Value = input.OrderState; parameters.Add(statePara); } //查询条件:发送时间开始 if (!String.IsNullOrEmpty(input.StartDate)) { sql.Append(" and o.create_date>=CDate(:startDate) "); //sql.Append(" and o.create_date>=CDate(:startDate,'yyyy-mm-dd hh24:mi:ss') "); //sql.Append(input.StartDate); //sql.Append("','yyyy-mm-dd hh24:mi:ss') "); DbParameter startDatePara = this.DbFacade.CreateParameter(); startDatePara.ParameterName = "startDate"; startDatePara.DbType = DbType.String; startDatePara.Value = ComUtil.formatDate(input.StartDate); parameters.Add(startDatePara); } //查询条件:发送时间开始 if (!String.IsNullOrEmpty(input.EndDate)) { sql.Append(" and o.create_date<=CDate(:EndDate) "); DbParameter EndDatePara = this.DbFacade.CreateParameter(); EndDatePara.ParameterName = "EndDate"; EndDatePara.DbType = DbType.String; EndDatePara.Value = ComUtil.formatDate(input.EndDate); parameters.Add(EndDatePara); } ////查询条件:发送时间结束 //if (!String.IsNullOrEmpty(input.EndDate)) //{ // sql.Append(" and o.create_date<=CDate(':endDate') "); // //sql.Append(" and o.create_date<=to_date(:endDate,'yyyy-mm-dd hh24:mi:ss') "); // //sql.Append(input.EndDate); // //sql.Append("','yyyy-mm-dd hh24:mi:ss') "); // DbParameter endDatePara = this.DbFacade.CreateParameter(); // endDatePara.ParameterName = "endDate"; // endDatePara.DbType = DbType.String; // endDatePara.Value = ComUtil.formatDate(input.EndDate); // parameters.Add(endDatePara); //} //查询条件:创建人 if (!String.IsNullOrEmpty(input.Creater)) { sql.Append(" and UCase(o.create_username) like :creater"); DbParameter createrPara = this.DbFacade.CreateParameter(); createrPara.ParameterName = "creater"; createrPara.DbType = DbType.String; createrPara.Value = ComUtil.GetLike(input.Creater.ToUpper()); parameters.Add(createrPara); } //查询条件:1卖方企业或2订单号 if ("1".Equals(input.SearchField)) { if (!String.IsNullOrEmpty(input.SearchKey)) { sql.Append(" and UCase(o.SALER_NAME & o.SALER_EASY & o.SALER_FAST & o.SALER_WUBI) like :org "); DbParameter orgPara = this.DbFacade.CreateParameter(); orgPara.ParameterName = "org"; orgPara.DbType = DbType.String; orgPara.Value = ComUtil.GetLike(input.SearchKey.ToUpper()); parameters.Add(orgPara); } } if ("2".Equals(input.SearchField)) { if (!String.IsNullOrEmpty(input.SearchKey)) { sql.Append(" and UCase(o.order_code) like :code "); DbParameter codePara = this.DbFacade.CreateParameter(); codePara.ParameterName = "code"; codePara.DbType = DbType.String; codePara.Value = ComUtil.GetLike(input.SearchKey.ToUpper()); parameters.Add(codePara); } } sql.Append(" Group by o.saler_id"); sql.Append(" ) a,(Select Distinct b.org_id,b.Org_Name,b.Org_Easy From cont_org b) b"); sql.Append(" Where a.Saler_Id=b.Org_id"); input.Rows = base.GetRowCount(sql.ToString(), parameters.ToArray()); rows = input.Rows; //DbParameter highIndexPara = DbFacade.CreateParameter(); //highIndexPara.ParameterName = "highRowNum"; //highIndexPara.DbType = DbType.Int32; //highIndexPara.Value = input.End; //parameters.Add(highIndexPara); //DbParameter lowIndexPara = DbFacade.CreateParameter(); //lowIndexPara.ParameterName = "lowRowNum"; //lowIndexPara.DbType = DbType.Int32; //lowIndexPara.Value = input.Start; //parameters.Add(lowIndexPara); DataTable tb = base.DbFacade.SQLExecuteDataTable(sql.ToString(), parameters.ToArray()); ds.Tables.Add(tb); } catch (Exception e) { throw e; } return(ds); }
//按企业,获得分页的订单明细信息 public DataSet GetOrderDetailList(OrderDetailSearchModel input, string salerId, string buyerId, string areaId, string productName, PagedParameter pageParam, out int rows) { int pageNum = Int32.Parse(pageParam.PageNum); int pageSize = Int32.Parse(pageParam.PageSize); string sql = @"select h.trade_name, h.medical_name, 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, Switch(i.unit_price Is Null,'-',True,Trim(Format(i.unit_price, 'Standard'))) As unit_price, i.record_id, CLng(iif(i.request_qty is null,0,i.request_qty)) as request_qty, i.saler_desc, Switch(i.item_status='1','发送',i.item_status='2', '已阅读', i.item_status='3', '已送货', i.item_status='5', '作废', i.item_status='6', '缺货', i.item_status='7', '完成') as item_status, Switch(i.order_type='0','蓝票', i.order_type='1','到货', i.order_type='2','红票') As order_type, h.doseage_form, h.province_max_price, 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, h.dealer_fullname, l.name, h.last_order_date, CLng(iif(h.last_order_qty is null,0,h.last_order_qty)) as last_order_qty, Switch(i.con_type='1', '招标', i.con_type='2', '竞价', i.con_type='3', '询价', i.con_type='4', '备案', i.con_type='7', '浏览', i.con_type='9', '临时', i.con_type='c', 'GPO直销', i.con_type='d', 'GPO自主合同') as con_type, h.medical_id, h.medical_wubi, h.medical_pinyin, h.medical_code, h.spell_abbr, h.name_wb from gpo_order_item i, gpo_hit_comm h, gpo_order o, cont_list l where i.hit_comm_id = h.record_id and h.contract_id = l.id and o.order_id = i.order_id and o.saler_id = :salerId and i.buyer_orgid = :buyerId and o.area_id = :areaId"; //modify by yanbing 2007-07-10 //and o.order_state = '2' if (!String.IsNullOrEmpty(input.OrderState)) { sql = sql + " and o.order_state =:order_state"; } //查询条件:发送时间开始 if (!String.IsNullOrEmpty(input.StartDate)) { sql = sql + string.Format(" and o.create_date>CDate('{0}')", input.StartDate); } //查询条件:发送时间结束 if (!String.IsNullOrEmpty(input.EndDate)) { sql = sql + string.Format(" and o.create_date<=CDate('{0}')", input.EndDate); } //end modify List <DbParameter> parameters = new List <DbParameter>(); DbParameter salerIdPara = this.DbFacade.CreateParameter(); salerIdPara.ParameterName = "salerId"; salerIdPara.DbType = DbType.String; salerIdPara.Value = salerId; parameters.Add(salerIdPara); DbParameter buyerIdPara = this.DbFacade.CreateParameter(); buyerIdPara.ParameterName = "buyerId"; buyerIdPara.DbType = DbType.String; buyerIdPara.Value = buyerId; parameters.Add(buyerIdPara); DbParameter areaIdPara = this.DbFacade.CreateParameter(); areaIdPara.ParameterName = "areaId"; areaIdPara.DbType = DbType.String; areaIdPara.Value = areaId; parameters.Add(areaIdPara); //modify by yanbing 2007-07-11 if (!String.IsNullOrEmpty(input.OrderState)) { DbParameter orderStatePara = this.DbFacade.CreateParameter(); orderStatePara.ParameterName = "order_state"; orderStatePara.DbType = DbType.String; orderStatePara.Value = input.OrderState; parameters.Add(orderStatePara); } //查询条件:创建人 if (!String.IsNullOrEmpty(input.Creater)) { sql = sql + " and UCase(o.create_username) like :creater"; DbParameter createrPara = this.DbFacade.CreateParameter(); createrPara.ParameterName = "creater"; createrPara.DbType = DbType.String; createrPara.Value = ComUtil.GetLike(input.Creater.ToUpper()); parameters.Add(createrPara); } //查询条件:1卖方企业或2订单号 if ("1".Equals(input.SearchField)) { if (!String.IsNullOrEmpty(input.SearchKey)) { sql = sql + " and UCase(o.saler_name & o.saler_easy & o.saler_wubi & o.saler_fast) like :org "; DbParameter orgPara = this.DbFacade.CreateParameter(); orgPara.ParameterName = "org"; orgPara.DbType = DbType.String; orgPara.Value = ComUtil.GetLike(input.SearchKey.ToUpper()); parameters.Add(orgPara); } } if ("2".Equals(input.SearchField)) { if (!String.IsNullOrEmpty(input.SearchKey)) { sql = sql + " and UCase(o.order_code) like :code "; DbParameter codePara = this.DbFacade.CreateParameter(); codePara.ParameterName = "code"; codePara.DbType = DbType.String; codePara.Value = ComUtil.GetLike(input.SearchKey.ToUpper()); parameters.Add(codePara); } } //end modify int count = 3; if (!((productName == null) || (productName == ""))) { string appender = @" And (h.medical_name Like :productName Or h.medical_id Like :productName Or h.medical_wubi Like :productName Or h.medical_pinyin Like :productName Or h.medical_code Like :productName Or h.trade_name Like :productName Or h.spell_abbr Like :productName Or h.name_wb Like :productName) "; sql += appender; count = 4; DbParameter productNamePara = this.DbFacade.CreateParameter(); productNamePara.ParameterName = "productName"; productNamePara.DbType = DbType.String; productNamePara.Value = ComUtil.GetLike(productName); parameters.Add(productNamePara); } if ((salerId == null)) { throw new System.ArgumentNullException("salerId"); } if ((buyerId == null)) { throw new System.ArgumentNullException("buyerId"); } if ((areaId == null)) { parameters[2].Value = System.DBNull.Value; } rows = base.GetRowCount(sql, parameters.ToArray()); //string pagedSql = base.GetPagedSql(sql, pageNum, pageSize); DataTable table = base.DbFacade.SQLExecuteDataTable(sql.ToString(), parameters.ToArray()); table.TableName = "OrderDetail"; DataSet ds = new DataSet(); ds.Tables.Add(table); return(ds); }
/// <summary> /// 订单详细列表,如果按企业到货 /// </summary> /// <param name="salerId"></param> /// <param name="buyerId"></param> /// <param name="areaId"></param> /// <param name="productName"></param> /// <returns></returns> public DataSet GetOrderDetailList(string salerId, string buyerId, string areaId, string productName) { string sql = @"select h.trade_name, h.medical_name, 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, Switch(i.unit_price Is Null,'-',True,Trim(Format(i.unit_price, 'Standard'))) As unit_price, i.record_id, CLng(iif(i.request_qty is null,0,i.request_qty)) as request_qty, i.saler_desc, Switch(i.item_status='1','发送',i.item_status='2', '已阅读', i.item_status='3', '已送货', i.item_status='5', '作废', i.item_status='6', '缺货', i.item_status='7', '完成') as item_status, Switch(i.order_type='0','蓝票', i.order_type='1','到货', i.order_type='2','红票') As order_type, h.doseage_form, Switch(h.province_max_price Is Null,'-',True,Trim(Format(h.province_max_price, 'Standard'))) As province_max_price, 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, h.dealer_fullname, l.name, h.last_order_date, CLng(iif(h.last_order_qty is null,0,h.last_order_qty)) as last_order_qty, Switch(i.con_type='1', '招标', i.con_type='2', '竞价', i.con_type='3', '询价', i.con_type='4', '备案', i.con_type='7', '浏览', i.con_type='9', '临时', i.con_type='c', 'GPO直销', i.con_type='d', 'GPO自主合同') as con_type, h.medical_id, h.medical_wubi, h.medical_pinyin, h.medical_code, h.spell_abbr, h.name_wb from gpo_order_item i, gpo_hit_comm h, gpo_order o, cont_list l where i.hit_comm_id = h.record_id and h.contract_id = l.id and o.order_id = i.order_id and o.saler_id = :salerId and i.buyer_orgid = :buyerId and o.area_id = :areaId "; List <DbParameter> parameters = new List <DbParameter>(); DbParameter salerIdPara = this.DbFacade.CreateParameter(); salerIdPara.ParameterName = "salerId"; salerIdPara.DbType = DbType.String; salerIdPara.Value = salerId; parameters.Add(salerIdPara); DbParameter buyerIdPara = this.DbFacade.CreateParameter(); buyerIdPara.ParameterName = "buyerId"; buyerIdPara.DbType = DbType.String; buyerIdPara.Value = buyerId; parameters.Add(buyerIdPara); DbParameter areaIdPara = this.DbFacade.CreateParameter(); areaIdPara.ParameterName = "areaId"; areaIdPara.DbType = DbType.String; areaIdPara.Value = areaId; parameters.Add(areaIdPara); int count = 3; if (!((productName == null) || (productName == ""))) { string appender = @" And (h.medical_name Like :productName Or h.medical_id Like :productName Or h.medical_wubi Like :productName Or h.medical_pinyin Like :productName Or h.medical_code Like :productName Or h.trade_name Like :productName Or h.spell_abbr Like :productName Or h.name_wb Like :productName) "; sql += appender; DbParameter productNamePara = this.DbFacade.CreateParameter(); productNamePara.ParameterName = "productName"; productNamePara.DbType = DbType.String; productNamePara.Value = ComUtil.GetLike(productName); parameters.Add(productNamePara); } if ((salerId == null)) { throw new System.ArgumentNullException("salerId"); } if ((buyerId == null)) { throw new System.ArgumentNullException("buyerId"); } DataTable table = base.DbFacade.SQLExecuteDataTable(sql.ToString(), parameters.ToArray()); table.TableName = "OrderDetail"; DataSet ds = new DataSet(); ds.Tables.Add(table); return(ds); }
//按订单,获得分页的订单明细信息 public DataSet GetOrderDetailListByOrder(string orderId, string productName, out int rows) { //int pageNum = Int32.Parse(pageParam.PageNum); //int pageSize = Int32.Parse(pageParam.PageSize); // string sql = @"select h.trade_name, // h.medical_name, // 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, // Switch(i.unit_price Is Null,'-',True,Trim(Format(i.unit_price, 'Standard'))) As unit_price, // i.record_id, // CLng(iif(i.request_qty is null,0,i.request_qty)) as request_qty, // i.saler_desc, // i.buyer_desc, // Switch(i.item_status='1','发送',i.item_status='2', // '已阅读', // i.item_status='3', // '已送货', // i.item_status='5', // '作废', // i.item_status='6', // '缺货', // i.item_status='7', // '完成') as item_status, // Switch(i.order_type='0','蓝票', // i.order_type='1','到货', // i.order_type='2','红票') As order_type, // h.doseage_form, // Switch(h.province_max_price Is Null,'-',True,Trim(Format(h.province_max_price, 'Standard'))) As province_max_price, // 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, // h.dealer_fullname, // format(h.last_order_date,'yyyy-mm-dd') as last_order_date, // CLng(iif(h.last_order_qty is null,0,h.last_order_qty)) as last_order_qty, // l.name, // Switch(i.con_type='1', // '招标', // i.con_type='2', // '竞价', // i.con_type='3', // '询价', // i.con_type='4', // '备案', // i.con_type='7', // '浏览', // i.con_type='9', // '临时', // i.con_type='c', // 'GPO直销', // i.con_type='d', // 'GPO自主合同') as con_type, // h.medical_id, // h.medical_wubi, // h.medical_pinyin, // h.medical_code, // h.spell_abbr, // // h.name_wb, // CLng(iif(REC.RECEIVE_QTY is null,0,REC.RECEIVE_QTY)) as receive_qty // from ((GPO_ORDER_ITEM AS i INNER JOIN GPO_HIT_COMM AS h ON i.HIT_COMM_ID = h.RECORD_ID) INNER JOIN CONT_LIST AS l ON h.CONTRACT_ID = l.ID) LEFT JOIN GPO_ORDER_RECEIVE AS REC ON i.RECORD_ID = REC.ORDER_ITEM_ID Where // i.order_id = :orderId"; string sql = @" select i.Id, i.Order_Id, (case when i.trade_Price Is Null then '-' else i.trade_Price end) As trade_Price, (case when i.RETAIL_PRICE Is Null then '-' else i.RETAIL_PRICE end) As RETAIL_PRICE,i.SUM AS total, i.AMOUNT As Request_Qty, i.SALER_DESCRIPTIONS as Saler_Desc, i.BUYER_DESCRIPTIONS as Buyer_Desc, i.OVER_AMOUNT as OVER_AMOUNT, i.OVER_SUM as OVER_SUM, (case i.STATE when '1' then '发送' when '2' then '已阅读' when '3' then '作废' when '4' then '已确认' when '5' then '完成' end) As status, (case i.order_type when '0' then '蓝票' when '1' then '到货' when '2' then '红票' end) As order_type, h.MANU_ID, h.MANU_NAME, h.MANU_NAME_ABBR, h.PRODUCT_NAME, h.COMMERCE_NAME, h.COMMON_NAME, h.CODE, h.ABBR_PY, h.ABBR_WB, h.BRAND, i.SPEC, i.MODEL, (case when i.OVER_AMOUNT is null then 0 else i.OVER_AMOUNT end) as receive_qty from (HC_ORD_ORDER_ITEM AS i INNER JOIN HC_ORD_PRODUCT AS h ON i.PROJECT_PROD_ID = h.ID) LEFT JOIN HC_ORD_ORDER_RECEIVE AS REC ON i.ID = REC.ORDER_ITEM_ID where i.order_id = @orderId and i.state <> '3'"; List <DbParameter> parameters = new List <DbParameter>(); DbParameter orderIdPara = this.DbFacade.CreateParameter(); orderIdPara.ParameterName = "orderId"; orderIdPara.DbType = DbType.Int64; orderIdPara.Value = orderId; parameters.Add(orderIdPara); if (!(string.IsNullOrEmpty(productName))) { string appender = @" And (h.PRODUCT_NAME Like @productName Or h.COMMERCE_NAME Like @productName Or h.CODE Like @productName Or h.ABBR_WB Like @productName Or h.ABBR_PY Like @productName Or h.COMMON_NAME Like @productName) "; sql += appender; DbParameter productNamePara = this.DbFacade.CreateParameter(); productNamePara.ParameterName = "productName"; productNamePara.DbType = DbType.String; productNamePara.Value = ComUtil.GetLike(productName); parameters.Add(productNamePara); } if ((orderId == null)) { throw new System.ArgumentNullException("orderId"); } rows = 0; //string pagedSql = base.GetPagedSql(sql, pageNum, pageSize); DataTable table = base.DbFacade.SQLExecuteDataTable(sql.ToString(), parameters.ToArray()); table.TableName = "OrderDetailByOrder"; DataSet ds = new DataSet(); ds.Tables.Add(table); return(ds); }