/// <summary>
/// 根据项目类型获取项目信息
/// </summary>
/// <param name="ProjectType"></param>
/// <returns></returns>
public DataTable GetProjectInfoByProjectType(string ProjectType)
{
List <DbParameter> parameters = new List <DbParameter>();
DataTable dt = new DataTable();
StringBuilder strSql = new StringBuilder();
strSql.Append(@"Select cast(ID As int) As ID,PROJECT_NAME From HC_ORD_PROJECT Where 1=1");
if (!string.IsNullOrEmpty(ProjectType))
{
strSql.Append(" And PROJECT_TYPE = @ProjectType");
DbParameter strProjectType = DbFacade.CreateParameter();
strProjectType.ParameterName = "ProjectType";
strProjectType.DbType = DbType.String;
strProjectType.Value = ProjectType;
parameters.Add(strProjectType);
}
strSql.Append(" Order By ID DESC");
try
{
dt = base.DbFacade.SQLExecuteDataTable(strSql.ToString(), parameters.ToArray());
}
catch (Exception ex)
{
throw ex;
}
return(dt);
}
/// <summary>
/// 获取发货单信息对象
/// </summary>
/// <param name="strOrdInvoiceFromId"></param>
/// <returns></returns>
public OrdInvoiceFromModel GetOrdInvoiceFromModel(string strOrdInvoiceFromId)
{
List <DbParameter> parameters = new List <DbParameter>();
DataTable dt = null;
StringBuilder strSql = new StringBuilder();
strSql.Append(@"Select
oif.Id,
oif.INVOICE_CODE,
org.ORG_NAME As SENDED_NAME,
org.ORG_ABBR As SENDED_ABBR,
oif.CREATE_USER_NAME,
oif.CREATE_DATE,
oif.TOTAL_SUM,
oif.OVER_SUM,
oif.SENDED_DATE,
oif.STATE,
oif.CREATE_USER_NAME,
oif.CREATE_DATE,
oif.MODIFY_USER_NAME,
oif.MODIFY_DATE,
oif.BUYER_DESCRIPTIONS,
oif.SALER_DESCRIPTIONS,
(Case oif.State When '1' Then '未发送' When '2' Then '已发送' When '3' Then '买方处理中' When '4' Then '作废' When '5' Then '买方处理完成' End) As StateName
From HC_ORD_INVOICE_FROM oif,HC_ORG org
Where oif.SENDER_ID=org.Id");
if (!string.IsNullOrEmpty(strOrdInvoiceFromId))
{
strSql.Append(" And oif.Id=@INVOICE_FROM_ID");
DbParameter paInvoiceFromId = DbFacade.CreateParameter();
paInvoiceFromId.ParameterName = "INVOICE_FROM_ID";
paInvoiceFromId.DbType = DbType.String;
paInvoiceFromId.Value = strOrdInvoiceFromId;
parameters.Add(paInvoiceFromId);
}
else
{
return(null);
}
OrdInvoiceFromModel model = null;
try
{
model = base.DbFacade.SQLExecuteObject(strSql.ToString(), new MapRow(GetOrdInvoiceFromModel), parameters.ToArray()) as OrdInvoiceFromModel;
}
catch (Exception ex)
{
throw ex;
}
return(model);
}
/// <summary>
/// 获取指定序列的ID
/// </summary>
/// <param name="type">序列前缀</param>
/// <param name="seq">序列名</param>
/// <returns></returns>
protected String GetNewId(String type, string seq)
{
string sql = "SELECT :TYPE||LPAD(" + seq + ".NEXTVAL, 20, '0') AS ID FROM DUAL ";
DbParameter paramType = DbFacade.CreateParameter();
paramType.ParameterName = "TYPE";
paramType.DbType = DbType.String;
paramType.Value = type;
return(DbFacade.SQLExecuteScalar(sql, paramType).ToString());
}
/// <summary>
/// 根据备货单明细ID 获取备货单明细记录对象
/// </summary>
/// <param name="stock_Item_Id"></param>
public OrdStockUpItemModel GetOrdStockUpItemModel(string stock_Item_Id)
{
List <DbParameter> parameters = new List <DbParameter>();
StringBuilder strSql = new StringBuilder();
strSql.Append(@"Select
osi.ID,
osi.COMMERCE_NAME,
osi.PRODUCT_NAME,
op.COMMON_NAME,
osi.BRAND,
osi.MANUFACTURE_ID,
osi.MANUFACTURE_NAME,
osi.MANUFACTURE_NAME_ABBR,
osi.SPEC,
osi.MODEL,
osi.BARCODE,
osi.BATCH_NO,
osi.NUM,
osi.VALID_DATE,
osi.REMARK,
op.price,
op.REG_NO,
op.REG_VALID_DATE,
op.SALER_ID,
op.SALER_NAME,
op.SALER_NAME_ABBR,
op.BASE_MEASURE,
op.PERFORMANCE,
(case osi.STATE when '1' then '未确认' when '2' then '已确认' when '3' then '完成' when '4' then '作废' end) As StateName
From HC_ORD_ORD_STOCK_ITEM As osi,HC_ORD_PRODUCT As op
Where osi.DATA_PRODUCT_ID=op.DATA_PRODUCT_ID and osi.PROJECT_ID=op.PROJECT_ID");
if (!string.IsNullOrEmpty(stock_Item_Id))
{
strSql.Append(" And osi.ID=:stock_Item_Id");
DbParameter strStock_Item_Id = DbFacade.CreateParameter();
strStock_Item_Id.ParameterName = "stock_Item_Id";
strStock_Item_Id.DbType = DbType.String;
strStock_Item_Id.Value = stock_Item_Id;
parameters.Add(strStock_Item_Id);
}
else
{
return(null);
}
OrdStockUpItemModel model = null;
model = base.DbFacade.SQLExecuteObject(strSql.ToString(), new MapRow(StockUpItemModel), parameters.ToArray()) as OrdStockUpItemModel;
return(model);
}
/// <summary>
/// 获取自义定编码大包装信息列表
/// </summary>
/// <returns></returns>
public DataTable GetDefineCodeDt(string ProjectID)
{
List <DbParameter> parameters = new List <DbParameter>();
DataTable dt = null;
StringBuilder strSql = new StringBuilder();
strSql.Append(@"Select
ohc.ID As HIT_COMM_ID,
sdi.PRODUCT_MNEMONIC,
sdi.SELF_PACKAGE,
sdi.ALIAS,
sdi.ALIAS_PINYIN,
ohc.PROJECT_ID,
ohc.PRODUCT_NAME,
ohc.COMMERCE_NAME,
ohc.COMMON_NAME,
ohc.SPEC,
ohc.MODEL,
ohc.BRAND,
ohc.SENDER_ID,
org1.ORG_ABBR As SENDER_NAME_ABBR,
org1.ORG_NAME As SENDER_NAME,
org2.ORG_ABBR As MANU_NAME_ABBR,
org2.ORG_NAME As MANU_NAME,
ohc.PRICE
From (Select * From HC_ORD_HIT_COMM ohc Where ohc.PROJECT_ID=@PROJECT_ID) As ohc
Left join HC_ORG org1 On org1.ID=ohc.SENDER_ID
Left join HC_ORG org2 On org2.ID=ohc.MANU_ID
Left join hc_self_define_info sdi
On ohc.ID=sdi.HIT_COMM_ID");
if (!string.IsNullOrEmpty(ProjectID))
{
DbParameter strProjectID = DbFacade.CreateParameter();
strProjectID.ParameterName = "PROJECT_ID";
strProjectID.DbType = DbType.String;
strProjectID.Value = ProjectID;
parameters.Add(strProjectID);
}
try
{
dt = base.DbFacade.SQLExecuteDataTable(strSql.ToString(), parameters.ToArray());
}
catch (Exception ex)
{
throw ex;
}
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="logedinUser"></param>
/// <returns></returns>
public DataTable GetStockUpList(LogedInUser logedinUser)
{
List <DbParameter> parameters = new List <DbParameter>();
DataTable dt = null;
StringBuilder strSql = new StringBuilder();
strSql.Append(@"Select
hoos.ID,
hoos.CODE,
hoos.BUYER_ID,
org1.ID As SENDER_ID,
org1.ORG_NAME As SENDER_NAME,
org1.ORG_ABBR As SENDER_NAME_ABBR,
org1.SPELL_ABBR As SENDER_NAME_SPELL_ABBR,
org1.ORG_NAME_WB As SENDER_NAME_WB,
hoos.CREATE_USER_NAME,
hoos.CREATE_DATE,
hoos.STATE,
(case hoos.STATE when '1' then '未发出' when '2' then '已发出' when '3' then '买方已确认' when '4' then '作废' when '5' then '确认中' when '6' then '完成' end) As StateName
From HC_ORD_ORD_STOCK hoos,Hc_org org1
Where org1.ID=hoos.SENDER_ID And hoos.STATE<>'1'");
if (!string.IsNullOrEmpty(logedinUser.UserOrg.Id))
{
strSql.Append(" And hoos.BUYER_ID=@Buyer_ID");
DbParameter strBuyerId = DbFacade.CreateParameter();
strBuyerId.ParameterName = "Buyer_ID";
strBuyerId.DbType = DbType.String;
strBuyerId.Value = logedinUser.UserOrg.Id;
parameters.Add(strBuyerId);
}
try
{
dt = base.DbFacade.SQLExecuteDataTable(strSql.ToString(), parameters.ToArray());
}
catch (Exception ex)
{
throw ex;
}
return(dt);
}
/// <summary>
/// 根据项目ID获取品种分类信息
/// </summary>
/// <param name="ProjectID"></param>
/// <returns></returns>
public DataTable GetProductClassInfoByProjectID(string ProjectID)
{
List <DbParameter> parameters = new List <DbParameter>();
DataTable dt = new DataTable();
StringBuilder strSql = new StringBuilder();
strSql.Append("Select ");
strSql.Append(" cast(ID As int) As ID,CLASS_NAME,PROJECT_ID ");
strSql.Append(" From HC_PROJECT_PRODUCT_CLASS");
strSql.Append(" Where 1=1");
if (!string.IsNullOrEmpty(ProjectID))
{
strSql.Append(" And PROJECT_ID = @PROJECT_ID");
DbParameter strProjectId = DbFacade.CreateParameter();
strProjectId.ParameterName = "PROJECT_ID";
strProjectId.DbType = DbType.String;
strProjectId.Value = ProjectID;
parameters.Add(strProjectId);
}
try
{
dt = base.DbFacade.SQLExecuteDataTable(strSql.ToString(), parameters.ToArray());
DataRow dr = dt.NewRow();
dr["ID"] = "0";
dr["CLASS_NAME"] = "全部";
dt.Rows.InsertAt(dr, 0);
dt.AcceptChanges();
}
catch (Exception ex)
{
throw ex;
}
return(dt);
}
public List <string> findSubPlatId(string platFatherId)
{
string sql = "select id from plt_plat where plat_father = :ID";
DbParameter idPara = DbFacade.CreateParameter();
idPara.ParameterName = "ID";
idPara.DbType = DbType.String;
idPara.Value = platFatherId;
List <string> result = null;
try
{
result = (List <string>)DbFacade.SQLExecuteList <string>(sql, new MapRow <string>(MapPlatId), idPara);
}
catch (Exception e)
{
throw e;
}
return(result);
}
public List <string> findPlatsByUserId(string userId)
{
string sql = "select plat_id as id from usr_usr_plat where user_id = :ID";
DbParameter idPara = DbFacade.CreateParameter();
idPara.ParameterName = "ID";
idPara.DbType = DbType.String;
idPara.Value = userId;
List <string> result = null;
try
{
result = (List <string>)DbFacade.SQLExecuteList <string>(sql, new MapRow <string>(MapPlatId), idPara);
}
catch (Exception e)
{
throw e;
}
return(result);
}
/// <summary>
/// 根据备货单ID 获取备货单记录对象
/// </summary>
/// <param name="stock_Id"></param>
public OrdStockUpModel GetOrdStockUpModel(string stock_Id)
{
List <DbParameter> parameters = new List <DbParameter>();
StringBuilder strSql = new StringBuilder();
strSql.Append(@"Select
hoos.ID,
hoos.CODE,
hoos.BUYER_ID,
hoos.SENDER_ID,
hoos.SENDER_NAME,
hoos.SENDER_NAME_ABBR,
hoos.CREATE_USER_NAME,
hoos.CREATE_DATE,
(case hoos.STATE when '1' then '未发送' when '2' then '已发送' when '3' then '买方已确认' when '4' then '作废' when '5' then '确认中' when '6' then '完成' end) As StateName
From HC_ORD_ORD_STOCK As hoos
Where 1=1");
if (!string.IsNullOrEmpty(stock_Id))
{
strSql.Append(" And hoos.ID=@stock_Id");
DbParameter strStock_Id = DbFacade.CreateParameter();
strStock_Id.ParameterName = "stock_Id";
strStock_Id.DbType = DbType.String;
strStock_Id.Value = stock_Id;
parameters.Add(strStock_Id);
}
else
{
return(null);
}
OrdStockUpModel model = null;
model = base.DbFacade.SQLExecuteObject(strSql.ToString(), new MapRow(StockUpModel), parameters.ToArray()) as OrdStockUpModel;
return(model);
}
/// <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="logedinUser"></param>
/// <param name="ProjectID"></param>
/// <returns></returns>
public DataTable GetStockList(LogedInUser logedinUser, string ProjectID, string strDataName)
{
List <DbParameter> parameters = new List <DbParameter>();
DataTable dt = null;
StringBuilder strSql = new StringBuilder();
strSql.Append(@"Select Ohc.*,
Sdi.PRODUCT_MNEMONIC,
Sdi.SELF_PACKAGE,
Sdi.ALIAS,
Sdi.ALIAS_PINYIN
From
(
Select
ohc.ID,
ohc.MANU_ID,
ohc.SALER_ID,
ohc.SENDER_ID,
pro.ID As PROJECT_ID,
pro.PROJECT_TYPE,
(case pro.PROJECT_TYPE when '1' then '招投标' when '2' then '备案采购' when '3' then '竞价采购' when '4' then '浏览采购' end) As PROJECT_TYPE_Name,
ppcc.CLASS_ID,
op.ID As PROJECT_PRODUCT_ID,
op.DATA_PRODUCT_ID,
op.COMMERCE_NAME,
op.PRODUCT_NAME,
op.COMMON_NAME,
op.ABBR_PY,
op.ABBR_WB,
ohc.SPEC,
ohc.MODEL,
ohc.BRAND,
ohc.REG_NO,
ohc.REG_VALID_DATE,
org1.ORG_NAME As MANU_NAME,
org1.ORG_ABBR As MANU_NAME_ABBR,
org1.SPELL_ABBR As MANU_NAME_SPELL_ABBR,
org1.ORG_NAME_WB As MANU_NAME_WB,
org2.ORG_NAME As SALER_NAME,
org2.ORG_ABBR As SALER_NAME_ABBR,
org2.SPELL_ABBR As SALER_NAME_SPELL_ABBR,
org2.ORG_NAME_WB As SALER_NAME_WB,
org3.ORG_NAME As SENDER_NAME,
org3.ORG_ABBR As SENDER_NAME_ABBR,
org3.SPELL_ABBR As SENDER_NAME_SPELL_ABBR,
org3.ORG_NAME_WB As SENDER_NAME_WB,
ohc.DEFAULT_MEASURE,
ohc.PRICE,
op.STATE,
(case op.STATE when '0' then '不可用' when '1' then '可用' end) As StateName,
hbs.STORE_NAME As STORE_ROOM_NAME
From HC_ORD_HIT_COMM ohc,HC_ORD_PROJECT pro,HC_PROJECT_PROD_CLASS_CONTENT ppcc,HC_ORD_PRODUCT op,HC_ORG org1,HC_ORG org2,HC_ORG org3
,HC_BUYER_STORE hbs
Where ohc.PROJECT_PROD_ID=op.Id And pro.Id = op.PROJECT_ID And ppcc.PROJECT_ID=op.PROJECT_ID And ppcc.PRODUCT_ID=op.ID
And ohc.MANU_ID=org1.ID And ohc.SALER_ID=org2.ID And ohc.SENDER_ID =org3.Id
And op.STATE='1'
And hbs.ID=ohc.STORE_ROOM_ID And op.PROJECT_ID=@PROJECT_ID
) As ohc
Left Join HC_SELF_DEFINE_INFO Sdi on Ohc.ID=sdi.HIT_COMM_ID
");
DbParameter strProjectID = DbFacade.CreateParameter();
strProjectID.ParameterName = "PROJECT_ID";
strProjectID.DbType = DbType.String;
strProjectID.Value = ProjectID;
parameters.Add(strProjectID);
try
{
dt = base.DbFacade.SQLExecuteDataTable(strSql.ToString(), strDataName, parameters.ToArray());
}
catch (Exception ex)
{
throw ex;
}
return(dt);
}
/// <summary>
/// 获取采购目录对象
/// </summary>
/// <param name="HitCommID"></param>
/// <returns></returns>
public OrdHitCommMode GetOrdHitCommModel(string HitCommID)
{
List <DbParameter> parameters = new List <DbParameter>();
StringBuilder strSql = new StringBuilder();
strSql.Append(@"Select Ohc.*,
Sdi.PRODUCT_MNEMONIC,
Sdi.SELF_PACKAGE,Sdi.ALIAS,Sdi.ALIAS_PINYIN From
(Select
h.PROJECT_ID,
h.ID,
p.PROJECT_TYPE,
(case p.PROJECT_TYPE when '1' then '招投标' when '2' then '备案采购' when '3' then '竞价采购' when '4' then '浏览采购' end) As PROJECT_TYPE_Name,
h.PROJECT_PROD_ID,
h.COMMERCE_NAME,
h.COMMON_NAME,
h.PRODUCT_NAME,
h.SPEC,
h.MODEL,
h.BRAND,
h.DEFAULT_MEASURE,
h.DEFAULT_MEASURE_EX,
h.PRICE,
h.MANU_NAME,
h.SALER_NAME,
org1.ID As SENDER_ID,
org1.ORG_NAME As SENDER_NAME,
h.STORE_ROOM_ID,
h.STORE_ROOM_NAME,
h.REG_NO,
h.REG_VALID_DATE,
ppc.CLASS_NAME
From HC_ORD_HIT_COMM h,HC_ORD_PROJECT p,HC_ORG org1,
HC_PROJECT_PROD_CLASS_CONTENT ppcc,HC_PROJECT_PRODUCT_CLASS ppc
Where h.PROJECT_ID= p.ID And org1.ID=h.SENDER_ID And h.ID=@HitCommID And ppcc.CLASS_ID=ppc.ID
And h.PROJECT_PROD_ID=ppcc.PRODUCT_ID
) As Ohc
Left Join hc_self_define_info sdi on Ohc.ID=sdi.HIT_COMM_ID");
if (!string.IsNullOrEmpty(HitCommID))
{
DbParameter strHitCommID = DbFacade.CreateParameter();
strHitCommID.ParameterName = "HitCommID";
strHitCommID.DbType = DbType.String;
strHitCommID.Value = HitCommID;
parameters.Add(strHitCommID);
}
else
{
return(null);
}
OrdHitCommMode model = null;
try
{
model = base.DbFacade.SQLExecuteObject(strSql.ToString(), new MapRow(HitCommModel), parameters.ToArray()) as OrdHitCommMode;
}
catch (Exception ex)
{
throw ex;
}
return(model);
}
/// <summary>
/// 获取库存商品列表信息
/// </summary>
/// <returns></returns>
public DataTable GetOrdSecondAyplnvList(LogedInUser logedinUser)
{
List <DbParameter> parameters = new List <DbParameter>();
DataTable dt = null;
StringBuilder strSql = new StringBuilder();
strSql.Append(@"Select Tab.*,ohc.STORE_ROOM_ID,
ohc.STORE_ROOM_NAME,ohc.ID As ordHitCommId From
(
Select
'0' As Sel,
osa.ID,
op.id As PROJECT_PROD_ID,
op.DATA_PRODUCT_ID,
op.CONT_PRODUCT_ID,
op.PROJECT_ID,
op.COMMERCE_NAME,
op.PRODUCT_NAME,
op.COMMON_NAME,
op.CODE,
op.GOODS_NO,
op.ABBR_PY,
op.ABBR_WB,
op.BASE_MEASURE,
op.BASE_MEASURE_SPEC,
op.BASE_MEASURE_MATER,
op.MAX_PRICE,
osi.SPEC_ID,
osi.MODEL_ID,
osi.SPEC,
osi.MODEL,
isnull(osi.BRAND,'-') As BRAND,
osa.CREATE_DATE,
org1.ID As SENDER_ID,
org1.ORG_NAME As SENDER_NAME,
org1.ORG_ABBR As SENDER_NAME_ABBR,
org1.SPELL_ABBR As SENDER_NAME_SPELL_ABBR,
org1.ORG_NAME_WB As SENDER_NAME_WB,
org2.ID As SALER_ID,
org2.ORG_NAME As SALER_NAME,
org2.ORG_ABBR As SALER_NAME_ABBR,
org2.SPELL_ABBR As SALER_NAME_SPELL_ABBR,
org2.ORG_NAME_WB As SALER_NAME_WB,
org3.ID As MANU_ID,
org3.ORG_NAME As MANU_NAME,
org3.ORG_ABBR As MANU_NAME_ABBR,
org3.SPELL_ABBR As MANU_NAME_SPELL_ABBR,
org3.ORG_NAME_WB As MANU_NAME_WB,
op.PRICE,
'' As FACT_AMOUNT,
op.DEFAULT_MEASURE,
op.DEFAULT_MEASURE_EX,
op.INSTRU_CODE,
op.INSTRU_NAME,
op.REG_NO,
op.REG_VALID_DATE,
isnull(osa.BATCH_NO,'-') As BATCH_NO,
isNull(convert(varchar,osa.VALID_DATE,23),'-') As VALID_DATE,
isnull(osa.PBNO,'-') As PBNO,
osa.SEND_BATCH_NO,
osa.INSTORE_BATCH_NO,
isnull(osi.BARCODE,'-') As BARCODE,
osa.NUM,
ost.BUYER_ID
From HC_ORD_SECOND_AYRLNV osa,HC_ORD_ORD_STOCK_ITEM osi,HC_ORD_ORD_STOCK ost,HC_ORD_PRODUCT op,HC_ORG org1,HC_ORG org2,HC_ORG org3
where osa.STOCK_ITEM_ID=osi.Id and op.id=osa.PROJECT_PRODUCT_ID And osa.state <> '0'
And osa.SENDER_ID=org1.ID And osa.SALER_ID=org2.ID And op.MANU_ID=org3.ID And ost.id=osi.STOCK_ID And ost.BUYER_ID=@Buyer_ID
) As Tab Left Join HC_ORD_HIT_COMM ohc
on Tab.PROJECT_ID=ohc.PROJECT_ID and Tab.PROJECT_PROD_ID=ohc.PROJECT_PROD_ID and Tab.SPEC_ID=ohc.SPEC_ID and Tab.MODEL_ID=ohc.MODEL_ID
");
strSql.Append(" Order By Tab.CREATE_DATE Desc");
if (!string.IsNullOrEmpty(logedinUser.UserOrg.Id))
{
DbParameter strBuyerId = DbFacade.CreateParameter();
strBuyerId.ParameterName = "Buyer_ID";
strBuyerId.DbType = DbType.String;
strBuyerId.Value = logedinUser.UserOrg.Id;
parameters.Add(strBuyerId);
}
try
{
dt = base.DbFacade.SQLExecuteDataTable(strSql.ToString(), parameters.ToArray());
}
catch (Exception ex)
{
throw ex;
}
return(dt);
}
/// <summary>
/// 获取备货单明细信息
/// </summary>
/// <returns></returns>
public DataTable GetStockUpItemList(string stock_Id)
{
List <DbParameter> parameters = new List <DbParameter>();
DataTable dt = null;
StringBuilder strSql = new StringBuilder();
strSql.Append(@"Select
'0' As Sel,
osi.ID,
oos.ID As Stock_Id,
osi.PROJECT_ID,
osi.PROJECT_PROD_ID,
op.SALER_ID,
op.SALER_NAME,
osi.MANUFACTURE_ID As MANU_ID,
osi.MANUFACTURE_NAME As MANU_NAME,
osi.MANUFACTURE_NAME_ABBR As MANU_NAME_ABBR,
oos.SENDER_ID,
oos.SENDER_NAME,
oos.SENDER_NAME_ABBR,
osi.SPEC,
osi.MODEL,
osi.BARCODE,
'' As barcode_Back,
isNull(osi.BATCH_NO,'-') As BATCH_NO,
osi.NUM,
isNull(convert(varchar,osi.VALID_DATE,23),'-') As VALID_DATE,
osi.REMARK,
op.price,
op.DATA_PRODUCT_ID,
op.CONT_PRODUCT_ID,
op.BASE_MEASURE,
op.COMMON_NAME,
op.PRODUCT_NAME,
op.ABBR_PY,
op.ABBR_WB,
osi.BRAND,
op.CODE,
osi.GOODS_NO,
op.BASE_MEASURE_SPEC,
op.BASE_MEASURE_MATER,
op.MAX_PRICE,
osi.SPEC_ID,
osi.MODEL_ID,
osi.STATE,
isNull(osi.PBNO,'-') As PBNO,
osi.SEND_BATCH_NO,
osi.INSTORE_BATCH_NO,
osi.CREATE_DATE,
(case osi.STATE when '1' then '未确认' when '2' then '已确认' when '3' then '完成' when '4' then '作废' end) As StateName
From HC_ORD_ORD_STOCK_ITEM osi,HC_ORD_PRODUCT op,HC_ORD_ORD_STOCK oos
Where osi.PROJECT_PROD_ID=op.ID and osi.PROJECT_ID=op.PROJECT_ID
And osi.STOCK_ID = oos.ID And osi.DATA_PRODUCT_ID=op.DATA_PRODUCT_ID
");
if (!string.IsNullOrEmpty(stock_Id))
{
strSql.Append(" And Osi.STOCK_ID=@STOCK_ID");
DbParameter strStockId = DbFacade.CreateParameter();
strStockId.ParameterName = "STOCK_ID";
strStockId.DbType = DbType.String;
strStockId.Value = stock_Id;
parameters.Add(strStockId);
}
try
{
dt = base.DbFacade.SQLExecuteDataTable(strSql.ToString(), parameters.ToArray());
}
catch (Exception ex)
{
throw ex;
}
return(dt);
}
//已到货列表的检索
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="userId"></param>
/// <returns></returns>
public DataTable GetMenu(string userId, string clientType)
{
DataTable dt = new DataTable();
StringBuilder sql = new StringBuilder();
try
{
sql.Append("select *");
sql.Append(" from (SELECT distinct CLIENT_MENU.MODULE_ID,");
sql.Append(" CLIENT_MENU.ID,");
sql.Append(" CLIENT_MENU.SORT_SN,");
sql.Append(" CLIENT_MENU.NAME,");
sql.Append(" CLIENT_MENU.FATHER,");
sql.Append(" CLIENT_MENU.MODULE_ACTION,");
sql.Append(" CLIENT_MENU.WEB_FLAG,");
sql.Append(" CLIENT_MENU.URL,CLIENT_MENU.SHORTCUT,CLIENT_MENU.IMAGE AS IMAGE1,");
sql.Append(" CLIENT_MENU.MODIFY_DATE");
sql.Append(" FROM (Gpo_Usr_User INNER JOIN gpo_usr_user_module ON");
sql.Append(" Gpo_Usr_User.id = gpo_usr_user_module.user_id)");
sql.Append(" INNER JOIN CLIENT_MENU ON gpo_usr_user_module.MODULE_ID = CLIENT_MENU.MODULE_ID ");
sql.Append(" where Gpo_Usr_User.ID = :userId ");
sql.Append(" and CLIENT_MENU.enable_flag = '1' AND (instr(CLIENT_MENU.CLIENT_ID,'H') = 0 AND instr(CLIENT_MENU.CLIENT_ID,'W')= 0 ");
//判断是否福建项目
if ("1".Equals(clientType))
{
sql.Append(" and instr(CLIENT_MENU.client_id,'F') = 0");
}
sql.Append(" OR CLIENT_MENU.CLIENT_ID IS NULL)");
sql.Append(" union");
sql.Append(" SELECT distinct CLIENT_MENU.MODULE_ID,");
sql.Append(" CLIENT_MENU.ID,");
sql.Append(" CLIENT_MENU.SORT_SN,");
sql.Append(" CLIENT_MENU.NAME,");
sql.Append(" CLIENT_MENU.FATHER,");
sql.Append(" CLIENT_MENU.MODULE_ACTION,");
sql.Append(" CLIENT_MENU.WEB_FLAG,");
sql.Append(" CLIENT_MENU.URL,CLIENT_MENU.SHORTCUT,CLIENT_MENU.IMAGE AS IMAGE1,");
sql.Append(" CLIENT_MENU.MODIFY_DATE");
sql.Append(" from CLIENT_MENU");
sql.Append(" where (CLIENT_MENU.module_id is null or CLIENT_MENU.father = '0') ");
sql.Append(" and CLIENT_MENU.enable_flag = '1' AND (instr(CLIENT_MENU.CLIENT_ID,'H') = 0 AND instr(CLIENT_MENU.CLIENT_ID,'W')= 0");
//判断是否福建项目
if ("1".Equals(clientType))
{
sql.Append(" and instr(CLIENT_MENU.client_id,'F') = 0");
}
sql.Append(" OR CLIENT_MENU.CLIENT_ID IS NULL)");
sql.Append(" ) nt");
sql.Append(" ORDER BY nt.SORT_SN");
DbParameter idPara = DbFacade.CreateParameter();
idPara.ParameterName = "userId";
idPara.DbType = DbType.AnsiString;
idPara.Value = userId;
dt = base.DbFacade.SQLExecuteDataTable(sql.ToString(), idPara);
}
catch (Exception e)
{
throw e;
}
return(dt);
}
/// <summary>
/// 获取项目产品信息
/// </summary>
/// <returns></returns>
public DataTable GetHitProductDt(string strProjectID, LogedInUser logedinUser, string strDataName)
{
List <DbParameter> parameters = new List <DbParameter>();
DataTable OrdProDt = null;
StringBuilder strSql = new StringBuilder();
//项目产品目录+项目配套子表
strSql.Append(@"Select
isnull(Cast(Pack.PACKAGE_ID as varchar),'') + isnull('【' + Pack.name + '】',' 【非配套产品】') As PackName,
(Case when Pack.AMOUNT is Null Or Pack.AMOUNT <=0 Then '-' Else str(cast(Pack.AMOUNT as int)) End) As AMOUNT,
Pro.*,
Hsdi.PRODUCT_MNEMONIC,
(case when Hsdi.SELF_PACKAGE is null then 1 else Hsdi.SELF_PACKAGE end )as SELF_PACKAGE,
Hsdi.ALIAS,
Hsdi.ALIAS_PINYIN
From
(
(Select
op.id,
op.PROJECT_PROD_ID As PROJECT_PROD_ID,
op.PRODUCT_NAME,
op.PROJECT_ID,
pro.PROJECT_TYPE,
(case pro.PROJECT_TYPE when '1' then '招投标' when '2' then '备案采购' when '3' then '竞价采购' when '4' then '浏览采购' end) As PROJECT_TYPE_Name,
ppcc.CLASS_ID,
op.DATA_PRODUCT_ID,
op.COMMON_NAME,
op.CODE As PRODUCTCODE,
op.ABBR_PY,
op.ABBR_WB,
op.SPEC_ID,
op.SPEC,
op.MODEL_ID,
op.MODEL,
op.PERFORMANCE,
isnull(op.BRAND,'-') as BRAND,
op.BASE_MEASURE,
op.DEFAULT_MEASURE,
op.Default_Measure_Ex,
op.INSTRU_CODE,
op.INSTRU_NAME,
op.REG_NO,
op.REG_VALID_DATE,
op.PRICE,
org1.ORG_NAME As MANU_NAME,
org1.ORG_ABBR As MANU_NAME_ABBR,
org1.SPELL_ABBR As MANU_NAME_SPELL_ABBR,
org1.ORG_NAME_WB As MANU_NAME_WB,
org2.ORG_NAME As SALER_NAME,
org2.ORG_ABBR As SALER_NAME_ABBR,
org2.SPELL_ABBR As SALER_NAME_SPELL_ABBR,
org2.ORG_NAME_WB As SALER_NAME_WB,
org3.ORG_NAME As SENDER_NAME,
org3.ORG_ABBR As SENDER_NAME_ABBR,
org3.SPELL_ABBR As SENDER_NAME_SPELL_ABBR,
org3.ORG_NAME_WB As SENDER_NAME_WB,
op.GOODS_NO,
op.BARCODE,
op.BASE_MEASURE_SPEC,
op.BASE_MEASURE_MATER,
op.MAX_PRICE,
op.MANU_ID,
op.SALER_ID,
op.SENDER_ID,
op.LAST_DATE,
isnull(cast(hop.bid_id as varchar),'-') As bid_id
From
HC_ORD_HIT_COMM op,HC_ORD_PRODUCT hop,HC_ORD_PROJECT pro,HC_ORG org1,HC_ORG org2,HC_ORG org3,HC_PROJECT_PROD_CLASS_CONTENT ppcc
Where op.PROJECT_ID = pro.ID And
op.PROJECT_PROD_ID=hop.Id And pro.Id = hop.PROJECT_ID And ppcc.PROJECT_ID=hop.PROJECT_ID And ppcc.PRODUCT_ID=hop.ID And
op.MANU_ID=org1.ID And op.SALER_ID=org2.Id And op.SENDER_ID=org3.Id And ppcc.PROJECT_ID=op.PROJECT_ID And ppcc.PRODUCT_ID=op.PROJECT_PROD_ID
And op.STATE='1' And op.PROJECT_ID=@PROJECT_ID
) Pro Left Join
(
Select opp.Id,opp.name,oppi.PACKAGE_ID,oppi.PROJECT_PROD_ID,oppi.AMOUNT,opp.PROJECT_ID From HC_ORD_PRODUCT_PACKAGE opp,HC_ORD_PRODUCT_PACKAGE_ITEM oppi
Where oppi.PACKAGE_ID=opp.Id And opp.PROJECT_ID=@PROJECT_ID
) Pack On Pro.PROJECT_PROD_ID=Pack.PROJECT_PROD_ID and Pro.PROJECT_ID=Pack.PROJECT_ID)
left join HC_SELF_DEFINE_INFO Hsdi on pro.id= Hsdi.HIT_COMM_ID
");
DbParameter strProID = DbFacade.CreateParameter();
strProID.ParameterName = "PROJECT_ID";
strProID.DbType = DbType.String;
strProID.Value = strProjectID;
parameters.Add(strProID);
DbParameter strBuyerID = DbFacade.CreateParameter();
strBuyerID.ParameterName = "BUYER_ID";
strBuyerID.DbType = DbType.String;
strBuyerID.Value = logedinUser.UserOrg.Id;
parameters.Add(strBuyerID);
try
{
OrdProDt = base.DbFacade.SQLExecuteDataTable(strSql.ToString(), strDataName, parameters.ToArray());
}
catch (Exception ex)
{
throw ex;
}
return(OrdProDt);
}
/// <summary>
/// 获取发货单明细信息列表
/// </summary>
/// <returns></returns>
public DataTable GetOrdInvoiceFromItemList(string StrInvoiceFromId)
{
List <DbParameter> parameters = new List <DbParameter>();
DataTable dt = null;
StringBuilder strSql = new StringBuilder();
strSql.Append(@"Select Tab.*,ohc.STORE_ROOM_ID,
ohc.STORE_ROOM_NAME,ohc.ID As ordHitCommId From
(
Select
'0' As Sel,
oifi.Id,
oifi.DATA_PRODUCT_ID,
oifi.PROJECT_ID,
oifi.INVOICE_FROM_ID,
oifi.PROJECT_PROD_ID As PROJECT_PRODUCT_ID,
oifi.BUYER_ID,
op.CONT_PRODUCT_ID,
op.Code As PRODUCT_CODE,
op.COMMERCE_NAME,
op.PRODUCT_NAME,
op.COMMON_NAME,
op.ABBR_PY,
op.ABBR_WB,
op.MAX_PRICE,
op.DEFAULT_MEASURE,
op.DEFAULT_MEASURE_EX,
op.INSTRU_CODE,
op.INSTRU_NAME,
op.REG_NO,
op.REG_VALID_DATE,
oifi.GOODS_NO,
oifi.SPEC_ID,
oifi.MODEL_ID,
oifi.SEND_MEASURE,
oifi.SEND_MEASURE_EX,
isnull(oifi.PBNO,'-') As PBNO,
oifi.SEND_BATCH_NO,
oifi.INSTORE_BATCH_NO,
isnull(oifi.INSTORE_BATCH_NO,'-') As INSTORE_BATCH_NO_ITEM,
oifi.SPEC,
oifi.MODEL,
oifi.BRAND,
oifi.BARCODE,
oifi.SALER_ID,
org1.ORG_NAME As SALER_NAME,
org1.ORG_ABBR As SALER_NAME_ABBR,
oif.SENDER_ID,
org2.ORG_NAME As SENDER_NAME,
org2.ORG_ABBR As SENDER_NAME_ABBR,
oifi.MANUFACTURE_ID As MANU_ID,
org3.ORG_NAME As MANU_NAME,
org3.ORG_ABBR As MANU_NAME_ABBR,
oifi.BASE_MEASURE,
oifi.BASE_MEASURE_SPEC,
oifi.BASE_MEASURE_MATER,
oifi.RETAIL_PRICE,
oifi.TRADE_PRICE As PRICE,
oifi.AMOUNT,
oifi.Sum,
cast(oifi.AMOUNT as bigint) As OVERAMOUNT,
oifi.OVER_AMOUNT,
oifi.OVER_SUM,
oifi.BATCH_NO,
isNull(convert(varchar,oifi.VALID_DATE,23),'-') As VALID_DATE,
oifi.State,
(Case oifi.State When '1' Then '未确认' When '2' Then '已确认' When '3' Then '作废' End) As StateName,
oif.CREATE_USER_ID As Send_Operator_Id,
oif.CREATE_USER_NAME As Send_Operator_Name,
oif.CREATE_DATE As Send_Operate_Date
From HC_ORD_INVOICE_FROM oif,HC_ORD_INVOICE_FROM_ITEM oifi,HC_ORD_PRODUCT op,HC_ORG org1,HC_ORG org2,HC_ORG org3
Where oif.Id=@INVOICE_FROM_ID And oif.Id=oifi.INVOICE_FROM_ID And oifi.PROJECT_PROD_ID=op.ID And org1.Id=oifi.SALER_ID And org2.Id=oif.SENDER_ID And org3.Id=oifi.MANUFACTURE_ID
)As Tab Left Join HC_ORD_HIT_COMM ohc
on Tab.PROJECT_ID=ohc.PROJECT_ID and Tab.PROJECT_PRODUCT_ID=ohc.PROJECT_PROD_ID and Tab.SPEC_ID=ohc.SPEC_ID and Tab.MODEL_ID=ohc.MODEL_ID
");
if (!string.IsNullOrEmpty(StrInvoiceFromId))
{
DbParameter paInvoiceFromId = DbFacade.CreateParameter();
paInvoiceFromId.ParameterName = "INVOICE_FROM_ID";
paInvoiceFromId.DbType = DbType.String;
paInvoiceFromId.Value = StrInvoiceFromId;
parameters.Add(paInvoiceFromId);
}
strSql.Append(" Order By Tab.Send_Operate_Date Desc");
try
{
dt = base.DbFacade.SQLExecuteDataTable(strSql.ToString(), parameters.ToArray());
}
catch (Exception ex)
{
throw ex;
}
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);
}