/// <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);
}
