public DataSet LoadForCheckCancelReceive(int requestSysNo)
{
DataCommand selectCommand = DataCommandManager.GetDataCommand("LoadForCheckCancelReceive");
selectCommand.SetParameterValue("@RequestSysNo", requestSysNo);
return(selectCommand.ExecuteDataSet());
}
public List <PriceChangeMaster> GetPriceChangeByStatus(RequestPriceStatus status)
{
DataCommand cmd = DataCommandManager.GetDataCommand("PriceChange_GetByPriceChangeByStatus");
cmd.SetParameterValue("@Status", status);
DataSet ds = cmd.ExecuteDataSet();
List <PriceChangeMaster> masterLst = new List <PriceChangeMaster>();
List <PriceChangeItem> itemList = new List <PriceChangeItem>();
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
masterLst = DataMapper.GetEntityList <PriceChangeMaster, List <PriceChangeMaster> >(ds.Tables[0].Rows);
itemList = DataMapper.GetEntityList <PriceChangeItem, List <PriceChangeItem> >(ds.Tables[1].Rows);
masterLst.ForEach(p =>
{
p.ItemList = itemList.Where(item => item.MasterSysNo == p.SysNo).ToList();
});
}
return(masterLst);
}
public static DataSet GetProductItemNoList(string companyCode)
{
DataCommand cmd = DataCommandManager.GetDataCommand("GetProductItemNoList");
cmd.SetParameterValue("@CompanyCode", companyCode);
return(cmd.ExecuteDataSet());
}
/// <summary>
/// 获取告警类型数据
/// </summary>
/// <param name="whereStr"></param>
/// <returns></returns>
public DataTable GetAlarmListIndex(QueryAlarmNew alarmNew)
{
int intPazeSize = alarmNew.PageSize;
int intNext = intPazeSize * (alarmNew.PageIndex - 1);
DataTable dtAlarmType = new DataTable();
//string strSql = @" SELECT distinct TOP " + intPazeSize + " id, ALARMOBJID,ALARMTYPE,ALARMTYPENAME,ALARMLEVEL,ALARMOBJNAME,ALARMCONTENT,STATUS,ALARMTIME,location " +
// " from AlarmAreaDevice WHERE id not in " +
// " ( SELECT TOP " + intNext + " id from AlarmAreaDevice ) and 1=1 ";
string whereStr = "";
string orderStr = "ORDER BY id desc";
whereStr += " and ALARMTIME between '" + alarmNew.StartTime.ToString("yyyy-MM-dd 00:00:00") + "' and '" + alarmNew.EndTime.ToString("yyyy-MM-dd 23:59:59") + "'";
if (alarmNew.AlarmStatus != "")
{
whereStr += " and STATUS = " + alarmNew.AlarmStatus;
}
var cmd = new DataCommand("GetAlarmListIndex", new SqlCustomDbCommand());
cmd.ReplaceParameterValue("#PageSize#", intPazeSize.ToString());
cmd.ReplaceParameterValue("#Next#", intNext.ToString());
cmd.ReplaceParameterValue("#whereStr#", whereStr);
cmd.ReplaceParameterValue("#orderStr#", orderStr);
return(cmd.ExecuteDataSet().Tables[0]);
}
/// <summary>
/// 根据TB_AREA表获取区域列表信息
/// </summary>
/// <returns></returns>
public DataSet GetAreaList()
{
// try
// {
// //FROM TB_AREA ORDER BY AREAID";
// string SQL = @"select layerobjectid,layerobjectname,layerobjectparentid,layerobjectdeepth from dbo.Becm_LayerObject
//order by layerobjectid";
// return SqlHelper.Query(SQL);
// }
// catch (Exception ex)
// {
// throw new Exception(ex.Message);
// }
try
{
var cmd = new DataCommand("GetAreaListNew", new SqlCustomDbCommand());
//cmd.ReplaceParameterValue("#Sort#", order);
return(cmd.ExecuteDataSet());
}
catch (Exception ee)
{
throw ee;
}
}
/// <summary>
/// 根据套餐编号获取套餐信息和套餐商品列表
/// </summary>
/// <param name="comboSysNo">套餐编号</param>
/// <returns></returns>
public static ComboInfo GetComboByComboSysNo(int comboSysNo)
{
DataCommand cmd = DataCommandManager.GetDataCommand("Promotion_GetComboByComboSysNo");
cmd.SetParameterValue("@ComboSysNo", comboSysNo);
DataSet ds = cmd.ExecuteDataSet();
ComboInfo combo = new ComboInfo();
DataTable dtMaster = ds.Tables[0];
DataTable dtItems = ds.Tables[1];
if (dtMaster != null && dtMaster.Rows.Count > 0)
{
combo = DataMapper.GetEntity <ComboInfo>(dtMaster.Rows[0], (row, entity) =>
{
entity.IsShowName = row["IsShow"] == null ? false : row["IsShow"].ToString().Trim().ToUpper() == "Y" ? true : false;
});
combo.Items = DataMapper.GetEntityList <ComboItem, List <ComboItem> >(dtItems.Rows, (row, entity) =>
{
entity.IsMasterItemB = row["IsMasterItem"] == null ? false : (row["IsMasterItem"].ToString() == "1" ? true : false);
});
}
return(combo);
}
public static ProductStockAdjustInfo GetProductStockAdjustmentInfo(int adjustSysNo)
{
ProductStockAdjustInfo adjustInfo = null;
DataCommand cmd = DataCommandManager.GetDataCommand("GetProductStockAdjustmentInfo");
cmd.SetParameterValue("@SysNo", adjustSysNo);
var sourceEntity = cmd.ExecuteDataSet();
if (null != sourceEntity && sourceEntity.Tables.Count > 0)
{
if (sourceEntity.Tables.Count >= 1)
{
DataTable mainInfoDt = sourceEntity.Tables[0];
if (mainInfoDt.Rows.Count > 0)
{
adjustInfo = new ProductStockAdjustInfo();
adjustInfo = DataMapper.GetEntity <ProductStockAdjustInfo>(mainInfoDt.Rows[0]);
}
}
if (sourceEntity.Tables.Count >= 2)
{
DataTable itemsInfoDt = sourceEntity.Tables[1];
if (itemsInfoDt.Rows.Count > 0)
{
foreach (DataRow dr in itemsInfoDt.Rows)
{
adjustInfo.AdjustItemList.Add(DataMapper.GetEntity <ProductStockAdjustItemInfo>(dr));
}
}
}
}
return(adjustInfo);
}
/// <summary>
/// 查询订单
/// </summary>
/// <returns></returns>
public static List <SOInfo> QuerySO()
{
DataCommand cmd = DataCommandManager.GetDataCommand("QuerySO");
List <SOInfo> list = new List <SOInfo>();
DataSet ds = cmd.ExecuteDataSet();
foreach (DataRow dr in ds.Tables[0].Rows)
{
SOInfo soInfo = DataMapper.GetEntity <SOInfo>(dr);
if (soInfo.OrderItems == null)
{
soInfo.OrderItems = new List <SOItemInfo>();
}
foreach (DataRow itemDR in ds.Tables[1].Rows)
{
int soSysNo = 0;
int.TryParse(itemDR["SOSysNo"].ToString(), out soSysNo);
if (soSysNo == soInfo.SysNo)
{
soInfo.OrderItems.Add(DataMapper.GetEntity <SOItemInfo>(itemDR));
}
}
list.Add(soInfo);
}
return(list);
}
public List <ComboInfo> GetComboListByProductSysNo(int productSysNo)
{
DataCommand cmd = DataCommandManager.GetDataCommand("GetComboListByProductSysNo");
cmd.SetParameterValue("@ProductSysNo", productSysNo);
DataSet ds = cmd.ExecuteDataSet();
List <ComboInfo> comboList = new List <ComboInfo>();
DataTable dtMaster = ds.Tables[0];
DataTable dtItems = ds.Tables[1];
if (dtMaster != null && dtMaster.Rows.Count > 0)
{
comboList = DataMapper.GetEntityList <ComboInfo, List <ComboInfo> >(dtMaster.Rows);
List <ComboItem> tempItemList = DataMapper.GetEntityList <ComboItem, List <ComboItem> >(dtItems.Rows, (row, entity) =>
{
entity.IsMasterItemB = row["IsMasterItem"] == null ? false : (row["IsMasterItem"].ToString() == "1" ? true : false);
});
foreach (ComboInfo combo in comboList)
{
combo.Items = new List <ComboItem>();
foreach (ComboItem item in tempItemList)
{
if (combo.SysNo == item.ComboSysNo)
{
combo.Items.Add(item);
}
}
}
}
return(comboList);
}
public DataSet MyCarInfoByCityID(string cityid)
{
DataCommand command = new DataCommand(ConnectionString, GetDbCommand(CarStatement.MyCarInfoByCityID, "Text"));
command.AddInputParameter("@CityID", DbType.String, cityid);
return(command.ExecuteDataSet());
}
/// <summary>
/// 获取查询的列表
/// </summary>
/// <param name="whereStr">查询的条件</param>
/// <param name="order">排序的方式</param>
/// <param name="parameters">查询的参数</param>
/// <returns></returns>
public DataTable GetList(string whereStr, string order)
{
// StringBuilder strSql = new StringBuilder();
// strSql.Append(@"select layerobjectid,layerobjectname,layerobjectpic,layerobjectcontent,layerobjectparentid,bgflag,areatype
// from (select layerobjectid,layerobjectname,layerobjectpic,layerobjectcontent,layerobjectparentid,bgflag,areatype
// from Becm_LayerObject union
// select layerobjectid,layerobjectname,layerobjectpic,layerobjectcontent,layerobjectparentid,bgflag,areatype
// from Becm_FunctionObject)
// aa where 1=1");
// if (!whereStr.Equals(string.Empty))
// {
// strSql.AppendFormat(" and {0}", whereStr);
// }
// strSql.AppendFormat(" order by {0}", order);
// DataTable dt = SqlHelper.Query(strSql.ToString(), parameters).Tables[0];
// return dt;
try
{
var cmd = new DataCommand("GetList", new SqlCustomDbCommand());
cmd.ReplaceParameterValue("#whereStr#", whereStr);
//cmd.ReplaceParameterValue("#Sort#", order);
return(cmd.ExecuteDataSet().Tables[0]);
}
catch (Exception ee)
{
throw ee;
}
}
/// <summary>
/// ////////////////////////////////
/// </summary>
/// <param name="productSysNo"></param>
/// <returns></returns>
public static ProductPriceInfo QueryProductPriceInfo(int productSysNo)
{
ProductPriceInfo result = new ProductPriceInfo();
DataCommand cmd = DataCommandManager.GetDataCommand("GetProductPriceById");
cmd.SetParameterValue("@SysNo", productSysNo);
cmd.SetParameterValue("@CompanyCode", Settings.CompanyCode);
using (DataTable drs = cmd.ExecuteDataSet().Tables[0])
{
foreach (DataRow dr in drs.Rows)
{
//result.Id = dr["ProductID"].ToString();
result.SysNo = (int)dr["SysNo"];
if (dr["CurrentPrice"] != null && dr["CurrentPrice"].ToString() != "")
{
result.CurrentPrice = (decimal)dr["CurrentPrice"];
}
if (dr["ItemPoint"] != null && dr["ItemPoint"].ToString() != "")
{
result.Point = Convert.ToDecimal(dr["ItemPoint"]);
}
if (dr["Category3SysNo"] != null && dr["Category3SysNo"].ToString() != "")
{
result.C3SysNo = Convert.ToInt32(dr["Category3SysNo"]);
}
if (dr["UnitCost"] != null && dr["UnitCost"].ToString() != "")
{
result.UnitCost = Convert.ToInt32(dr["UnitCost"]);
}
}
}
return(result);
}
public static List <AuthFunctionModel> LoadFunctionsByUserSysNo(int userSysNo, string applicationKey, string topName)
{
DataCommand cmd = new DataCommand("LoadFunctionsByUserSysNo");
cmd.SetParameter("@UserSysNo", DbType.Int32, userSysNo);
cmd.SetParameter("@ApplicationKey", DbType.AnsiString, applicationKey);
var ds = cmd.ExecuteDataSet();
var permissions = DataMapper.GetEntityList <AuthPermissionModel, List <AuthPermissionModel> >(ds.Tables[0].Rows);
var functions = DataMapper.GetEntityList <AuthFunctionModel, List <AuthFunctionModel> >(ds.Tables[1].Rows);
if (!string.IsNullOrWhiteSpace(topName))
{
var topEntity = functions.FirstOrDefault(x => x.FunctionName == topName);
if (topEntity != null)
{
functions.RemoveAll(x => !x.SysCode.StartsWith(topEntity.SysCode));
}
}
for (int i = 0; i < functions.Count; i++)
{
functions[i].Permissions = permissions.FindAll(x => x.FunctionSysNo == functions[i].SysNo);
}
return(functions);
}
/// <summary>
/// 支付时获取SO详细信息，从CenterDB获取
/// </summary>
/// <param name="SOSysNo">订单号</param>
/// <returns>订单详细信息</returns>
public static PayOrderInfo PayGetCenterDBOrderInfoBySOSysNo(int SOSysNo)
{
PayOrderInfo orderInfo = null;
DataCommand command = DataCommandManager.GetDataCommand("Order_PayGetCenterDBOrderInfoBySOSysNo");
command.SetParameterValue("@SOID", SOSysNo);
DataSet result = command.ExecuteDataSet();
if (result != null && result.Tables.Count > 0)
{
DataTable masterTable = result.Tables[0];
if (masterTable.Rows != null && masterTable.Rows.Count > 0)
{
orderInfo = DataMapper.GetEntity <PayOrderInfo>(masterTable.Rows[0]);
}
if (result.Tables != null && result.Tables.Count > 1)
{
DataTable itemTable = result.Tables[1];
if (itemTable.Rows != null && itemTable.Rows.Count > 0 && orderInfo != null)
{
orderInfo.SOItemList = DataMapper.GetEntityList <PaySOItemInfo, List <PaySOItemInfo> >(itemTable.Rows);
}
}
}
return(orderInfo);
}
//public List<ComboInfo> GetComboListByProductSysNo(int productSysNo)
//{
// DataCommand cmd = DataCommandManager.GetDataCommand("GetComboListByProductSysNo");
// cmd.SetParameterValue("@ProductSysNo", productSysNo);
// DataSet ds = cmd.ExecuteDataSet();
// List<ComboInfo> comboList = new List<ComboInfo>();
// DataTable dtMaster = ds.Tables[0];
// DataTable dtItems = ds.Tables[1];
// if (dtMaster != null && dtMaster.Rows.Count > 0)
// {
// comboList = DataMapper.GetEntityList<ComboInfo, List<ComboInfo>>(dtMaster.Rows);
// List<ComboItem> tempItemList = DataMapper.GetEntityList<ComboItem, List<ComboItem>>(dtItems.Rows, (row, entity) =>
// {
// entity.IsMasterItem = row["IsMasterItem"] == null ? false : (row["IsMasterItem"].ToString() == "1" ? true : false);
// });
// foreach (ComboInfo combo in comboList)
// {
// combo.Items = new List<ComboItem>();
// foreach (ComboItem item in tempItemList)
// {
// if (combo.SysNo == item.ComboSysNo)
// {
// combo.Items.Add(item);
// }
// }
// }
// }
// return comboList;
//}
//public virtual int CheckComboExits(string comboName, int productSysNo)
//{
// DataCommand dc = DataCommandManager.GetDataCommand("CheckSaleRuleExits");
// dc.SetParameterValue("@ProductSysNo", productSysNo);
// dc.SetParameterValue("@SaleRuleName", comboName);
// dc.SetParameterValue("@CreateTime", DateTime.Now.Date);
// return dc.ExecuteScalar<int>();
//}
/// <summary>
/// 加载Combo所有信息
/// </summary>
/// <param name="sysNo"></param>
/// <returns></returns>
public virtual ComboQueryResult Load(int sysNo)
{
DataCommand cmd = DataCommandManager.GetDataCommand("LoadComboInfo");
cmd.SetParameterValue("@SysNo", sysNo);
DataSet ds = cmd.ExecuteDataSet();
DataTable dtMaster = ds.Tables[0];
DataTable dtItem = ds.Tables[1];
if (dtMaster == null || dtMaster.Rows.Count == 0)
{
return(null);
}
var info = DataMapper.GetEntity <ComboQueryResult>(dtMaster.Rows[0]);
var itemList = DataMapper.GetEntityList <ComboItemQueryResult, List <ComboItemQueryResult> >(dtItem.Rows);
info.Items = new List <ComboItem>();
foreach (var item in itemList.OrderByDescending(item => item.IsMasterItem))
{
info.Items.Add(item);
}
return(info);
}
/// <summary>
/// 查询入库单信息
/// </summary>
/// <returns></returns>
public static List <POInfo> QueryPO()
{
DataCommand cmd = DataCommandManager.GetDataCommand("QueryPO");
List <POInfo> list = new List <POInfo>();
DataSet ds = cmd.ExecuteDataSet();
foreach (DataRow dr in ds.Tables[0].Rows)
{
POInfo poInfo = DataMapper.GetEntity <POInfo>(dr);
if (poInfo.Items == null)
{
poInfo.Items = new List <POItemInfo>();
}
foreach (DataRow itemDR in ds.Tables[1].Rows)
{
if (itemDR["POID"].ToString() == poInfo.BillNo)
{
poInfo.Items.Add(DataMapper.GetEntity <POItemInfo>(itemDR));
}
}
list.Add(poInfo);
}
return(list);
}
/// <summary>
/// 申报时获取订单信息
/// </summary>
/// <param name="SOSysNo">订单号</param>
/// <returns></returns>
public DeclareOrderInfo DeclareGetOrderInfoBySOSysNo(int SOSysNo)
{
DeclareOrderInfo orderInfo = null;
DataCommand command = DataCommandManager.GetDataCommand("SO_DeclareGetOrderInfoBySOSysNo");
command.SetParameterValue("@SOID", SOSysNo);
DataSet result = command.ExecuteDataSet();
if (result != null && result.Tables.Count > 0)
{
DataTable masterTable = result.Tables[0];
if (masterTable.Rows != null && masterTable.Rows.Count > 0)
{
orderInfo = DataMapper.GetEntity <DeclareOrderInfo>(masterTable.Rows[0]);
}
if (result.Tables != null && result.Tables.Count > 1)
{
DataTable itemTable = result.Tables[1];
if (itemTable.Rows != null && itemTable.Rows.Count > 0 && orderInfo != null)
{
orderInfo.SOItemList = DataMapper.GetEntityList <DeclareSOItemInfo, List <DeclareSOItemInfo> >(itemTable.Rows);
}
}
if (result.Tables != null && result.Tables.Count > 2)
{
DataTable payTable = result.Tables[2];
orderInfo.PayInfo = DataMapper.GetEntity <DeclareSOPayInfo>(payTable.Rows[0]);
}
}
return(orderInfo);
}
/// <summary>
/// 加载团购信息
/// </summary>
/// <param name="sysNo"></param>
/// <returns></returns>
public GroupBuyingQueryResult Load(int sysNo)
{
DataCommand cmd = DataCommandManager.GetDataCommand("GetProductGroupBuyingEntity");
cmd.SetParameterValue("@SysNo", sysNo);
var ds = cmd.ExecuteDataSet();
GroupBuyingQueryResult result = null;
if (ds.Tables[0].Rows.Count > 0)
{
result = DataMapper.GetEntity <GroupBuyingQueryResult>(ds.Tables[0].Rows[0]);
}
//获取团购价格
GroupBuyingPriceInfo priceInfo = null;
if (ds.Tables[1].Rows.Count > 0)
{
priceInfo = DataMapper.GetEntity <GroupBuyingPriceInfo>(ds.Tables[1].Rows[0]);
}
if (priceInfo != null)
{
result.GroupBuyPrice = priceInfo.GroupBuyingPrice;
}
return(result);
}
public int CalcVSPOQuantity(int soItemSysNo)
{
DataCommand dataCommand = DataCommandManager.GetDataCommand("CalculatePurchaseQty");
dataCommand.SetParameterValue("@SOItemSysNo", soItemSysNo);
DataTable dt = dataCommand.ExecuteDataSet().Tables[0];
int purchaseQty = 0, qty = 0;
if (dt.Rows.Count > 0)
{
if ((dt.Rows[0]["InventoryQty"] == null) || (dt.Rows[0]["InventoryQty"].ToString() == "-999999"))
{
qty = 0;
}
else
{
qty = Convert.ToInt32(dt.Rows[0]["InventoryQty"]);
}
int unPayOrderQty = CalUnPayOrderQty(Convert.ToInt32(dt.Rows[0]["UnProductSysNo"]), dt.Rows[0]["UnStockSysNo"].ToString());
qty = qty + unPayOrderQty;
int siqty = Convert.ToInt32(dt.Rows[0]["Quantity"]);
if (qty < 0)//虚库
{
if ((System.Math.Abs(qty) - siqty) >= 0)
{
purchaseQty = siqty;
}
else
{
purchaseQty = System.Math.Abs(qty);
}
}
}
return(purchaseQty);
}
/// <summary>
/// 加载Combo所有信息
/// </summary>
/// <param name="sysNo"></param>
/// <returns></returns>
public virtual ComboInfo Load(int sysNo)
{
ComboInfo info = null;
DataCommand cmd = DataCommandManager.GetDataCommand("LoadComboInfo");
cmd.SetParameterValue("@SysNo", sysNo);
DataSet ds = cmd.ExecuteDataSet();
DataTable dtMaster = ds.Tables[0];
DataTable dtItem = ds.Tables[1];
if (dtMaster == null || dtMaster.Rows.Count == 0)
{
return(null);
}
info = DataMapper.GetEntity <ComboInfo>(dtMaster.Rows[0], (row, entity) =>
{
entity.Name = new BizEntity.LanguageContent(row["SaleRuleName"].ToString().Trim());
entity.TargetStatus = entity.Status;
entity.IsShowName = row["IsShow"].ToString().Trim().ToUpper() == "Y" ? true : false;
});
info.Items = DataMapper.GetEntityList <ComboItem, List <ComboItem> >(dtItem.Rows, (row, entity) =>
{
entity.IsMasterItemB = row["IsMasterItem"] == null ? false : (row["IsMasterItem"].ToString() == "1" ? true : false);
});
return(info);
}
/// <summary>
/// 根据TB_AREA表获取区域列表信息
/// </summary>
/// <returns></returns>
public DataSet GetAreaList()
{
// try
// {
// //FROM TB_AREA ORDER BY AREAID";
// string SQL = @"select layerobjectid,layerobjectname,layerobjectparentid,layerobjectdeepth from dbo.Becm_LayerObject
//order by layerobjectid";
// return SqlHelper.Query(SQL);
// }
// catch (Exception ex)
// {
// throw new Exception(ex.Message);
// }
try
{
var cmd = new DataCommand("GetAreaListNew", new SqlCustomDbCommand());
//cmd.ReplaceParameterValue("#Sort#", order);
return cmd.ExecuteDataSet();
}
catch (Exception ee)
{
throw ee;
}
}
public static DataSet GetPassGiftCardInfo(string GiftCardFabricationSysNo, string companyCode)
{
DataCommand cmd = DataCommandManager.GetDataCommand("GetPassGiftCardInfo");
cmd.SetParameterValue("@GiftCardFabricationSysNo", GiftCardFabricationSysNo);
cmd.SetParameterValue("@CompanyCode", companyCode);
return(cmd.ExecuteDataSet());
}
/// <summary>
/// 获取我的租赁/试驾信息
/// </summary>
/// <param name="userid">用户ID</param>
/// <param name="RType">ZL：汽车租赁 SJ:汽车试驾</param>
/// <returns></returns>
public DataSet MyReservation(string userid, string RType)
{
DataCommand command = new DataCommand(ConnectionString, GetDbCommand(CarStatement.MyReservation, "Text"));
command.AddInputParameter("@CustomerID", DbType.String, userid);
command.AddInputParameter("@RType", DbType.String, RType);
return(command.ExecuteDataSet());
}
public static DataSet GetSalesMails(string productSysNo, string CompanyCode)
{
DataCommand cmd = DataCommandManager.GetDataCommand("GetProductSlEmail");
cmd.SetParameterValue("@ProductSysNo", productSysNo);
cmd.SetParameterValue("@CompanyCode", CompanyCode);
return(cmd.ExecuteDataSet());
}
public DataSet GetAllTest()
{
DataSet result = new DataSet();
DataCommand command = new DataCommand(ConnectionString, GetDbCommand(BaseDataStatement.TempTest, "Text"));
result = command.ExecuteDataSet();
return(result);
}
/// <summary>
/// 获取告警类型数据
/// </summary>
/// <param name="whereStr"></param>
/// <returns></returns>
public DataTable GetAlarmList(QueryAlarmNew alarmNew, string groupId)
{
try
{
int intPazeSize = alarmNew.PageSize;
int intNext = intPazeSize * (alarmNew.PageIndex - 1);
string whereStr = "";
string orderStr = "ORDER BY id desc";
whereStr += " and ALARMTIME between '" + alarmNew.StartTime.ToString("yyyy-MM-dd 00:00:00") + "' and '" + alarmNew.EndTime.ToString("yyyy-MM-dd 23:59:59") + "'";
if (alarmNew.AlarmLevel != "")
{
whereStr += " and ALARMLEVEL = " + alarmNew.AlarmLevel;
}
if (alarmNew.AlarmStatus != "")
{
whereStr += " and STATUS = " + alarmNew.AlarmStatus;
}
if (alarmNew.AlarmType != "")
{
whereStr += " and alarmtype = " + alarmNew.AlarmType;
}
IList <string> lstAreaId = GetAlarmListAreaId(alarmNew.ObjectId, alarmNew.ObjType);
IList <string> lstDeviceId = GetAlarmListDeviceId(alarmNew.ObjectId, alarmNew.ObjType);
whereStr += " and (";
if (lstAreaId.Count > 0)
{
string strAreaId = string.Join(",", lstAreaId.ToArray());
whereStr += " ALARMOBJID in (" + strAreaId + ") and alarmObjType=32 ";
}
else
{
whereStr += "1=2";
}
whereStr += " or ";
if (lstDeviceId.Count > 0)
{
string strDeviceId = string.Join(",", lstDeviceId.ToArray());
whereStr += " ALARMOBJID in (" + strDeviceId + ") and alarmObjType=31 ";
}
else
{
whereStr += "1=2";
}
whereStr += ")";
var cmd = new DataCommand("GetAlarmList", new SqlCustomDbCommand());
cmd.ReplaceParameterValue("#PageSize#", intPazeSize.ToString());
cmd.ReplaceParameterValue("#Next#", intNext.ToString());
cmd.ReplaceParameterValue("#whereStr#", whereStr);
cmd.ReplaceParameterValue("#orderStr#", orderStr);
return(cmd.ExecuteDataSet().Tables[0]);
}
catch (Exception ex)
{
return(null);
}
}
/// <summary>
/// 根据选择对象获取所有子区域ID
/// </summary>
/// <param name="whereStr"></param>
/// <returns></returns>
private IList <string> GetAlarmListDeviceId(int ObjectId, AreaType ObjType)
{
string whereStr = "";
DataTable dtAlarmType = new DataTable();
IList <string> lstDeviceId = new List <string>();
string strSql = "";
if (ObjType == AreaType.Area)
{
strSql = @" select distinct s2.deviceid as id from f_GetChildAreaId(" + ObjectId + ") s1 inner join Becm_Device s2 on s1.id = s2.areaid ";
if (!whereStr.Equals(string.Empty))
{
strSql = strSql + whereStr;
}
var cmd = new DataCommand("f_GetChildAreaIdDevice", new SqlCustomDbCommand());
cmd.ReplaceParameterValue("#ObjectId#", ObjectId.ToString());
dtAlarmType = cmd.ExecuteDataSet().Tables[0];
// dtAlarmType = SqlHelper.Query(strSql).Tables[0];
if (dtAlarmType.Rows.Count > 0)
{
foreach (DataRow row in dtAlarmType.Rows)
{
if (row["id"] != DBNull.Value)
{
lstDeviceId.Add(row["id"].ToString());
}
}
}
}
else
{
strSql = @" select distinct s2.deviceid as id from f_GetChildFuncAreaId(" + ObjectId + ") s1 inner join Becm_Device s2 on s1.id = s2.areaid2 ";
if (!whereStr.Equals(string.Empty))
{
strSql = strSql + whereStr;
}
//dtAlarmType = SqlHelper.Query(strSql).Tables[0];
var cmd = new DataCommand("f_GetChildFuncAreaIdDevice", new SqlCustomDbCommand());
cmd.ReplaceParameterValue("#ObjectId#", ObjectId.ToString());
dtAlarmType = cmd.ExecuteDataSet().Tables[0];
if (dtAlarmType.Rows.Count > 0)
{
foreach (DataRow row in dtAlarmType.Rows)
{
if (row["id"] != DBNull.Value)
{
lstDeviceId.Add(row["id"].ToString());
}
}
}
}
return(lstDeviceId);
}
/// <summary>
/// 获取表的基础数据集
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static DataTable GetBaseData(BaseDataModel model)
{
var dtold = new DataTable();
var tcount = new List <string>();
var cmdName = string.Empty;
tcount = GetTcountNames(model, tcount);
switch (model.Unit)
{
case ChartUnit.unit_hour:
cmdName = "getBaseDataByHour";
break;
case ChartUnit.unit_day:
cmdName = "getBaseDataByDate";
break;
case ChartUnit.unit_month:
cmdName = "getBaseDataByMonth";
break;
default:
cmdName = "getBaseDataByMonth";
break;
}
//var cmd = new DataCommand(cmdName, new SqlCustomDbCommand());
//cmd.SetParameterValue("@countid", model.ObjectId);
//cmd.SetParameterValue("@itemcode", model.ItemCode);
for (var i = 0; i < tcount.Count; i++)
{
var cmd = new DataCommand(cmdName, new SqlCustomDbCommand());
cmd.SetParameterValue("@countid", model.ObjectId);
cmd.SetParameterValue("@itemcode", model.ItemCode);
cmd.ReplaceParameterValue("#TableName#", tcount[i]);
//cmd.SetParameterValue("#TableName#", tcount[i]);
//object param = cmd.GetParameterValue("#TableName#");
//cmd.SetParameterValue("@tname", tcount[i]);
DataTable dts;
if (model.Unit == ChartUnit.unit_hour)
{
cmd.SetParameterValue("@cdate", model.Starttime.ToString("yyyy-MM-dd"));
}
try
{
dts = cmd.ExecuteDataSet().Tables[0];
dtold = i.Equals(0) ? dts.Clone() : dtold;
dtold = UniteDataTable(dtold, dts.Copy());
}
catch (Exception e)
{
//throw new Exception(e.Message);
}
}
return(MakerData(dtold, model));
}
public static DataTable GetProductAccessoriesByPOSysno(int poSysNo)
{
DataCommand cmd = DataCommandManager.GetDataCommand("GetProductAccessoriesByPOSysno");
cmd.SetParameterValue("@POSysNo", poSysNo);
cmd.SetParameterValue("@CompanyCode", Settings.CompanyCode);
return(cmd.ExecuteDataSet().Tables[0]);
}
private string GetObjectName(int isarea, int id)
{
var cmd = new DataCommand("getImportObject", new SqlCustomDbCommand());
cmd.SetParameterValue("@isarea", isarea);
cmd.SetParameterValue("@id", id);
var dt = cmd.ExecuteDataSet().Tables[0];
return(dt.Rows.Count > 0 ? dt.Rows[0][0].ToString() : string.Empty);
}
public static DataSet GetGiftCardFabricationMaster(string companyCode)
{
DataSet dsResult = new DataSet();
DataCommand cmd = DataCommandManager.GetDataCommand("GetGiftCardFabricationMaster");
cmd.SetParameterValue("@CompanyCode", companyCode);
dsResult = cmd.ExecuteDataSet();
return(dsResult);
}
/// <summary>
/// 获取表的基础数据集
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static DataTable GetBaseData(BaseDataModel model)
{
var dtold = new DataTable();
var tcount = new List<string>();
var cmdName = string.Empty;
tcount = GetTcountNames(model, tcount);
switch (model.Unit)
{
case ChartUnit.unit_hour:
cmdName = "getBaseDataByHour";
break;
case ChartUnit.unit_day:
cmdName = "getBaseDataByDate";
break;
case ChartUnit.unit_month:
cmdName = "getBaseDataByMonth";
break;
default:
cmdName = "getBaseDataByMonth";
break;
}
//var cmd = new DataCommand(cmdName, new SqlCustomDbCommand());
//cmd.SetParameterValue("@countid", model.ObjectId);
//cmd.SetParameterValue("@itemcode", model.ItemCode);
for (var i = 0; i < tcount.Count; i++)
{
var cmd = new DataCommand(cmdName, new SqlCustomDbCommand());
cmd.SetParameterValue("@countid", model.ObjectId);
cmd.SetParameterValue("@itemcode", model.ItemCode);
cmd.ReplaceParameterValue("#TableName#", tcount[i]);
//cmd.SetParameterValue("#TableName#", tcount[i]);
//object param = cmd.GetParameterValue("#TableName#");
//cmd.SetParameterValue("@tname", tcount[i]);
DataTable dts;
if (model.Unit == ChartUnit.unit_hour)
{
cmd.SetParameterValue("@cdate", model.Starttime.ToString("yyyy-MM-dd"));
}
try
{
dts = cmd.ExecuteDataSet().Tables[0];
dtold = i.Equals(0) ? dts.Clone() : dtold;
dtold = UniteDataTable(dtold, dts.Copy());
}
catch (Exception e)
{
//throw new Exception(e.Message);
}
}
return MakerData(dtold, model);
}
/// <summary>
/// 获取告警类型数据
/// </summary>
/// <param name="whereStr"></param>
/// <returns></returns>
public DataTable GetAlarmListIndex(QueryAlarmNew alarmNew)
{
int intPazeSize = alarmNew.PageSize;
int intNext = intPazeSize * (alarmNew.PageIndex - 1);
DataTable dtAlarmType = new DataTable();
//string strSql = @" SELECT distinct TOP " + intPazeSize + " id, ALARMOBJID,ALARMTYPE,ALARMTYPENAME,ALARMLEVEL,ALARMOBJNAME,ALARMCONTENT,STATUS,ALARMTIME,location " +
// " from AlarmAreaDevice WHERE id not in " +
// " ( SELECT TOP " + intNext + " id from AlarmAreaDevice ) and 1=1 ";
string whereStr = "";
string orderStr = "ORDER BY id desc";
whereStr += " and ALARMTIME between '" + alarmNew.StartTime.ToString("yyyy-MM-dd 00:00:00") + "' and '" + alarmNew.EndTime.ToString("yyyy-MM-dd 23:59:59") + "'";
if (alarmNew.AlarmStatus != "")
{
whereStr += " and STATUS = " + alarmNew.AlarmStatus;
}
var cmd = new DataCommand("GetAlarmListIndex", new SqlCustomDbCommand());
cmd.ReplaceParameterValue("#PageSize#", intPazeSize.ToString());
cmd.ReplaceParameterValue("#Next#", intNext.ToString());
cmd.ReplaceParameterValue("#whereStr#", whereStr);
cmd.ReplaceParameterValue("#orderStr#", orderStr);
return cmd.ExecuteDataSet().Tables[0];
}
/// <summary>
/// 获取告警类型
/// </summary>
/// <param name="whereStr"></param>
/// <returns></returns>
public DataTable GetAlarmType(string whereStr)
{
//DataTable dtAlarmType = new DataTable();
//string strSql = "select TYPE,NAME from TB_ALARMTYPE where 1=1";
//if (!whereStr.Equals(string.Empty))
//{
// strSql = strSql + whereStr;
//}
//dtAlarmType = SqlHelper.Query(strSql).Tables[0];
//return dtAlarmType;
var cmd = new DataCommand("GetAlarmType", new SqlCustomDbCommand());
cmd.ReplaceParameterValue("#whereStr#", whereStr);
return cmd.ExecuteDataSet().Tables[0];
}
private string GetObjectName(int isarea, int id)
{
var cmd = new DataCommand("getImportObject", new SqlCustomDbCommand());
cmd.SetParameterValue("@isarea", isarea);
cmd.SetParameterValue("@id", id);
var dt = cmd.ExecuteDataSet().Tables[0];
return dt.Rows.Count > 0 ? dt.Rows[0][0].ToString() : string.Empty;
}
/// <summary>
/// 获取表的基础数据集
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static DataTable GetBaseDataItem(BaseDataModelNew model)
{
DataTable dtold = new DataTable();
try
{
List<string> Tcount = new List<string>();
Tcount = GetTcountNames(model, Tcount);
for (int i = 0; i < Tcount.Count; i++)
{
string basesql = "";
string sql = "";
switch (model.Unit)
{
case "1":
basesql = " select top 1 CountID,Value24 as value,ItemCode from \"{0}\" where CountID='{1}' and CONVERT(varchar(100), CDate, 23)='{2}' and ItemCode='{3}'";
break;
case "2":
basesql = " select top 1 CountID,Value365 as value,ItemCode from \"{0}\" where CountID={1} and ItemCode='{3}'";
sql = string.Format(basesql, new object[] { Tcount[i], model.ObjectId, model.Starttime.ToString("yyyy-MM-dd"), model.ItemCode });
break;
case "3":
basesql = " select top 1 CountID,Value30 as value,ItemCode from \"{0}\" where CountID={1} and ItemCode='{3}'";
break;
default:
basesql = " select top 1 CountID,Value24 as value,ItemCode from \"{0}\" where CountID='{1}' and CONVERT(varchar(100), CDate, 23)='{2}' and ItemCode='{3}'";
break;
}
// sql = string.Format(basesql, new object[] { Tcount[i], model.Starttime, model.Endtime, model.ItemCode });
sql = string.Format(basesql, new object[] { Tcount[i], model.ObjectId, model.Starttime.ToString("yyyy-MM-dd"), model.ItemCode });
var cmd = new DataCommand("GetBaseDataItem", new SqlCustomDbCommand());
cmd.ReplaceParameterValue("#SQLSTR#", sql);
//DataTable dts = SqlHelper.Query(sql).Tables[0];
DataTable dts = cmd.ExecuteDataSet().Tables[0];
DataTable dtNew = GetNewDataByDts(model, dts);
dtold = i.Equals(0) ? dtNew.Clone() : dtold;
dtold = UniteDataTable(dtold, dtNew.Copy());
}
return dtold;
}
catch (Exception ex)
{
if (ex.ToString().IndexOf("对象名") > -1)
{
throw new Exception("缺少对应的数据表！");
}
else if (ex.ToString().IndexOf("列名") > -1)
{
throw new Exception("缺少对应的数据列！");
}
else
{
throw new Exception("数据表发生异常！");
}
}
}
/// <summary>
/// 根据选择对象获取所有子区域ID
/// </summary>
/// <param name="whereStr"></param>
/// <returns></returns>
private IList<string> GetAlarmListDeviceId(int ObjectId, AreaType ObjType)
{
string whereStr = "";
DataTable dtAlarmType = new DataTable();
IList<string> lstDeviceId = new List<string>();
string strSql = "";
if (ObjType == AreaType.Area)
{
strSql = @" select distinct s2.deviceid as id from f_GetChildAreaId(" + ObjectId + ") s1 inner join Becm_Device s2 on s1.id = s2.areaid ";
if (!whereStr.Equals(string.Empty))
{
strSql = strSql + whereStr;
}
var cmd = new DataCommand("f_GetChildAreaIdDevice", new SqlCustomDbCommand());
cmd.ReplaceParameterValue("#ObjectId#", ObjectId.ToString());
dtAlarmType = cmd.ExecuteDataSet().Tables[0];
// dtAlarmType = SqlHelper.Query(strSql).Tables[0];
if (dtAlarmType.Rows.Count > 0)
{
foreach (DataRow row in dtAlarmType.Rows)
{
if (row["id"] != DBNull.Value)
{
lstDeviceId.Add(row["id"].ToString());
}
}
}
}
else
{
strSql = @" select distinct s2.deviceid as id from f_GetChildFuncAreaId(" + ObjectId + ") s1 inner join Becm_Device s2 on s1.id = s2.areaid2 ";
if (!whereStr.Equals(string.Empty))
{
strSql = strSql + whereStr;
}
//dtAlarmType = SqlHelper.Query(strSql).Tables[0];
var cmd = new DataCommand("f_GetChildFuncAreaIdDevice", new SqlCustomDbCommand());
cmd.ReplaceParameterValue("#ObjectId#", ObjectId.ToString());
dtAlarmType = cmd.ExecuteDataSet().Tables[0];
if (dtAlarmType.Rows.Count > 0)
{
foreach (DataRow row in dtAlarmType.Rows)
{
if (row["id"] != DBNull.Value)
{
lstDeviceId.Add(row["id"].ToString());
}
}
}
}
return lstDeviceId;
}
/// <summary>
/// 获取告警类型数据
/// </summary>
/// <param name="whereStr"></param>
/// <returns></returns>
public DataTable GetAlarmList(QueryAlarmNew alarmNew, string groupId)
{
try
{
int intPazeSize = alarmNew.PageSize;
int intNext = intPazeSize * (alarmNew.PageIndex - 1);
string whereStr = "";
string orderStr = "ORDER BY id desc";
whereStr += " and ALARMTIME between '" + alarmNew.StartTime.ToString("yyyy-MM-dd 00:00:00") + "' and '" + alarmNew.EndTime.ToString("yyyy-MM-dd 23:59:59") + "'";
if (alarmNew.AlarmLevel != "")
{
whereStr += " and ALARMLEVEL = " + alarmNew.AlarmLevel;
}
if (alarmNew.AlarmStatus != "")
{
whereStr += " and STATUS = " + alarmNew.AlarmStatus;
}
if (alarmNew.AlarmType != "")
{
whereStr += " and alarmtype = " + alarmNew.AlarmType;
}
IList<string> lstAreaId = GetAlarmListAreaId(alarmNew.ObjectId, alarmNew.ObjType);
IList<string> lstDeviceId = GetAlarmListDeviceId(alarmNew.ObjectId, alarmNew.ObjType);
whereStr += " and (";
if (lstAreaId.Count > 0)
{
string strAreaId = string.Join(",", lstAreaId.ToArray());
whereStr += " ALARMOBJID in (" + strAreaId + ") and alarmObjType=32 ";
}
else
{
whereStr += "1=2";
}
whereStr += " or ";
if (lstDeviceId.Count > 0)
{
string strDeviceId = string.Join(",", lstDeviceId.ToArray());
whereStr += " ALARMOBJID in (" + strDeviceId + ") and alarmObjType=31 ";
}
else
{
whereStr += "1=2";
}
whereStr += ")";
var cmd = new DataCommand("GetAlarmList", new SqlCustomDbCommand());
cmd.ReplaceParameterValue("#PageSize#", intPazeSize.ToString());
cmd.ReplaceParameterValue("#Next#", intNext.ToString());
cmd.ReplaceParameterValue("#whereStr#", whereStr);
cmd.ReplaceParameterValue("#orderStr#", orderStr);
return cmd.ExecuteDataSet().Tables[0];
}
catch (Exception ex)
{
return null;
}
}
/// <summary>
/// 获取查询的列表
/// </summary>
/// <param name="whereStr">查询的条件</param>
/// <param name="order">排序的方式</param>
/// <param name="parameters">查询的参数</param>
/// <returns></returns>
public DataTable GetList(string whereStr, string order)
{
// StringBuilder strSql = new StringBuilder();
// strSql.Append(@"select layerobjectid,layerobjectname,layerobjectpic,layerobjectcontent,layerobjectparentid,bgflag,areatype
// from (select layerobjectid,layerobjectname,layerobjectpic,layerobjectcontent,layerobjectparentid,bgflag,areatype
// from Becm_LayerObject union
// select layerobjectid,layerobjectname,layerobjectpic,layerobjectcontent,layerobjectparentid,bgflag,areatype
// from Becm_FunctionObject)
// aa where 1=1");
// if (!whereStr.Equals(string.Empty))
// {
// strSql.AppendFormat(" and {0}", whereStr);
// }
// strSql.AppendFormat(" order by {0}", order);
// DataTable dt = SqlHelper.Query(strSql.ToString(), parameters).Tables[0];
// return dt;
try
{
var cmd = new DataCommand("GetList", new SqlCustomDbCommand());
cmd.ReplaceParameterValue("#whereStr#", whereStr);
//cmd.ReplaceParameterValue("#Sort#", order);
return cmd.ExecuteDataSet().Tables[0];
}
catch (Exception ee)
{
throw ee;
}
}
/// <summary>
/// 根据选择对象获取所有子区域ID
/// </summary>
/// <param name="whereStr"></param>
/// <returns></returns>
public IList<string> GetAlarmListAreaId(int ObjectId, AreaType ObjType)
{
string whereStr = "";
DataTable dtAlarmType = new DataTable();
IList<string> lstAreaId = new List<string>();
string strSql = "";
if (ObjType == AreaType.Area)
{
strSql = @" select * from f_GetChildAreaId(" + ObjectId + ") ";
if (!whereStr.Equals(string.Empty))
{
strSql = strSql + whereStr;
}
var cmd = new DataCommand("f_GetChildAreaId", new SqlCustomDbCommand());
cmd.ReplaceParameterValue("#ObjectId#", ObjectId.ToString());
dtAlarmType = cmd.ExecuteDataSet().Tables[0];
if (dtAlarmType.Rows.Count > 0)
{
foreach (DataRow row in dtAlarmType.Rows)
{
if (row["id"] != DBNull.Value)
{
lstAreaId.Add(row["id"].ToString());
}
}
}
}
else
{
strSql = @" select * from f_GetChildFuncAreaId(" + ObjectId + ") ";
if (!whereStr.Equals(string.Empty))
{
strSql = strSql + whereStr;
}
var cmd = new DataCommand("f_GetChildFuncAreaId", new SqlCustomDbCommand());
cmd.ReplaceParameterValue("#ObjectId#", ObjectId.ToString());
dtAlarmType = cmd.ExecuteDataSet().Tables[0];
if (dtAlarmType.Rows.Count > 0)
{
foreach (DataRow row in dtAlarmType.Rows)
{
if (row["id"] != DBNull.Value)
{
lstAreaId.Add(row["id"].ToString());
}
}
}
}
return lstAreaId;
}
/// <summary>
/// 获取查询的列表
/// </summary>
/// <param name="whereStr">查询的条件</param>
/// <param name="order">排序的方式</param>
/// <param name="parameters">查询的参数</param>
/// <returns></returns>
public DataSet GetItemCode(string whereStr)
{
//DataSet dsItemCode = new DataSet();
//string strSql = "select * from \"Becm_ItemCode\" where 1=1";
//if (!whereStr.Equals(string.Empty))
//{
// strSql = strSql + whereStr;
//}
//dsItemCode = SqlHelper.Query(strSql);
//return dsItemCode;
try
{
var cmd = new DataCommand("GetItemCode", new SqlCustomDbCommand());
cmd.ReplaceParameterValue("#whereStr#", whereStr);
//cmd.ReplaceParameterValue("#Sort#", order);
return cmd.ExecuteDataSet();
}
catch (Exception ee)
{
throw ee;
}
//return ds.Tables[0];
}
/// <summary>
/// 获取查询的列表
/// </summary>
/// <param name="whereStr">查询的条件</param>
/// <param name="order">排序的方式</param>
/// <param name="parameters">查询的参数</param>
/// <returns></returns>
public DataTable GetListItemCode(string whereStr, string order)
{
//StringBuilder strSql = new StringBuilder();
//strSql.Append("select * from \"Becm_ItemCode\" where 1=1");
//if (!whereStr.Equals(string.Empty))
//{
// strSql.AppendFormat(" and {0}", whereStr);
//}
//strSql.AppendFormat(" order by {0}", order);
//DataTable dt = SqlHelper.Query(strSql.ToString(), parameters).Tables[0];
//return dt;
try
{
var cmd = new DataCommand("GetListItemCode", new SqlCustomDbCommand());
cmd.ReplaceParameterValue("#whereStr#", whereStr);
return cmd.ExecuteDataSet().Tables[0];
}
catch (Exception ee)
{
throw ee;
}
}