private Type GetMemberType(DbColumnInfo colInfo)
{
var nullable = colInfo.Flags.IsSet(DbColumnFlags.Nullable);
var typeInfo = colInfo.TypeInfo;
var mType = typeInfo.VendorDbType.ColumnOutType;
if (mType.IsValueType && nullable)
{
mType = ReflectionHelper.GetNullable(mType);
}
Type forcedType;
if (_config.ForceDataTypes.TryGetValue(colInfo.ColumnName, out forcedType))
{
mType = forcedType;
return(mType);
}
if (mType == typeof(byte[]))
{
var changeToGuid =
_config.Options.IsSet(DbFirstOptions.Binary16AsGuid) && typeInfo.Size == 16 ||
_config.Options.IsSet(DbFirstOptions.BinaryKeysAsGuid) && colInfo.Flags.IsSet(DbColumnFlags.PrimaryKey | DbColumnFlags.ForeignKey);
if (changeToGuid)
{
_guidTypeInfo = _guidTypeInfo ?? _dbSettings.ModelConfig.Driver.TypeRegistry.GetDbTypeInfo(typeof(Guid), 0);
colInfo.TypeInfo = _guidTypeInfo;
mType = typeof(Guid);
}
}
return(mType);
}
protected override void PopulateVersionAndTypes()
{
base._sqlVersion = "10";
List <KeyValuePair <int, byte> > list = new List <KeyValuePair <int, byte> >();
using (SqlDataReader reader = new SqlCommand("select system_type_id, user_type_id, name from sys.types where system_type_id = user_type_id", base._cx).ExecuteReader())
{
while (reader.Read())
{
DbTypeInfo dbTypeInfo = SqlSchemaReader.GetDbTypeInfo(reader.GetString(2));
if (!((dbTypeInfo == null) || base._sqlTypes.ContainsKey(reader.GetInt32(1))))
{
base._sqlTypes.Add(reader.GetInt32(1), dbTypeInfo);
}
else
{
list.Add(new KeyValuePair <int, byte>(reader.GetInt32(1), reader.GetByte(0)));
}
}
}
foreach (KeyValuePair <int, byte> pair in list)
{
if (base._sqlTypes.ContainsKey(pair.Value))
{
base._sqlTypes[pair.Key] = base._sqlTypes[pair.Value];
}
}
}
public virtual DbTypeInfo GetDbTypeInfo(DbRow columnRow)
{
var charSize = columnRow.GetAsLong("CHARACTER_MAXIMUM_LENGTH");
var byteSize = columnRow.GetAsLong("CHARACTER_OCTET_LENGTH");
var dataTypeString = columnRow.GetAsString("DATA_TYPE").ToLowerInvariant();
var prec = columnRow.GetAsInt("NUMERIC_PRECISION");
var scale = columnRow.GetAsInt("NUMERIC_SCALE");
var isNullable = (columnRow.GetAsString("IS_NULLABLE") == "YES");
bool isMemo = (charSize < 0 || byteSize < 0);
var typeDef = Driver.TypeRegistry.FindVendorDbTypeInfo(dataTypeString);
// if(typeDef == null)
// typeDef = Driver.TypeRegistry.Types.FirstOrDefault(td => td.Aliases.Contains(dataTypeString));
if (typeDef == null)
{
return(null);
}
var isAutoMemo = Driver.TypeRegistry.AutoMemoTypes.Contains(dataTypeString);
var size = isAutoMemo? -1 : (charSize != 0 ? charSize : byteSize);
var typeSpec = typeDef.FormatTypeSpec(size, prec, scale, isMemo);
var typeInfo = new DbTypeInfo(typeDef, typeSpec, isNullable, size, (byte)prec, (byte)scale, typeDef.DefaultColumnInit);
// AssignValueConverters(typeInfo); - no need for value converters
return(typeInfo);
}
//used for creating db type info for parameters, when there's no member
public virtual DbTypeInfo GetDbTypeInfo(Type dataType, int size)
{
Type elemType;
if (dataType.IsListOfDbPrimitive(out elemType))
{
return(ConstructArrayTypeInfo(elemType));
}
bool isMemo = size < 0;
var colType = dataType;
var typeDef = FindVendorDbTypeInfo(colType, isMemo);
Util.Check(typeDef != null, "Failed to find vendor DB type for CLR type {0}.", dataType);
var typeSpec = typeDef.FormatTypeSpec(size, 18, 4, isMemo);
var typeInfo = new DbTypeInfo(typeDef, typeSpec, true, size, 0, 0, typeDef.DefaultColumnInit);
var colOutType = typeInfo.VendorDbType.ColumnOutType;
if (colOutType != dataType)
{
var conv = Converters.GetConverter(colOutType, dataType);
Util.Check(conv != null, "Failed to find value converter for types {0} <-> {1}", colOutType, dataType);
typeInfo.ColumnToPropertyConverter = conv.ColumnToProperty;
typeInfo.PropertyToColumnConverter = conv.PropertyToColumn;
}
return(typeInfo);
}
private DbTypeInfo GetDbTypeInfo <TEntity>()
where TEntity : IDbEntity, new()
{
Type entityType = typeof(TEntity);
DbTypeInfo entityDbTypeInfo;
if (!DbConnection.dbTypeInfo.TryGetValue(entityType, out entityDbTypeInfo))
{
IDbEntity defaultEntity = new TEntity();
SchemaCollection entitySchemaCollection = new SchemaCollection();
foreach (DbOperationInfo operation in defaultEntity.DbOperations.Values)
{
if (operation.ParameterType != null && !entitySchemaCollection.Contains(operation.ParameterType))
{
DataTable schemaTable = this.GetSchema(operation.ParameterType);
entitySchemaCollection.Add(schemaTable);
}
}
entityDbTypeInfo = new DbTypeInfo()
{
DefaultEntity = defaultEntity,
SchemaInfo = entitySchemaCollection
};
DbConnection.dbTypeInfo[entityType] = entityDbTypeInfo;
}
return(entityDbTypeInfo);
}
protected virtual void PopulateVersionAndTypes()
{
string cmdText = "select SERVERPROPERTY('productversion')\r\nselect xtype, xusertype, name from " + this._serverPrefix + "dbo.systypes";
List <KeyValuePair <short, byte> > list = new List <KeyValuePair <short, byte> >();
using (SqlDataReader reader = new SqlCommand(cmdText, this._cx).ExecuteReader())
{
reader.Read();
this._sqlVersion = reader.GetString(0);
if (reader.NextResult())
{
while (reader.Read())
{
DbTypeInfo dbTypeInfo = SqlSchemaReader.GetDbTypeInfo(reader.GetString(2));
if (!((dbTypeInfo == null) || this._sqlTypes.ContainsKey(reader.GetInt16(1))))
{
this._sqlTypes.Add(reader.GetInt16(1), dbTypeInfo);
}
else
{
list.Add(new KeyValuePair <short, byte>(reader.GetInt16(1), reader.GetByte(0)));
}
}
}
}
foreach (KeyValuePair <short, byte> pair in list)
{
if (this._sqlTypes.ContainsKey(pair.Value))
{
this._sqlTypes[pair.Key] = this._sqlTypes[pair.Value];
}
}
}
public ColumnDefinition(IMember member, DbTypeInfo type)
: this(
name: member.Name,
type: member.IsTimestamp ? MySqlDbType.Timestamp(6) : type
)
{
if (member.IsIdentity)
{
AutoIncriment = member.IsIdentity;
IsKey = true;
}
else if (member.IsOptional)
{
IsOptional = true;
}
if (member.IsTimestamp)
{
IsOptional = false;
Default = "CURRENT_TIMESTAMP(6)";
OnUpdate = "CURRENT_TIMESTAMP(6)";
}
}
public void Delete <TEntity>(TEntity[] entities)
where TEntity : IDbEntity, new()
{
const DbOperation operation = DbOperation.Delete;
Type entityType = typeof(TEntity);
DbTypeInfo entityDbTypeInfo = this.GetDbTypeInfo <TEntity>();
if (!entityDbTypeInfo.DefaultEntity.DbOperations.ContainsKey(operation))
{
throw new InvalidOperationException(string.Format("Operation {0} is not supported by type {1}.", operation, entityType.Name));
}
DbOperationInfo operationInfo = entityDbTypeInfo.DefaultEntity.DbOperations[operation];
using (DataTable entityTable = entityDbTypeInfo.SchemaInfo[operationInfo.ParameterType].Clone())
{
for (int i = 0; i < entities.Length; ++i)
{
DataRow entityRow = entityTable.NewRow();
entities[i].PopulateDataRow(this, entityRow);
entityTable.Rows.Add(entityRow);
}
using (SqlCommand deleteCommand = new SqlCommand(operationInfo.Procedure, this.dbConnection))
{
SqlParameter entityParameter = new SqlParameter()
{
ParameterName = operationInfo.ParameterName,
SqlDbType = SqlDbType.Structured,
Value = entityTable
};
deleteCommand.Parameters.Add(entityParameter);
deleteCommand.CommandType = CommandType.StoredProcedure;
bool connectionAlreadyOpen = (this.dbConnection.State == ConnectionState.Open);
if (!connectionAlreadyOpen)
{
this.dbConnection.Open();
}
try
{
deleteCommand.ExecuteNonQuery();
}
finally
{
if (!connectionAlreadyOpen)
{
this.dbConnection.Close();
}
}
}
}
}
public DataSet QueryNewLine(ISession session, DateTime start, DateTime end, string po, string barCode, int pageIndex, int pageSize, bool fetch, ref int count)
{
DbSession dbsession = session.DbSession as DbSession;
StringBuilder sql = new StringBuilder();
IDbCommand cmd = dbsession.CreateSqlStringCommand("");
sql.Append(@"
Select pol.Ord_Num As PONumber,pol.line_num As POLine
,sku.itm_barcode As BarCode,i.itm_code As ItemCode,i.itm_name As ItemName,sku.color_code As ColorCode,sku.size_code As SizeCode
,color.Name As ColorText
,pol.rcv_qty As Qty,Rownum As rowindex
,sku.sku_id as SKUID
From ord_pur_line pol
Inner Join ord_pur_head poh On pol.Ord_Num=poh.ord_num
Inner Join prd_item_sku sku On sku.sku_id=pol.sku_id
Inner Join prd_item i On i.itm_code=sku.itm_code
Left Join prd_item_color color On color.code=sku.color_code
Where poh.ven_id=:venId And poh.loc_code=:locCode And pol.rcv_qty>0
and poh.ord_status in (2,3) and poh.aprv_rslt=1 and pol.line_status in (1,3)");
dbsession.AddParameter(cmd, ":venId", DbTypeInfo.Int32(), this.VendorID);
dbsession.AddParameter(cmd, ":locCode", DbTypeInfo.AnsiString(8), this.LocationCode);
if (start > new DateTime(1900, 1, 1))
{
sql.Append(" And poh.create_time>=:startDate");
dbsession.AddParameter(cmd, ":startDate", DbTypeInfo.Date(), start);
}
if (end > new DateTime(1900, 1, 1))
{
sql.Append(" And poh.create_time<:endDate");
dbsession.AddParameter(cmd, ":endDate", DbTypeInfo.Date(), end.AddDays(1));
}
if (!string.IsNullOrEmpty(po) && po.Trim().Length > 0)
{
sql.Append(" And poh.ord_num=:po");
dbsession.AddParameter(cmd, ":po", DbTypeInfo.AnsiString(16), po.Trim().ToUpper());
}
if (!string.IsNullOrEmpty(barCode) && barCode.Trim().Length > 0)
{
sql.Append(" And sku.itm_barcode Like :barCode");
dbsession.AddParameter(cmd, ":barCode", DbTypeInfo.AnsiString(20), "%" + barCode.Trim().ToUpper() + "%");
}
if (fetch)
{
cmd.CommandText = "select count(*) from (" + sql.ToString() + ")t";
count = Magic.Framework.Utils.Cast.Int(dbsession.ExecuteScalar(cmd));
}
int startIndex = (pageIndex - 1) * pageSize + 1, endIndex = pageIndex * pageSize;
sql.Append(" order by poh.ord_num desc,pol.line_num)t1 where t1.rowindex>=").Append(startIndex)
.Append(" and t1.rowindex<=").Append(endIndex);
cmd.CommandText = "select * from(" + sql.ToString();
return(dbsession.ExecuteDataSet(cmd));
}
private static string BytesToLiteral(DbTypeInfo typeInfo, object value)
{
if (value == null || value == DBNull.Value)
{
return("NULL");
}
var bytes = (byte[])value;
return(@"E'\\x" + HexUtil.ByteArrayToHex(bytes) + "'");
}
public SOHead UpdateLineStatus(ISession session, int status)
{
DbSession dbsession = session.DbSession as DbSession;
IDbCommand cmd = dbsession.CreateSqlStringCommand("Update ord_lines Set status=:status Where order_id=:orderId");
dbsession.AddParameter(cmd, ":orderId", DbTypeInfo.Int32(), this.ID);
dbsession.AddParameter(cmd, ":status", DbTypeInfo.Int32(), status);
dbsession.ExecuteNonQuery(cmd);
return(this);
}
private void InitTableTypeInfo()
{
var msTypeReg = (MsSqlTypeRegistry)this.DbModel.Driver.TypeRegistry;
var userDefinedVendorType = msTypeReg.UserDefinedVendorDbType;
var customTableType = DbModel.CustomDbTypes.First(t => t.Name == MsSqlDbDriver.ArrayAsTableTypeName);
var tableTypeFullName = customTableType.FullName;
ArrayAsTableDbTypeInfo = new DbTypeInfo(userDefinedVendorType, tableTypeFullName, false, 0, 0, 0);
ArrayAsTableDbTypeInfo.PropertyToColumnConverter = MsSqlDbDriver.ConvertListToRecordList;
}
private static string NumToLiteralWithCast(DbTypeInfo typeInfo, object value)
{
if (value == null || value == DBNull.Value)
{
return("NULL");
}
var strValue = value.ToString();
return(string.Format("CAST({0} AS {1})", strValue, typeInfo.SqlTypeSpec));
}
/// <summary>
/// 查询某个用户某个DashPage下所有的Dashlet
/// </summary>
/// <param name="session"></param>
/// <param name="dashpageId"></param>
/// <param name="userId"></param>
/// <returns></returns>
public static IList <Dashlet> FindDashletByUserAndPage(ISession session, int userId, int dashpageId)
{
string oql = @"Select Dashlet from Dashlet l, UserDashlet ul where l.DashletId=ul.DashletId and ul.DashpageId=? and ul.UserId=?";
return(session.CreateObjectQuery(oql)
.Attach(typeof(Dashlet))
.Attach(typeof(UserDashlet))
.SetValue(0, dashpageId, DbTypeInfo.Int32())
.SetValue(1, userId, DbTypeInfo.Int32()).List <Dashlet>());
}
/// <summary>
/// 查询某个用户的DashPage
/// </summary>
/// <param name="session"></param>
/// <param name="userId"></param>
/// <returns></returns>
public static IList <DashPage> FindDashPageByUser(ISession session, int userId)
{
string oql = @"Select DashPage from DashPage p, UserDashpage up where p.DashpageId=up.DashpageId and up.UserId=?";
return(session.CreateObjectQuery(oql)
.Attach(typeof(DashPage))
.Attach(typeof(UserDashpage))
.SetValue(0, userId, DbTypeInfo.Int32())
.List <DashPage>());
}
/// <summary>
/// Find all users in the group
/// </summary>
/// <param name="session"></param>
/// <returns></returns>
public IList <User> FindUsersInGroup(ISession session)
{
string oql = @"select u.* from User u inner join UserToGroup ug on u.UserId=ug.UserId where ug.GroupId=?";
return
(session.CreateObjectQuery(oql)
.Attach(typeof(User))
.Attach(typeof(UserToGroup))
.SetValue(0, this.GroupId, DbTypeInfo.Int32())
.List <User>());
}
private static string ConvertList(object value, DbTypeInfo elemTypeInfo)
{
var strings = new List <string>();
var iEnum = value as IEnumerable;
foreach (var v in iEnum)
{
strings.Add(elemTypeInfo.ToLiteral(elemTypeInfo.PropertyToColumnConverter(v)));
}
return(string.Join(", ", strings));
}
// Special converters for literal presentations (used in batch mode). Default converter provides too much precision and it blows up
public static string DateTimeToLiteralMsSql(DbTypeInfo typeInfo, object value)
{
if (value == null || value == DBNull.Value)
{
return("NULL");
}
var dt = (DateTime)value;
var str = "'" + dt.ToString("yyyy-MM-ddTHH:mm:ss.FFF") + "'";
return(str);
}
public void PreAcceptEndIf(CIf cif, Object objstate)
{
if (objstate == null)
{
return;
}
DbTypeInfo state = (DbTypeInfo)objstate;
dbtype = state.oldtype;
}
public void AddLine(ISession session, string po, string poLine, string area, string section, decimal qty)
{
if (this.Status != POReturnStatus.New)
{
throw new Exception("采购退货单不是新建状态，无法添加明细");
}
POLine line = POLine.Retrieve(session, po, poLine);
if (line == null)
{
throw new Exception(po + "行" + poLine + "不存在");
}
if (line.ReceiveQty < qty)
{
throw new Exception(po + "行" + poLine + "退货数量" + qty.ToString() + "大于收货数量" + line.ReceiveQty.ToString());
}
StockDetail sto = StockDetail.Retrieve(session, line.SKUID, this.LocationCode, area, section);
if (sto == null)
{
throw new Exception(po + "行" + poLine + "库存明细(" + area + "," + section + ")不存在");
}
if (sto.StockQty < qty)
{
throw new Exception(po + "行" + poLine + "退货数量" + qty.ToString() + "大于库存量" + sto.StockQty.ToString());
}
DbSession dbsession = session.DbSession as DbSession;
IDbCommand cmd = dbsession.CreateSqlStringCommand("Select Sum(a.rtn_qty) From ord_pur_rtn_line a Where a.po_num=:po And a.po_line=:poline");
dbsession.AddParameter(cmd, ":po", DbTypeInfo.AnsiString(16), po);
dbsession.AddParameter(cmd, ":poline", DbTypeInfo.AnsiString(4), poLine);
decimal returnedQty = Cast.Decimal(dbsession.ExecuteScalar(cmd));
if (returnedQty + qty > line.ReceiveQty)
{
throw new Exception(po + "行" + poLine + "，已退货数量" + returnedQty.ToString() + "本次退货数量" + qty.ToString() + "大于收货数量" + line.ReceiveQty.ToString());
}
POReturnLine rtnLine = new POReturnLine();
rtnLine.OrderNumber = this.OrderNumber;
rtnLine.LineNumber = this.NextLineNumber();
rtnLine.PONumber = po;
rtnLine.POLine = poLine;
rtnLine.SKUID = line.SKUID;
rtnLine.Price = line.Price;
rtnLine.Quantity = qty;
rtnLine.TaxValue = line.TaxValue;
rtnLine.StockDetailID = sto.StockDetailID;
rtnLine.Create(session);
}
public void Put <TEntity>(TEntity[] entities)
where TEntity : IDbEntity, new()
{
const DbOperation operation = DbOperation.Put;
Type entityType = typeof(TEntity);
DbTypeInfo entityDbTypeInfo = this.GetDbTypeInfo <TEntity>();
if (!entityDbTypeInfo.DefaultEntity.DbOperations.ContainsKey(operation))
{
throw new InvalidOperationException(string.Format("Operation {0} is not supported by type {1}.", operation, entityType.Name));
}
DbOperationInfo operationInfo = entityDbTypeInfo.DefaultEntity.DbOperations[operation];
using (DataTable entityTable = entityDbTypeInfo.SchemaInfo[operationInfo.ParameterType].Clone())
{
for (int i = 0; i < entities.Length; ++i)
{
DataRow entityRow = entityTable.NewRow();
entityRow[DbConnection.entityIdFieldName] = i;
entities[i].PopulateDataRow(this, entityRow);
entityTable.Rows.Add(entityRow);
}
using (DataTable entityUpdateTable = new DataTable())
{
using (SqlCommand putCommand = new SqlCommand(operationInfo.Procedure, this.dbConnection))
{
SqlParameter entityParameter = new SqlParameter()
{
ParameterName = operationInfo.ParameterName,
SqlDbType = SqlDbType.Structured,
Value = entityTable
};
putCommand.Parameters.Add(entityParameter);
putCommand.CommandType = CommandType.StoredProcedure;
using (SqlDataAdapter putAdapter = new SqlDataAdapter(putCommand))
{
putAdapter.Fill(entityUpdateTable);
}
}
foreach (DataRow entityUpdateRow in entityUpdateTable.Rows)
{
int entityUpdateId = (int)entityUpdateRow[DbConnection.entityIdFieldName];
entities[entityUpdateId].PopulateEntity(this, entityUpdateRow);
}
}
}
}
/// <summary>
/// 获取可以分配给用户的用户组
/// </summary>
/// <param name="user"></param>
/// <returns></returns>
public IList <UserGroup> GetAssigningGroupsForUser(UserBase user)
{
IList <UserGroup> grps = new List <UserGroup>();
//string sql = @"select * from sys_usergroup g where g.group_id not in (select group_id from sys_user_to_group where user_id=@USER_ID)";
string oql = "select g.* from UserGroup g where g.GroupId not in(select GroupId from UserToGroup where UserId=@UserId)";
return(_session
.CreateObjectQuery(oql)
.Attach(typeof(UserGroup))
.Attach(typeof(UserToGroup))
.SetValue("@UserId", user.UserId, DbTypeInfo.Int32())
.List <UserGroup>());
}
public ColumnDefinition(string name, DbTypeInfo type)
{
#region Preconditions
if (name == null)
throw new ArgumentNullException(nameof(name));
if (type == null)
throw new ArgumentNullException(nameof(type));
#endregion
Name = name;
Type = type;
}
public static string MySqlStringToLiteral(DbTypeInfo typeDef, object value)
{
if (value == null || value == DBNull.Value)
{
return("NULL");
}
var str = (string)value;
//Escape backslash - this is MySql specific
str = str.Replace(@"\", @"\\");
if (!str.Contains('\'')) //fast case
{
return("'" + str + "'");
}
return("'" + str.Replace("'", "''") + "'");
}
/// <summary>
/// 获取指定用户所在的用户组
/// </summary>
/// <param name="user"></param>
/// <returns></returns>
public IList <IUserGroup> GetGroupsForUser(UserBase user)
{
string oql = @"select g.* from UserGroup g inner join UserToGroup ug on g.GroupId=ug.GroupId
where UserId=@UserId";
IList <IUserGroup> grps = new List <IUserGroup>();
ObjectQuery query = _session.CreateObjectQuery(oql).Attach(typeof(UserGroup)).Attach(typeof(UserToGroup));
query.SetValue("@UserId", user.UserId, DbTypeInfo.Int32());
IList <UserGroup> userGroups = query.List <UserGroup>();
foreach (UserGroup ug in userGroups)
{
grps.Add(ug);
}
return(grps);
}
private DbTypeInfo GetTypeInfoByDatabaseType(DbTypeInfo[] dbType, DbType databasedbType)
{
DbTypeInfo result = dbType.First();
List <string> mstypes = new List <string>();
switch (databasedbType)
{
case DbType.MySql:
mstypes = SqlSugar.MySqlDbBind.MappingTypesConst.Select(it => it.Key.ToLower()).ToList();
break;
case DbType.SqlServer:
mstypes = SqlSugar.SqlServerDbBind.MappingTypesConst.Select(it => it.Key.ToLower()).ToList();
break;
case DbType.Sqlite:
mstypes = SqlSugar.SqliteDbBind.MappingTypesConst.Select(it => it.Key.ToLower()).ToList();
break;
case DbType.Oracle:
mstypes = SqlSugar.OracleDbBind.MappingTypesConst.Select(it => it.Key.ToLower()).ToList();
break;
case DbType.PostgreSQL:
mstypes = SqlSugar.PostgreSQLDbBind.MappingTypesConst.Select(it => it.Key.ToLower()).ToList();
break;
case DbType.Dm:
mstypes = SqlSugar.DmDbBind.MappingTypesConst.Select(it => it.Key.ToLower()).ToList();
break;
case DbType.Kdbndp:
mstypes = SqlSugar.KdbndpDbBind.MappingTypesConst.Select(it => it.Key.ToLower()).ToList();
break;
default:
break;
}
result = dbType.FirstOrDefault(it => mstypes.Contains(it.Name.ToLower()));
if (result == null)
{
throw new Exception("WebFirst暂时不支持类型" + string.Join(",", dbType.Select(it => it.Name)));
}
return(result);
}
public static int CreatePeriodBalance(ISession session, INVPeriod openPeriod)
{
DbSession dbsession = session.DbSession as DbSession;
IDbCommand command = dbsession.CreateSqlStringCommand(@"
Insert Into inv_stock_balance(blnc_id, pd_id, sku_id, loc_code, area_code, sec_code, lot_num, stock_qty)
Select seq_inv_stock_balance.nextval,:pid,sku_id,loc_code,area_code,sec_code,lot_num,stock_qty
From inv_stock_detail
Where stock_qty<>0");
dbsession.AddParameter(command, ":pid", DbTypeInfo.Int32(), openPeriod.PeriodID);
dbsession.ExecuteNonQuery(command);
INVPeriod prev = INVPeriod.GetPeriod(session, openPeriod.StartingDate.AddDays(-1));
command = dbsession.CreateStoredProcCommand("f_rpt_inv_balance", new object[] { openPeriod.PeriodID, (prev == null ? 0 : prev.PeriodID) });
dbsession.ExecuteNonQuery(command);
return(1);
}
static SqlSchemaReader()
{
DbTypeInfo[] infoArray2 = new DbTypeInfo[] {
new DbTypeInfo("BigInt", typeof(long)), new DbTypeInfo("Binary", typeof(Binary), true), new DbTypeInfo("Bit", typeof(bool)), new DbTypeInfo("Char", typeof(string), true), new DbTypeInfo("DateTime", typeof(DateTime)), new DbTypeInfo("DateTime2", typeof(DateTime)), new DbTypeInfo("DateTimeOffset", typeof(DateTimeOffset)), new DbTypeInfo("Date", typeof(DateTime)), new DbTypeInfo("Time", typeof(TimeSpan)), new DbTypeInfo("Decimal", typeof(decimal), true, true), new DbTypeInfo("Float", typeof(double)), new DbTypeInfo("Image", typeof(Binary)), new DbTypeInfo("Int", typeof(int)), new DbTypeInfo("Money", typeof(decimal)), new DbTypeInfo("NChar", typeof(string), true), new DbTypeInfo("NText", typeof(string)),
new DbTypeInfo("Numeric", typeof(decimal), true, true), new DbTypeInfo("NVarchar", typeof(string), true), new DbTypeInfo("Real", typeof(float)), new DbTypeInfo("RowVersion", typeof(Binary)), new DbTypeInfo("SmallDateTime", typeof(DateTime)), new DbTypeInfo("SmallInt", typeof(short)), new DbTypeInfo("SmallMoney", typeof(decimal)), new DbTypeInfo("Sql_Variant", typeof(object)), new DbTypeInfo("Text", typeof(string)), new DbTypeInfo("Timestamp", typeof(Binary)), new DbTypeInfo("TinyInt", typeof(byte)), new DbTypeInfo("UniqueIdentifier", typeof(Guid)), new DbTypeInfo("VarBinary", typeof(Binary), true), new DbTypeInfo("VarChar", typeof(string), true), new DbTypeInfo("Xml", typeof(XElement))
};
foreach (DbTypeInfo info in infoArray2)
{
ByName.Add(info.Name, info);
}
try
{
_geometryType = Type.GetType("Microsoft.SqlServer.Types.SqlGeometry, Microsoft.SqlServer.Types, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91");
if (_geometryType != null)
{
ByName.Add("Geometry", new DbTypeInfo("Geometry", _geometryType));
}
}
catch
{
}
try
{
_geographyType = Type.GetType("Microsoft.SqlServer.Types.SqlGeography, Microsoft.SqlServer.Types, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91");
if (_geographyType != null)
{
ByName.Add("Geography", new DbTypeInfo("Geography", _geographyType));
}
}
catch
{
}
try
{
_hierarchyIdType = Type.GetType("Microsoft.SqlServer.Types.SqlHierarchyId, Microsoft.SqlServer.Types, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91");
if (_hierarchyIdType != null)
{
ByName.Add("HierarchyId", new DbTypeInfo("HierarchyId", _hierarchyIdType));
}
}
catch
{
}
}
/// <summary>
/// 获取可以分配给用户组的用户
/// </summary>
/// <param name="groupId"></param>
/// <param name="userNameToMatch"></param>
/// <returns></returns>
public DataTable GetAssigningUserForGroup(int groupId, string userNameToMatch)
{
//string sql = @"select u.User_Id as Id, u.User_Name as UserName,u.Full_Name as FullName,u.Email as Email from SYS_User u where u.user_id not in (select user_id from SYS_User_To_Group where Group_ID=@GROUP_ID) AND u.User_Name like @NameMatch";
string oql = @"select u.UserId as UserId, u.UserName as UserName,u.FullName as FullName,u.Email as Email from User u where u.UserId not in (select UserId from UserToGroup where GroupId=@GroupId) ";
ObjectQuery query = _session.CreateObjectQuery(oql).Attach(typeof(User)).Attach(typeof(UserToGroup));
query.SetValue("@GroupId", groupId, DbTypeInfo.Int32());
string nameMatch = "%";
if (!string.IsNullOrEmpty(userNameToMatch))
{
nameMatch = userNameToMatch + "%";
query.And(Exp.Like("u.UserName", nameMatch));
query.And(Exp.Like("u.FullName", nameMatch));
}
return(query.DataSet().Tables[0]);
}
public TEntity[] Get <TEntity>(params DbParameter[] parameters)
where TEntity : IDbEntity, new()
{
const DbOperation operation = DbOperation.Get;
Type entityType = typeof(TEntity);
DbTypeInfo entityDbTypeInfo = this.GetDbTypeInfo <TEntity>();
if (!entityDbTypeInfo.DefaultEntity.DbOperations.ContainsKey(operation))
{
throw new InvalidOperationException(string.Format("Operation {0} is not supported by type {1}.", operation, entityType.Name));
}
DbOperationInfo operationInfo = entityDbTypeInfo.DefaultEntity.DbOperations[operation];
using (DataTable entityTable = new DataTable())
{
using (SqlCommand getCommand = new SqlCommand(operationInfo.Procedure, this.dbConnection))
{
getCommand.CommandType = CommandType.StoredProcedure;
foreach (DbParameter parameter in parameters)
{
getCommand.Parameters.AddWithValue(parameter.Name, parameter.Value);
}
using (SqlDataAdapter getAdapter = new SqlDataAdapter(getCommand))
{
getAdapter.Fill(entityTable);
}
}
TEntity[] results = new TEntity[entityTable.Rows.Count];
for (int i = 0; i < results.Length; ++i)
{
TEntity entity = new TEntity();
entity.PopulateEntity(this, entityTable.Rows[i]);
results[i] = entity;
}
return(results);
}
}
/// <summary>
/// 匹配出最符合的类型
/// </summary>
/// <param name="cstype"></param>
/// <param name="type"></param>
/// <param name="columnInfo"></param>
/// <param name="dbtype"></param>
/// <returns></returns>
private decimal GetSort(DbTypeInfo cstype, CodeType type, DbColumnInfo columnInfo, DbType dbtype)
{
decimal result = 0;
if (columnInfo.DataType.Equals(cstype.Name, StringComparison.OrdinalIgnoreCase))
{
result = result + 10000;
}
else
{
result = result - 30000;
}
if (columnInfo.Length == Convert.ToInt32(cstype.Length))
{
result = result + 5000;
}
else if (columnInfo.Length > Convert.ToInt32(cstype.Length))
{
result = result + (columnInfo.Length - Convert.ToInt32(cstype.Length)) * -3;
}
else
{
result = result - 500;
}
if (columnInfo.DecimalDigits == Convert.ToInt32(cstype.DecimalDigits))
{
result = result + 5000;
}
else if (columnInfo.DecimalDigits > Convert.ToInt32(cstype.DecimalDigits))
{
result = result + (columnInfo.DecimalDigits - Convert.ToInt32(cstype.DecimalDigits)) * -3;
}
else
{
result = result - 500;
}
if (type.Name.Contains("nString") && columnInfo.DataType == "varchar")
{
result = result - 500;
}
return(result);
}
/// <summary>
/// Registers the type.
/// </summary>
/// <param name="type">The type.</param>
/// <param name="capacity">The capacity.</param>
/// <param name="sqlText">The SQL text.</param>
protected void RegisterType(DbType type, int capacity, string sqlText)
{
lock (lockTypeMap)
{
DbTypeInfo tinfo;
if (!typeMap.TryGetValue(sqlText, out tinfo))
{
tinfo = new DbTypeInfo(type, sqlText)
{
Length = capacity
};
typeMap.Add(sqlText, tinfo);
}
else
{
tinfo.Length = capacity;
tinfo.DbType = type;
}
}
}
public bool TryGetDbType(Type type, out DbTypeInfo info)
=> builtins.TryGetValue(type, out info);
public Object PreAcceptThen(CIf cif, Object objstate)
{
int newtype = 0;
CExpression exp = cif.Condition;
bool not = exp is CNot;
if (not)
{
exp = ((CNot)exp).Operand;
if (exp is CParenExpression)
exp = ((CParenExpression)exp).InnerExpression;
}
if (exp is CLogic && ((CLogic)exp).Operation.Value == "or")
{
CExpression lhs = ((CLogic)exp).Left;
if (lhs is CAccess && ((CAccess)lhs).IsRootAccess &&
((CAccess)lhs).ReferenceToken.Value == "g_fmssql")
newtype |= DbMsSql;
exp = ((CLogic)exp).Right;
}
if (exp is CAccess)
{
CAccess acc = (CAccess)exp;
if (acc.IsRootAccess)
{
if (acc.ReferenceToken.Value == "g_fmssql")
newtype |= DbMsSql;
if (acc.ReferenceToken.Value == "g_fmysql")
newtype |= DbMySql;
}
}
if (newtype == 0)
return null;
if (not)
newtype = ~newtype;
DbTypeInfo state;
if (objstate == null)
{
state = new DbTypeInfo();
state.oldtype = dbtype;
}
else
state = (DbTypeInfo)objstate;
state.currenttype = newtype;
state.elsetype &= ~newtype;
dbtype = newtype;
return state;
}
