public void InitDbContext()
{
DbFactory = new DatabaseFactory();
DatabaseHelper.CreatedDatabase(new DVSContext(), 8);
DatabaseHelper.CreatedDatabase(new TaskQueueContext(), 5);
}
public void SetUp()
{
var types = new[] { typeof(User), typeof(ExtraUserInfo), typeof(UserWithExtraInfo), typeof(Usersss), typeof(House), typeof(Supervisor) };
var dbFactory = new DatabaseFactory();
var config = FluentMappingConfiguration.Scan(s =>
{
s.Assembly(typeof(User).GetTypeInfo().Assembly);
s.IncludeTypes(types.Contains);
s.PrimaryKeysNamed(y => ToLowerIf(y.Name + "Id", false));
s.TablesNamed(y => ToLowerIf(Inflector.MakePlural(y.Name), false));
s.Columns.Named(x => ToLowerIf(x.Name, false));
s.Columns.ForceDateTimesToUtcWhere(x => x.GetMemberInfoType() == typeof(DateTime) || x.GetMemberInfoType() == typeof(DateTime?));
s.Columns.ResultWhere(y => ColumnInfo.FromMemberInfo(y).ResultColumn);
s.OverrideMappingsWith(new FluentMappingOverrides());
s.OverrideMappingsWith(new OneToManyMappings());
});
dbFactory.Config().WithFluentConfig(config);
var configuration = new ConfigurationBuilder()
.AddJsonFile("config.json")
.Build();
var testDBType = Convert.ToInt32(configuration.GetSection("TestDBType").Value);
switch (testDBType)
{
case 1: // SQLite In-Memory
TestDatabase = new InMemoryDatabase();
Database = dbFactory.Build(new Database(TestDatabase.Connection));
break;
case 2: // SQL Local DB
case 3: // SQL Server
var dataSource = configuration.GetSection("TestDbDataSource").Value;
TestDatabase = new SQLLocalDatabase(dataSource);
Database = dbFactory.Build(new Database(TestDatabase.Connection, new SqlServer2008DatabaseType()));
break;
case 4: // SQL CE
case 5: // MySQL
case 6: // Oracle
case 7: // Postgres
Assert.Fail("Database platform not supported for unit testing");
return;
#if !DNXCORE50
case 8: // Firebird
TestDatabase = new FirebirdDatabase();
var db = new Database(TestDatabase.Connection, new FirebirdDatabaseType());
db.Mappers.Insert(0, new FirebirdDefaultMapper());
Database = dbFactory.Build(db);
break;
#endif
default:
Assert.Fail("Unknown database platform specified");
return;
}
InsertData();
}
static void Main(string[] args)
{
IDatabaseFactory factory = new DatabaseFactory()
.Connection("TestDb")
.Table<Category>()
.Key(x => x.CategoryId, DatabaseGeneratedOption.Identity)
.Configure()
.Table<Product>()
.Configure();
IDatabase data = factory.CreateDatabase();
Category category1 = new Category { Name = "Category 1" };
Category category2 = new Category { Name = "Category 2" };
Product product1 = new Product { ProductId = "PROD123", Name = "Product 123", Description = "The first Product", Price = 19.99M };
Product product2 = new Product { ProductId = "PROD234", Name = "Product 234", Description = "The second Product", Price = 24.99M };
Product product3 = new Product { ProductId = "PROD345", Name = "Product 345", Description = "The third Product", Price = 29.99M };
data.BeginTransaction();
try
{
data.Insert(category1);
data.Insert(category2);
product1.CategoryId = category1.CategoryId;
product2.CategoryId = category2.CategoryId;
product3.CategoryId = category2.CategoryId;
data.Insert(product1);
data.Insert(product2);
data.Insert(product3);
data.CommitTransaction();
Console.WriteLine("OK");
}
catch(Exception ex)
{
data.RollbackTransaction();
Console.WriteLine(ex.Message);
}
IList<Product> products = data.Query<Product>("SELECT ProductId, CategoryId, Name, Description, Price FROM Product WHERE CategoryId = @CategoryId")
.Parameter("@CategoryId", category2.CategoryId)
.Execute();
foreach (Product product in products)
{
Console.WriteLine(product.ProductId);
}
Console.WriteLine();
Product prodX = data.Select<Product>("PROD123");
Console.WriteLine("{0}\t{1}", prodX.ProductId, prodX.Name);
Console.ReadKey();
}
public void ClearMinStartDateForAssignmentAppraisersForOrder(int appraiserOrderId)
{
using (var dbFactory = new DatabaseFactory())
{
var repository = new TaskQueueRepository(dbFactory);
repository.ClearMinStartDateForAssignmentAppraisersForOrder(appraiserOrderId);
}
}
public void TestInitialize()
{
this.InitDbContext();
var dbFactory = new DatabaseFactory();
_repository = new TaskQueueRepository(dbFactory);
ClearTaskQueueContext();
}
public UnitOfWork(DatabaseFactory dbFactory)
{
this.dbFactory = dbFactory;
}
public void Setup()
{
var dbfactory = new DatabaseFactory();
dbfactory
.Config()
.UsingDatabase(() => new Database(""))
.WithFluentConfig(FluentMappingConfiguration.Configure(new MyMappings()));
_database = dbfactory.GetDatabase();
}
public DummyConfig(DatabaseFactory databaseFactory)
{
this.databaseFactory = databaseFactory;
connectionStrings = new Dictionary<string, string>(StringComparer.CurrentCultureIgnoreCase)
{
{ "Mine", "Mine Connection String" },
{ "ActivityLogging", "Activity Logging Connection String"},
{ "VerticalGoods", "Vertical Goods Connection String"},
{ "Foo", "Foo Connection String"},
{ "Bar", "Bar Connection String"}
};
}
public void SetUp()
{
var types = new[] { typeof(User), typeof(ExtraUserInfo), typeof(Usersss), typeof(House), typeof(Supervisor) };
var dbFactory = new DatabaseFactory();
dbFactory.Config().WithFluentConfig(
FluentMappingConfiguration.Scan(s =>
{
s.Assembly(typeof (User).Assembly);
s.IncludeTypes(types.Contains);
s.WithSmartConventions();
s.Columns.ResultWhere(y => ColumnInfo.FromMemberInfo(y).IgnoreColumn);
s.OverrideMappingsWith(new FluentMappingOverrides());
})
);
var testDBType = Convert.ToInt32(ConfigurationManager.AppSettings["TestDBType"]);
switch (testDBType)
{
case 1: // SQLite In-Memory
TestDatabase = new InMemoryDatabase();
Database = dbFactory.Build(new Database(TestDatabase.Connection));
break;
case 2: // SQL Local DB
case 3: // SQL Server
TestDatabase = new SQLLocalDatabase();
Database = dbFactory.Build(new Database(TestDatabase.Connection, new SqlServer2008DatabaseType()));
break;
case 4: // SQL CE
case 5: // MySQL
case 6: // Oracle
case 7: // Postgres
Assert.Fail("Database platform not supported for unit testing");
return;
case 8: // Firebird
TestDatabase = new FirebirdDatabase();
var db = new Database(TestDatabase.Connection, new FirebirdDatabaseType());
db.Mapper = new FirebirdDefaultMapper();
Database = dbFactory.Build(db);
break;
default:
Assert.Fail("Unknown database platform specified");
return;
}
InsertData();
}
protected DatabaseTest()
{
ConnectionStringSettings connectionStringSettings = ConfigurationManager.ConnectionStrings["BolfTracker"];
var providerName = connectionStringSettings.ProviderName;
var connectionString = connectionStringSettings.ConnectionString;
var providerFactory = DbProviderFactories.GetFactory(providerName);
DatabaseFactory = new DatabaseFactory(providerFactory, connectionString);
QueryFactory = new QueryFactory(true, true);
UnitOfWork = new UnitOfWork(DatabaseFactory);
}
public void Run()
{
try
{
if (Task.TaskStatus != TaskStatus.Error)
{
if (Task.TaskType.MaxPoolingTask.HasValue && Task.PoolingTaskNumber >= Task.TaskType.MaxPoolingTask)
{
Task.TaskStatus = TaskStatus.Error;
Task.ErrorMessage = "Max pooling task";
SendMaxPoolingTaskNotificationInternal();
}
else
{
LaunchTask();
if (Task.TaskStatus != TaskStatus.Error)
Task.TaskStatus = TaskStatus.Completed;
}
EndTask();
}
}
catch (Exception ex)
{
if (!ex.Data.Contains("TaskId"))
ex.Data.Add("TaskId", Task.Id);
Task.ErrorMessage = Logger.WriteError(ex).ToString();
Task.TaskStatus = TaskStatus.Error;
CreateResendOnErrorTask();
}
finally
{
try
{
using (var dbFactory = new DatabaseFactory())
{
var repository = new TaskQueueRepository(dbFactory);
repository.SetTaskEnd(Task.Id, Task.TaskStatus, Task.Result, Task.ErrorMessage);
}
}
catch (Exception ex)
{
Logger.WriteError(ex);
}
}
}
public void SetUp()
{
var types = new[] { typeof(User), typeof(ExtraUserInfo) };
var dbFactory = new DatabaseFactory();
dbFactory.Config().WithFluentConfig(
FluentMappingConfiguration.Scan(s =>
{
s.Assembly(typeof (User).Assembly);
s.IncludeTypes(types.Contains);
s.WithSmartConventions();
s.OverrideMappingsWith(new FluentMappingOverrides());
})
);
var testDBType = Convert.ToInt32(ConfigurationManager.AppSettings["TestDBType"]);
switch (testDBType)
{
case 1: // SQLite In-Memory
TestDatabase = new InMemoryDatabase();
Database = dbFactory.Build(new Database(TestDatabase.Connection));
break;
case 2: // SQL Local DB
case 3: // SQL Server
TestDatabase = new SQLLocalDatabase();
Database = dbFactory.Build(new Database(TestDatabase.Connection, new SqlServer2012DatabaseType()));
break;
case 4:
case 5:
case 6:
Assert.Fail("Database platform not supported for unit testing");
return;
default:
Assert.Fail("Unknown database platform specified");
return;
}
InsertData();
}
public void SetUp()
{
var dbFactory = new DatabaseFactory();
var personColumnProvider = new PersonColumnProvider();
var columnProvider = new PostColumnProvider();
var personMapper = new PersonMapper(personColumnProvider);
var mapper = new PostMapper(columnProvider, personMapper);
var helper = new DataParamHelper();
var paramRepository = new PostParamRepository(columnProvider, helper);
var collectionRepository = new PostParamCollectionRepository(paramRepository);
var personParamRepository = new PersonParamRepository(personColumnProvider, helper);
var personCollectionRepository = new PersonParamCollectionRepository(personParamRepository);
_repository = new PostRepository(dbFactory, mapper, collectionRepository);
_personRepository = new PersonRepository(dbFactory, personMapper, personCollectionRepository);
}
public static void AddNew(TaskTypeEnum taskType, Dictionary<TaskParamsKey, string> parameters = null, DateTime? minStartDate = null, int? parentTaskId = null)
{
Task task = new Task();
task.CreateDate = DateTime.Now;
task.TaskStatus = TaskStatus.Pending;
task.TaskTypeEnum = taskType;
task.MinStartDate = minStartDate;
task.ParentTaskId = parentTaskId;
if (parameters != null)
{
task.TaskParams = new List<TaskParam>();
foreach (var item in parameters)
{
task.TaskParams.Add(new TaskParam(item.Key, item.Value));
}
}
using (var dbFactory = new DatabaseFactory())
{
var repository = new TaskQueueRepository(dbFactory);
repository.Add(task);
}
}
public void ProcessQueue()
{
try
{
lock (_lockObj)
{
using (var dbFactory = new DatabaseFactory())
{
var repository = new TaskQueueRepository(dbFactory);
var tasks = repository.GetTasksToRun();
foreach (var task in tasks)
{
var baseTask = _taskFactory.CreateTask(task.TaskTypeEnum);
baseTask.Load(task);
System.Threading.Tasks.Task.Factory.StartNew(baseTask.Run);
}
}
}
}
catch (Exception ex)
{
Logger.WriteError(ex);
}
}
public override void Cleanup()
{
DatabaseFactory.ClearDatabaseProviderFactory();
base.Cleanup();
}
/// <summary>
/// 一个资金id多次插入
/// </summary>
private static void Test1T()
{
MemoryDataManager.Start();
var table = MemoryDataManager.XHCapitalMemoryList.GetByCapitalAccountLogo(2);
var table1 = MemoryDataManager.XHHoldMemoryList.GetByAccountHoldLogoId(4);
Console.WriteLine("开始Test1T");
XH_CapitalAccountTable_DeltaInfo delta = new XH_CapitalAccountTable_DeltaInfo();
delta.AvailableCapitalDelta = 1;
XH_AccountHoldTableInfo_Delta holdDelta = new XH_AccountHoldTableInfo_Delta();
var data = table1.Data;
holdDelta.Data = data;
holdDelta.AccountHoldLogoId = data.AccountHoldLogoId;
holdDelta.AvailableAmountDelta = 1;
holdDelta.FreezeAmountDelta = 1;
Database database = DatabaseFactory.CreateDatabase();
using (DbConnection connection = database.CreateConnection())
{
connection.Open();
DbTransaction transaction = connection.BeginTransaction();
bool isSuccess = false;
try
{
//table.AddDeltaToDB(delta, database, transaction);
//MakeException();
//table.AddDeltaToDB(delta, database, transaction);
//table.AddDeltaToDB(delta, database, transaction);
//table1.AddDeltaToDB(holdDelta,database,transaction);
transaction.Commit();
isSuccess = true;
}
catch (Exception ex)
{
transaction.Rollback();
isSuccess = false;
Console.WriteLine("Exception:" + ex.Message);
}
if (isSuccess)
{
//table.AddDeltaToMemory(delta);
//table.AddDeltaToMemory(delta);
//table.AddDeltaToMemory(delta);
//table1.AddDeltaToMemory(holdDelta);
}
}
Console.WriteLine(table.Data.AvailableCapital);
Console.WriteLine(table1.Data.AvailableAmount);
ExitConsole();
MemoryDataManager.End();
}
/// <summary>
/// 获取公司当前月份的应付款金额
/// </summary>
/// <param name="year"></param>
/// <param name="month"></param>
/// <returns></returns>
public IList <SupplierPaymentsRecordInfo> SelectCurrentMonthPaymentsRecords(int year, int month)
{
const string SQL = @"DECLARE @CompanyID UNIQUEIDENTIFIER --往来单位ID
,@PaymentDays INT --账期
,@time1 date --当前开始日期
,@time2 date --账期日期(根据账期计算账期日期)(结束日期)
,@time3 date --账期日期(根据账期计算账期日期)(开始日期)
,@TheMonthNum int --月份循环值
DECLARE @TempFilialeRecord TABLE(
FilialeId UNIQUEIDENTIFIER null,
TotalAmount decimal(18,4) null,
TotalNoPayed decimal(18,4) null
)
SET @time1= '{0}'--参数2 开始日期
SET @time2= dateadd(mm,0,DATEADD(MS,-3,DATEADD(MM, DATEDIFF(MM,0,@time1)+1, 0)))
--------------------------------无账期应付款总额-------------------------------------------
INSERT INTO @TempFilialeRecord([FilialeId],[TotalAmount],[TotalNoPayed])
SELECT FilialeId,
ISNULL(sum(case when LinkTradeType = 1 and AccountReceivable< 0 then AccountReceivable else 0 end),0) AS TotalAmount ,
ISNULL(sum(case when IsChecked =0 then AccountReceivable else 0 end),0) AS TotalNoPayed
FROM lmShop_Reckoning AS R with(nolock)
INNER JOIN lmshop_CompanyCussent CC ON R.ThirdCompanyID=CC.CompanyID AND CC.CompanyType=1 AND CC.PaymentDays=0
where R.DateCreated between @time1 and @time2
and AuditingState=1 AND R.[State]<>2 and LinkTradeType in (1,2)
and (
LinkTradeCode like 'RK%' or LinkTradeCode like 'SL%' or LinkTradeCode like 'SO%'
or LinkTradeCode like 'SI%' or LinkTradeCode like 'TSI%' or LinkTradeCode like 'TSO%'
or LinkTradeCode like 'TR%') and TradeCode not like 'GT%'
GROUP BY FilialeId,IsOut
--------------------------------有账期应付款总额-------------------------------------------
--循环变量
DECLARE @W_Count INT, @W_Num INT
----临时往来单位表
SELECT ROW_NUMBER() OVER(ORDER BY CompanyID) AS RowID,CompanyId,PaymentDays INTO #TempCompanyCussent
FROM lmShop_CompanyCussent WHERE CompanyType=1 AND PaymentDays>0
--循环变量赋值
SET @W_Num =1
SELECT @W_Count=COUNT(0) FROM #TempCompanyCussent
--循环往来单位
WHILE(@W_Num <= @W_Count)
BEGIN
--获取往来单位
SELECT @CompanyID = CompanyId,@PaymentDays=PaymentDays FROM #TempCompanyCussent WHERE RowID = @w_num
SET @time3= dateadd(mm,-@PaymentDays,'{0}')--参数2 开始日期
SET @time2= dateadd(mm,-@PaymentDays,DATEADD(MS,-3,DATEADD(MM, DATEDIFF(MM,0,@time1)+1, 0)))
INSERT INTO @TempFilialeRecord([FilialeId],[TotalAmount],[TotalNoPayed])
SELECT FilialeId,
ISNULL(sum(case when LinkTradeType = 1 and AccountReceivable<0 then AccountReceivable else 0 end),0) AS TotalAmount ,
ISNULL(sum(case when IsChecked =0 then AccountReceivable else 0 end),0) AS TotalNoPayed FROM lmshop_Reckoning as R with(nolock)
where R.[ThirdCompanyID]=@CompanyID AND R.DateCreated between @time3 and @time2
and AuditingState=1 AND R.[State]<>2 and LinkTradeType in (1,2)
and (
LinkTradeCode like 'RK%' or LinkTradeCode like 'SL%' or LinkTradeCode like 'SO%'
or LinkTradeCode like 'SI%' or LinkTradeCode like 'TSI%' or LinkTradeCode like 'TSO%'
or LinkTradeCode like 'TR%') and TradeCode not like 'GT%'
group by FilialeId,IsOut
SET @W_Num +=1 --循环累加
END
DROP TABLE #TempCompanyCussent
SELECT FilialeId,@time1 AS DayTime,SUM(TotalAmount) AS TotalAmount,SUM(TotalNoPayed) AS TotalNoPayed
FROM @TempFilialeRecord GROUP BY FilialeId ORDER BY TotalAmount DESC ";
var startTime = new DateTime(year, month, 1);
var newSQL = string.Format(SQL, startTime);
using (var db = DatabaseFactory.Create())
{
return(db.Select <SupplierPaymentsRecordInfo>(true, newSQL).ToList());
}
}
/// <summary>
/// Commit
/// </summary>
/// <returns></returns>
public TransactionResult Commit()
{
TransactionResult result = null;
Database db = DatabaseFactory.CreateDatabase(_myConnection.DatabaseName);
using (DbConnection connection = db.CreateConnection())
{
connection.Open();
DbTransaction transaction = connection.BeginTransaction();
try
{
switch (ScreenMode)
{
case ScreenMode.Add:
//Adding or Editing EmployeeFamily
result = AddEditEmployeeFamily(db, transaction);
if (result.Status == TransactionStatus.Failure)
{
transaction.Rollback();
return(result);
}
break;
case ScreenMode.Edit:
break;
case ScreenMode.Delete:
result = DeleteEmployeeFamily(db, transaction);
if (result.Status == TransactionStatus.Failure)
{
transaction.Rollback();
return(result);
}
break;
case ScreenMode.View:
break;
}
transaction.Commit();
return(result);
}
catch (Exception ex)
{
transaction.Rollback();
if (ScreenMode == ScreenMode.Add)
{
ErrorLog.LogErrorMessageToDB("", "EmployeeFamilyDL.cs", "Commit For Add", ex.Message.ToString(), _myConnection);
System.Web.HttpContext.Current.Response.Redirect("~/OrderErrorPage.aspx");
return(new TransactionResult(TransactionStatus.Failure, "Failure Adding"));
}
if (ScreenMode == ScreenMode.Edit)
{
ErrorLog.LogErrorMessageToDB("", "EmployeeFamilyDL.cs", "Commit For Edit", ex.Message.ToString(), _myConnection);
System.Web.HttpContext.Current.Response.Redirect("~/OrderErrorPage.aspx");
return(new TransactionResult(TransactionStatus.Failure, "Failure Updating"));
}
if (ScreenMode == ScreenMode.Delete)
{
ErrorLog.LogErrorMessageToDB("", "EmployeeFamilyDL.cs", "Commit For Delete", ex.Message.ToString(), _myConnection);
System.Web.HttpContext.Current.Response.Redirect("~/OrderErrorPage.aspx");
return(new TransactionResult(TransactionStatus.Failure, "Failure Deleting"));
}
}
}
return(null);
}
public List <System.Text.StringBuilder> GetCollectionsAnyJSON(Query query)
{
List <System.Text.StringBuilder> obuilder = new List <System.Text.StringBuilder>();
System.Text.StringBuilder builder;
Database db;
if (query.connection == null || query.connection == string.Empty)
{
db = DatabaseFactory.CreateDatabase("Default");
}
else
{
db = DatabaseFactory.CreateDatabase(query.connection);
}
using (DbConnection connection = db.CreateConnection())
{
connection.Open();
DbCommand dbCommand = db.GetStoredProcCommand(query.method, query.input.ToArray());
if (query.Timeout != 0)
{
dbCommand.CommandTimeout = query.Timeout;
}
int columns;
ArrayList namecolumns;
using (IDataReader dr = db.ExecuteReader(dbCommand))
{
columns = dr.FieldCount;
namecolumns = new ArrayList();
builder = new System.Text.StringBuilder();
for (int idx = 0; idx < columns; idx++)
{
namecolumns.Add(dr.GetName(idx));
}
builder.Append("[");
while (dr.Read())
{
builder.Append("{");
for (int idx = 0; idx < columns - 1; idx++)
{
builder.Append(string.Format("\"{0}\":{1},", namecolumns[idx], query.GetFormatType(dr[idx])));
}
builder.Append(string.Format("\"{0}\":{1}", namecolumns[columns - 1], query.GetFormatType(dr[columns - 1])));
builder.Append("},");
}
builder.Append("]");
obuilder.Add(builder);
while (dr.NextResult())
{
columns = dr.FieldCount;
namecolumns = new ArrayList();
builder = new System.Text.StringBuilder();
for (int idx = 0; idx < columns; idx++)
{
namecolumns.Add(dr.GetName(idx));
}
builder.Append("[");
while (dr.Read())
{
builder.Append("{");
for (int idx = 0; idx < columns - 1; idx++)
{
builder.Append(string.Format("\"{0}\":{1},", namecolumns[idx], query.GetFormatType(dr[idx])));
}
builder.Append(string.Format("\"{0}\":{1}", namecolumns[columns - 1], query.GetFormatType(dr[columns - 1])));
builder.Append("},");
}
builder.Append("]");
obuilder.Add(builder);
}
}
connection.Close();
}
return(obuilder);
}
public static List <Order> GetOrder(DateTime start, DateTime end, int status)
{
try
{
Dictionary <string, object> listKV = new Dictionary <string, object>();
listKV.Add("@date_from", start);
listKV.Add("@date_to", end);
listKV.Add("@status", status);
List <Order> listOrderReturn = new List <Order>();
SqlDataReader reader = DatabaseFactory.DataReader(System.Data.CommandType.StoredProcedure, "SearchOrderByStatus", listKV, DatabaseFactory.GetConnection(DatabaseFactory.SQL_TYPE_MSSQL).GetConnection());
while (reader.Read())
{
Order order = Order.GetOrder(reader.GetInt32(0));
listOrderReturn.Add(order);
}
return(listOrderReturn);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Update
/// </summary>
/// <param name="info">info</param>
public int Update(PlanPullOrderInfo info)
{
Database db = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = db.GetSqlStringCommand(TT_MPM_PLAN_PULL_ORDER_UPDATE);
db.AddInParameter(dbCommand, "@ID", DbType.Int64, info.Id);
db.AddInParameter(dbCommand, "@FID", DbType.Guid, info.Fid);
db.AddInParameter(dbCommand, "@ORDER_CODE", DbType.String, info.OrderCode);
db.AddInParameter(dbCommand, "@PART_BOX_CODE", DbType.String, info.PartBoxCode);
db.AddInParameter(dbCommand, "@PART_BOX_NAME", DbType.String, info.PartBoxName);
db.AddInParameter(dbCommand, "@PLANT", DbType.String, info.Plant);
db.AddInParameter(dbCommand, "@WORKSHOP", DbType.String, info.Workshop);
db.AddInParameter(dbCommand, "@ASSEMBLY_LINE", DbType.String, info.AssemblyLine);
db.AddInParameter(dbCommand, "@SUPPLIER_NUM", DbType.String, info.SupplierNum);
db.AddInParameter(dbCommand, "@SOURCE_ZONE_NO", DbType.String, info.SourceZoneNo);
db.AddInParameter(dbCommand, "@SOURCE_WM_NO", DbType.String, info.SourceWmNo);
db.AddInParameter(dbCommand, "@TARGET_ZONE_NO", DbType.String, info.TargetZoneNo);
db.AddInParameter(dbCommand, "@TARGET_WM_NO", DbType.String, info.TargetWmNo);
db.AddInParameter(dbCommand, "@PUBLISH_TIME", DbType.DateTime, info.PublishTime);
db.AddInParameter(dbCommand, "@ORDER_TYPE", DbType.Int32, info.OrderType);
db.AddInParameter(dbCommand, "@DOCK", DbType.String, info.Dock);
db.AddInParameter(dbCommand, "@UNLOADING_TIME", DbType.Int32, info.UnloadingTime);
db.AddInParameter(dbCommand, "@EXPECTED_ARRIVAL_TIME", DbType.DateTime, info.ExpectedArrivalTime);
db.AddInParameter(dbCommand, "@SUGGEST_DELIVERY_TIME", DbType.DateTime, info.SuggestDeliveryTime);
db.AddInParameter(dbCommand, "@ACTUAL_ARRIVAL_TIME", DbType.DateTime, info.ActualArrivalTime);
db.AddInParameter(dbCommand, "@WINDOW_TIMES", DbType.String, info.WindowTimes);
db.AddInParameter(dbCommand, "@ORDER_STATUS", DbType.Int32, info.OrderStatus);
db.AddInParameter(dbCommand, "@COMMENTS", DbType.String, info.Comments);
db.AddInParameter(dbCommand, "@PRINT_TIMES", DbType.Int32, info.PrintTimes);
db.AddInParameter(dbCommand, "@LAST_PRINT_DATE", DbType.DateTime, info.LastPrintDate);
db.AddInParameter(dbCommand, "@LAST_PRINT_USER", DbType.String, info.LastPrintUser);
db.AddInParameter(dbCommand, "@ASN_FLAG", DbType.Boolean, info.AsnFlag);
db.AddInParameter(dbCommand, "@TIME_ZONE", DbType.String, info.TimeZone);
db.AddInParameter(dbCommand, "@KEEPER", DbType.String, info.Keeper);
db.AddInParameter(dbCommand, "@DELIVERY_ADD", DbType.String, info.DeliveryAdd);
db.AddInParameter(dbCommand, "@INSPECTION_FLAG", DbType.Boolean, info.InspectionFlag);
db.AddInParameter(dbCommand, "@INSPECTION_MODE", DbType.Int32, info.InspectionMode);
db.AddInParameter(dbCommand, "@VALID_FLAG", DbType.Boolean, info.ValidFlag);
db.AddInParameter(dbCommand, "@CREATE_USER", DbType.String, info.CreateUser);
db.AddInParameter(dbCommand, "@CREATE_DATE", DbType.DateTime, info.CreateDate);
db.AddInParameter(dbCommand, "@MODIFY_USER", DbType.String, info.ModifyUser);
db.AddInParameter(dbCommand, "@MODIFY_DATE", DbType.DateTime, info.ModifyDate);
db.AddInParameter(dbCommand, "@TRUST_TIME", DbType.DateTime, info.TrustTime);
db.AddInParameter(dbCommand, "@SUM_PACKAGE_QTY", DbType.Int32, info.SumPackageQty);
db.AddInParameter(dbCommand, "@SUM_PART_QTY", DbType.Decimal, info.SumPartQty);
db.AddInParameter(dbCommand, "@SUM_WEIGHT", DbType.Decimal, info.SumWeight);
db.AddInParameter(dbCommand, "@SUM_VOLUME", DbType.Decimal, info.SumVolume);
db.AddInParameter(dbCommand, "@ROUTE_CODE", DbType.String, info.RouteCode);
db.AddInParameter(dbCommand, "@ROUTE_NAME", DbType.String, info.RouteName);
db.AddInParameter(dbCommand, "@CUST_CODE", DbType.String, info.CustCode);
db.AddInParameter(dbCommand, "@CUST_SNAME", DbType.String, info.CustSname);
db.AddInParameter(dbCommand, "@CUST_TRUST_NO", DbType.String, info.CustTrustNo);
db.AddInParameter(dbCommand, "@DELIVERY_CODE", DbType.String, info.DeliveryCode);
db.AddInParameter(dbCommand, "@DELIVERY_SNAME", DbType.String, info.DeliverySname);
db.AddInParameter(dbCommand, "@DELIVERY_NAME", DbType.String, info.DeliveryName);
db.AddInParameter(dbCommand, "@DELIVERY_CONTACT", DbType.String, info.DeliveryContact);
db.AddInParameter(dbCommand, "@DELIVERY_TEL", DbType.String, info.DeliveryTel);
db.AddInParameter(dbCommand, "@SHIPPING_CODE", DbType.String, info.ShippingCode);
db.AddInParameter(dbCommand, "@SHIPPING_SNAME", DbType.String, info.ShippingSname);
db.AddInParameter(dbCommand, "@SHIPPING_NAME", DbType.String, info.ShippingName);
db.AddInParameter(dbCommand, "@SHIPPING_ADD", DbType.String, info.ShippingAdd);
db.AddInParameter(dbCommand, "@SHIPPING_CONTACT", DbType.String, info.ShippingContact);
db.AddInParameter(dbCommand, "@SHIPPING_TEL", DbType.String, info.ShippingTel);
db.AddInParameter(dbCommand, "@ORGANIZATION_FID", DbType.Guid, info.OrganizationFid);
return(int.Parse("0" + db.ExecuteNonQuery(dbCommand)));
}
/// <summary>
/// 新增厂家事务方法
/// </summary>
/// <param name="aId">区域编号</param>
/// <param name="name">工作站名称</param>
/// <param name="bankAddress"></param>
/// <param name="addr">工作站地址</param>
/// <param name="manager">负责人</param>
/// <param name="managerPhone">负责人电话</param>
/// <param name="phone">工作站电话</param>
/// <param name="bankAccount"></param>
/// <param name="bankName"></param>
/// <param name="providerType"></param>
/// <returns></returns>
public int AddSup(string aId, string name, string bankAccount, string bankName, string bankAddress, string addr, string manager, string managerPhone, string phone, string providerType)
{
int result = 0;
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into OrgOrganize(");
strSql.Append("AreaId,OrgName,OrgAddress,OrgPhone,OrgFax,OrgType,OrgParentId,AddTime,Status)");
strSql.Append(" values (");
strSql.Append("@AreaId,@OrgName,@OrgAddress,@OrgPhone,@OrgFax,@OrgType,@OrgParentId,@AddTime,@Status)");
strSql.Append(";select @@IDENTITY");
Database db = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
db.AddInParameter(dbCommand, "AreaId", DbType.AnsiString, aId);
db.AddInParameter(dbCommand, "OrgName", DbType.String, name);
db.AddInParameter(dbCommand, "OrgAddress", DbType.String, addr);
db.AddInParameter(dbCommand, "OrgPhone", DbType.AnsiString, phone);
db.AddInParameter(dbCommand, "OrgFax", DbType.AnsiString, "");
db.AddInParameter(dbCommand, "OrgType", DbType.Byte, 3);
db.AddInParameter(dbCommand, "OrgParentId", DbType.Int64, 0);
db.AddInParameter(dbCommand, "AddTime", DbType.DateTime, DateTime.Now);
db.AddInParameter(dbCommand, "Status", DbType.Byte, 1);
using (DbConnection conn = db.CreateConnection())
{
conn.Open();
DbTransaction trans = conn.BeginTransaction();
try
{
var oId = db.ExecuteScalar(dbCommand, trans);
StringBuilder strSql2 = new StringBuilder();
strSql2.Append("insert into OrgProviderInfo(");
strSql2.Append("OrgId,BankAccount,BankName,BankAddress,Manager,ManagerPhone,ProviderType,AddTime)");
strSql2.Append(" values (");
strSql2.Append("@OrgId,@BankAccount,@BankName,@BankAddress,@Manager,@ManagerPhone,@ProviderType,@AddTime1)");
strSql2.Append(";select @@IDENTITY");
DbCommand dbCommand2 = db.GetSqlStringCommand(strSql2.ToString());
db.AddInParameter(dbCommand2, "OrgId", DbType.Int64, oId);
db.AddInParameter(dbCommand2, "BankAccount", DbType.String, bankAccount);
db.AddInParameter(dbCommand2, "BankName", DbType.String, bankName);
db.AddInParameter(dbCommand2, "BankAddress", DbType.String, bankAddress);
db.AddInParameter(dbCommand2, "Manager", DbType.String, manager);
db.AddInParameter(dbCommand2, "ManagerPhone", DbType.AnsiString, managerPhone);
db.AddInParameter(dbCommand2, "ProviderType", DbType.AnsiString, providerType);
db.AddInParameter(dbCommand2, "AddTime1", DbType.DateTime, DateTime.Now);
object obj = db.ExecuteScalar(dbCommand2, trans);
if (!int.TryParse(obj.ToString(), out result))
{
trans.Rollback();
return(0);
}
trans.Commit();
}
catch
{
trans.Rollback();
}
conn.Close();
return(result);
}
}
/// <summary>
/// Constructor
/// </summary>
public CountryDAL()
{
db = DatabaseFactory.CreateDatabase("POS");
}
public CustomGroupingDao()
{
this.database = DatabaseFactory.CreateDatabase();
}
public PlayerRepository(DatabaseFactory dbFactory) : base(dbFactory)
{
}
public HcServicecenterEntity GetSingleHcServicecenterRecordById(object param)
{
Database db = DatabaseFactory.CreateDatabase();
string sql = "SELECT Id, serviceCenterName, serviceCenerAddress, Latitude, Longitude, Phone, Email, ownerName, status, createdBy, createdAt, serviceType, serviceTags FROM HC_ServiceCenter WHERE Id=@Id";
DbCommand dbCommand = db.GetSqlStringCommand(sql);
db.AddInParameter(dbCommand, "Id", DbType.String, param);
HcServicecenterEntity hcServicecenterEntity = null;
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
if (dataReader.Read())
{
hcServicecenterEntity = new HcServicecenterEntity();
if (dataReader["Id"] != DBNull.Value)
{
hcServicecenterEntity.Id = dataReader["Id"].ToString();
}
if (dataReader["serviceCenterName"] != DBNull.Value)
{
hcServicecenterEntity.Servicecentername = dataReader["serviceCenterName"].ToString();
}
if (dataReader["serviceCenerAddress"] != DBNull.Value)
{
hcServicecenterEntity.Serviceceneraddress = dataReader["serviceCenerAddress"].ToString();
}
if (dataReader["Latitude"] != DBNull.Value)
{
hcServicecenterEntity.Latitude = dataReader["Latitude"].ToString();
}
if (dataReader["Longitude"] != DBNull.Value)
{
hcServicecenterEntity.Longitude = dataReader["Longitude"].ToString();
}
if (dataReader["Phone"] != DBNull.Value)
{
hcServicecenterEntity.Phone = dataReader["Phone"].ToString();
}
if (dataReader["Email"] != DBNull.Value)
{
hcServicecenterEntity.Email = dataReader["Email"].ToString();
}
if (dataReader["ownerName"] != DBNull.Value)
{
hcServicecenterEntity.Ownername = dataReader["ownerName"].ToString();
}
if (dataReader["status"] != DBNull.Value)
{
hcServicecenterEntity.Status = dataReader["status"].ToString();
}
if (dataReader["createdBy"] != DBNull.Value)
{
hcServicecenterEntity.Createdby = dataReader["createdBy"].ToString();
}
if (dataReader["createdAt"] != DBNull.Value)
{
hcServicecenterEntity.Createdat = dataReader["createdAt"].ToString();
}
if (dataReader["serviceType"] != DBNull.Value)
{
hcServicecenterEntity.Servicetype = dataReader["serviceType"].ToString();
}
if (dataReader["serviceTags"] != DBNull.Value)
{
hcServicecenterEntity.Servicetags = dataReader["serviceTags"].ToString();
}
}
}
return(hcServicecenterEntity);
}
protected void Page_Load(object sender, EventArgs e)
{
VerifyPage("", true);
var iid = Request.QueryString["iid"];
if (!string.IsNullOrEmpty(iid))
{
try
{
var imodel = _infosDal.GetModel(Convert.ToInt32(iid));
if (null != imodel)
{
Database db = DatabaseFactory.CreateDatabase();
using (DbConnection conn = db.CreateConnection())
{
conn.Open();
DbTransaction trans = conn.BeginTransaction();
try
{
var templatesql = "select TIID from TempInfo where ParentID=" + iid;
DbCommand dbComTemplate = db.GetSqlStringCommand(templatesql);
var dt = db.ExecuteDataSet(dbComTemplate, trans).Tables[0];
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
DelTemplate(db, trans, Convert.ToInt32(dt.Rows[i]["TIID"]));
}
}
#region
var Attas = new StringBuilder();
Attas.Append(" select * from AttaList where IID=" + iid);
DbCommand dbComAttList = db.GetSqlStringCommand(Attas.ToString());
var dtAttList1 = db.ExecuteDataSet(dbComAttList, trans).Tables[0];
dtAttList.Merge(dtAttList1);
var Infos = new StringBuilder();
Infos.Append(" select * from Infos where IID=" + iid);
DbCommand dbComInfos = db.GetSqlStringCommand(Infos.ToString());
var dtInfos1 = db.ExecuteDataSet(dbComInfos, trans).Tables[0];
dtInfos.Merge(dtInfos1);
#endregion
var delinfo = "delete from InfoLabel where IID=" + iid + ";delete from AdInfos where Inf_IID=" + iid + ";delete from AdInfos where IID=" + iid + ";delete from AttaList where IID=" + iid + ";delete from Infos where IID=" + iid;
DbCommand dbComDelInfo = db.GetSqlStringCommand(delinfo);
db.ExecuteNonQuery(dbComDelInfo, trans);
trans.Commit();
conn.Close();
#region delete files
if (dtAttList.Rows.Count > 0)
{
for (int i = 0; i < dtAttList.Rows.Count; i++)
{
try
{
var oldbpic =
new FileInfo(
HttpContext.Current.Server.MapPath("/UploadFiles/" + dtAttList.Rows[i]["AttID"]));
if (!string.IsNullOrEmpty(dtAttList.Rows[i]["AttID"].ToString()) && oldbpic.Exists)
{
oldbpic.Delete();
}
}
catch (Exception)
{
}
}
}
if (dtInfos.Rows.Count > 0)
{
for (int i = 0; i < dtInfos.Rows.Count; i++)
{
try
{
var oldbpic =
new FileInfo(
HttpContext.Current.Server.MapPath("/UploadFiles/" + dtInfos.Rows[i]["PicAttID"]));
if (!string.IsNullOrEmpty(dtInfos.Rows[i]["PicAttID"].ToString()) && oldbpic.Exists)
{
oldbpic.Delete();
}
}
catch (Exception)
{
}
try
{
var oldbpic1 =
new FileInfo(
HttpContext.Current.Server.MapPath("/UploadFiles/" +
dtInfos.Rows[i]["VideoAttID"]));
if (!string.IsNullOrEmpty(dtInfos.Rows[i]["VideoAttID"].ToString()) && oldbpic1.Exists)
{
oldbpic1.Delete();
}
}
catch (Exception)
{
}
try
{
var oldbpic2 =
new FileInfo(
HttpContext.Current.Server.MapPath("/UploadFiles/" + dtInfos.Rows[i]["ADPic"]));
if (!string.IsNullOrEmpty(dtInfos.Rows[i]["ADPic"].ToString()) && oldbpic2.Exists)
{
oldbpic2.Delete();
}
}
catch (Exception)
{
}
}
}
if (dtTempInfo.Rows.Count > 0)
{
for (int i = 0; i < dtTempInfo.Rows.Count; i++)
{
try
{
var oldbpic =
new FileInfo(
HttpContext.Current.Server.MapPath("/UploadFiles/" + dtTempInfo.Rows[i]["AttID"]));
if (!string.IsNullOrEmpty(dtTempInfo.Rows[i]["AttID"].ToString()) && oldbpic.Exists)
{
oldbpic.Delete();
}
}
catch (Exception)
{
}
}
}
#endregion
Response.Write("0|~|");
Response.End();
}
catch (System.Threading.ThreadAbortException ex)
{
}
catch (Exception ee)
{
trans.Rollback();
conn.Close();
Response.Write("1|~|" + ee.Message);
Response.End();
}
}
}
else
{
Response.Write("1|~|操作失败");
Response.End();
}
}
catch (System.Threading.ThreadAbortException ex)
{
}
catch (Exception ee)
{
Response.Write("1|~|" + ee.Message);
Response.End();
}
}
}
/// <summary>
/// Constructor
/// </summary>
public CBReceiptDetailDAL()
{
db = DatabaseFactory.CreateDatabase("POS");
}
/// <summary>
/// Decides whether to Call Add/Edit/Delete method
/// And Calls the appropriate method
/// </summary>
/// <returns>TransactionResult - Success / Failure</returns>
public TransactionResult Commit()
{
TransactionResult result = null;
Database db = DatabaseFactory.CreateDatabase(_myConnection.DatabaseName);
using (DbConnection connection = db.CreateConnection())
{
connection.Open();
DbTransaction transaction = connection.BeginTransaction();
try
{
switch (ScreenMode)
{
case ScreenMode.Add:
//Adding Employee
result = AddWorkFromHomeEmployee(db, transaction);
if (result.Status == TransactionStatus.Failure)
{
transaction.Rollback();
return(result);
}
break;
case ScreenMode.Edit:
//Editing Employee
result = EditWorkFromHomeEmployee(db, transaction);
if (result.Status == TransactionStatus.Failure)
{
transaction.Rollback();
return(result);
}
break;
case ScreenMode.View:
break;
}
transaction.Commit();
return(result);
}
catch (Exception ex)
{
transaction.Rollback();
if (ScreenMode == ScreenMode.Add)
{
ErrorLog.LogErrorMessageToDB("", "WorkFromHomeDL.cs", "Commit For Add", ex.Message.ToString(), _myConnection);
return(new TransactionResult(TransactionStatus.Failure, "Failure Adding"));
}
if (ScreenMode == ScreenMode.Edit)
{
ErrorLog.LogErrorMessageToDB("", "WorkFromHomeDL.cs", "Commit For Edit", ex.Message.ToString(), _myConnection);
return(new TransactionResult(TransactionStatus.Failure, "Failure Updating"));
}
if (ScreenMode == ScreenMode.Delete)
{
ErrorLog.LogErrorMessageToDB("", "WorkFromHomeDL.cs", "Commit For Delete", ex.Message.ToString(), _myConnection);
return(new TransactionResult(TransactionStatus.Failure, "Failure Deleting"));
}
}
}
return(null);
}
/// <summary>
/// 更新厂家事务方法
/// </summary>
/// <param name="dt">dataTable</param>
/// <returns></returns>
public bool UpDateOrgSupTran(DataTable dt)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update OrgOrganize set ");
strSql.Append("OrgName=@OrgName,");
strSql.Append("OrgAddress=@OrgAddress,");
strSql.Append("OrgPhone=@OrgPhone,");
strSql.Append("OrgFax=@OrgFax,");
strSql.Append("OrgType=@OrgType,");
strSql.Append("OrgParentId=@OrgParentId ");
strSql.Append(" where OrgId=@OrgId ");
Database db = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
db.AddInParameter(dbCommand, "OrgId", DbType.Int64, dt.Rows[0]["OrgId"]);
db.AddInParameter(dbCommand, "OrgName", DbType.String, dt.Rows[0]["OrgName"]);
db.AddInParameter(dbCommand, "OrgAddress", DbType.String, dt.Rows[0]["OrgAddress"]);
db.AddInParameter(dbCommand, "OrgPhone", DbType.AnsiString, dt.Rows[0]["OrgPhone"]);
db.AddInParameter(dbCommand, "OrgFax", DbType.AnsiString, dt.Rows[0]["OrgFax"]);
db.AddInParameter(dbCommand, "OrgType", DbType.Byte, dt.Rows[0]["OrgType"]);
db.AddInParameter(dbCommand, "OrgParentId", DbType.Int64, dt.Rows[0]["OrgParentId"]);
bool result = false;
using (DbConnection conn = db.CreateConnection())
{
conn.Open();
DbTransaction trans = conn.BeginTransaction();
try
{
db.ExecuteNonQuery(dbCommand, trans);
StringBuilder strSql2 = new StringBuilder();
strSql2.Append("update OrgProviderInfo set ");
strSql2.Append("BankAccount=@BankAccount,");
strSql2.Append("BankName=@BankName,");
strSql2.Append("BankAddress=@BankAddress,");
strSql2.Append("Manager=@Manager,");
strSql2.Append("ProviderType=@ProviderType,");
strSql2.Append("ManagerPhone=@ManagerPhone");
strSql2.Append(" where OrgId=@OrgId ");
DbCommand dbCommand2 = db.GetSqlStringCommand(strSql2.ToString());
db.AddInParameter(dbCommand2, "OrgId", DbType.Int64, dt.Rows[0]["OrgId"]);
db.AddInParameter(dbCommand2, "BankAccount", DbType.String, dt.Rows[0]["BankAccount"]);
db.AddInParameter(dbCommand2, "BankName", DbType.String, dt.Rows[0]["BankName"]);
db.AddInParameter(dbCommand2, "BankAddress", DbType.String, dt.Rows[0]["BankAddress"]);
db.AddInParameter(dbCommand2, "Manager", DbType.String, dt.Rows[0]["Manager"]);
db.AddInParameter(dbCommand2, "ProviderType", DbType.String, dt.Rows[0]["ProviderType"]);
db.AddInParameter(dbCommand2, "ManagerPhone", DbType.AnsiString, dt.Rows[0]["ManagerPhone"]);
db.ExecuteNonQuery(dbCommand2, trans);
trans.Commit();
result = true;
}
catch
{
trans.Rollback();
}
conn.Close();
return(result);
}
}
/// <summary>
/// 公司对应往来单位当月应付款总额
/// </summary>
/// <param name="year"></param>
/// <param name="month"></param>
/// <param name="filialeId"></param>
/// <param name="companyName"></param>
/// <returns></returns>
public IList <SupplierPaymentsRecordInfo> SelectCurrentMonthPaymentsRecordsDetail(int year, int month, Guid filialeId, string companyName)
{
const string SQL = @"DECLARE @CompanyID UNIQUEIDENTIFIER --往来单位ID
,@CompanyName varchar(128) -- 公司名称
,@PaymentDays INT --账期
,@time1 date --开始日期
,@time2 date --账期日期(根据账期计算账期日期)(结束日期)
,@time3 date --账期日期(根据账期计算账期日期)(开始日期)
,@TheMonthNum int --月份循环值
DECLARE @TempCompanyIdRecord TABLE(
CompanyId UNIQUEIDENTIFIER null,
CompanyName varchar(128) null,
PaymentDays int null,
TotalAmount decimal(18,4) null,
TotalNoPayed decimal(18,4) null
)
SET @time1= '{0}'--参数2 开始日期
SET @time2= dateadd(mm,0,DATEADD(MS,-3,DATEADD(MM, DATEDIFF(MM,0,@time1)+1, 0)))
INSERT INTO @TempCompanyIdRecord([CompanyId],[CompanyName],[PaymentDays],[TotalAmount],[TotalNoPayed])
SELECT R.ThirdCompanyID AS CompanyId,CC.CompanyName,0 AS PaymentDays ,
ISNULL(sum(case when LinkTradeType = 1 and AccountReceivable< 0 then AccountReceivable else 0 end),0) AS TotalAmount ,
ISNULL(sum(case when IsChecked =0 then AccountReceivable else 0 end),0) AS TotalNoPayed
FROM lmShop_Reckoning AS R with(nolock)
INNER JOIN lmshop_CompanyCussent CC ON R.ThirdCompanyID=CC.CompanyID AND CC.CompanyType=1 AND CC.PaymentDays=0 {2}
where R.DateCreated between @time1 and @time2 {1}
and AuditingState=1 AND R.[State]<>2 and LinkTradeType in (1,2)
and (
LinkTradeCode like 'RK%' or LinkTradeCode like 'SL%' or LinkTradeCode like 'SO%'
or LinkTradeCode like 'SI%' or LinkTradeCode like 'TSI%' or LinkTradeCode like 'TSO%'
or LinkTradeCode like 'TR%') and TradeCode not like 'GT%'
GROUP BY R.ThirdCompanyID,CC.CompanyName
--循环变量
DECLARE @W_Count INT, @W_Num INT
----临时往来单位表
SELECT ROW_NUMBER() OVER(ORDER BY CompanyID) AS RowID,CompanyId,CompanyName,PaymentDays INTO #TempCompanyCussent
FROM lmShop_CompanyCussent AS CC WHERE CC.CompanyType=1 AND CC.PaymentDays>0 {2}
--循环变量赋值
SET @W_Num =1
SELECT @W_Count=COUNT(0) FROM #TempCompanyCussent
--循环往来单位
WHILE(@W_Num <= @W_Count)
BEGIN
--获取往来单位
SELECT @CompanyID = CompanyId,@CompanyName=CompanyName,@PaymentDays=PaymentDays FROM #TempCompanyCussent WHERE RowID = @w_num
SET @time3= dateadd(mm,-@PaymentDays,'{0}')--参数2 开始日期
SET @time2= dateadd(mm,-@PaymentDays,DATEADD(MS,-3,DATEADD(MM, DATEDIFF(MM,0,@time1)+1, 0)))
INSERT INTO @TempCompanyIdRecord([CompanyId],[CompanyName],[PaymentDays],[TotalAmount],[TotalNoPayed])
SELECT @CompanyID AS CompanyId,@CompanyName AS CompanyName,@PaymentDays AS PaymentDays,
ISNULL(sum(case when LinkTradeType = 1 and AccountReceivable< 0 then AccountReceivable else 0 end),0) AS TotalAmount ,
ISNULL(sum(case when IsChecked =0 then AccountReceivable else 0 end),0) AS TotalNoPayed FROM lmshop_Reckoning as R with(nolock)
where R.[ThirdCompanyID]=@CompanyID AND R.DateCreated between @time3 and @time2 {1}
and AuditingState=1 AND R.[State]<>2 and LinkTradeType in (1,2)
and (
LinkTradeCode like 'RK%' or LinkTradeCode like 'SL%' or LinkTradeCode like 'SO%'
or LinkTradeCode like 'SI%' or LinkTradeCode like 'TSI%' or LinkTradeCode like 'TSO%'
or LinkTradeCode like 'TR%') and TradeCode not like 'GT%'
SET @W_Num +=1 --循环累加
END
DROP TABLE #TempCompanyCussent
SELECT CompanyId,CompanyName,PaymentDays,@time1 AS DayTime,TotalAmount,TotalNoPayed FROM @TempCompanyIdRecord WHERE TotalAmount!=0 ORDER BY TotalAmount DESC ";
string filieleStr = string.Empty;
if (filialeId != Guid.Empty)
{
filieleStr = string.Format(" AND R.FilialeId='{0}' ", filialeId);
}
var startTime = new DateTime(year, month, 1);
var newSQL = string.Format(SQL, startTime, filieleStr, string.IsNullOrEmpty(companyName) ? ""
: string.Format(" AND CC.CompanyName LIKE '%{0}%'", companyName));
using (var db = DatabaseFactory.Create())
{
return(db.Select <SupplierPaymentsRecordInfo>(true, newSQL).ToList());
}
}
protected override void BeforeExecute(CommandInfo command)
{
DbContext = DatabaseFactory.Create();
base.BeforeExecute(command);
}
static DataAccessComponent()
{
DatabaseFactory.SetDatabaseProviderFactory(new DatabaseProviderFactory(), false);
}
public EADatabaseTransformer(DatabaseFactory factory, UTF_EA.Model model, NameTranslator nameTranslator) : base(nameTranslator)
{
this._factory = factory;
this._model = model;
}
private static Database GetDatabaseInstance()
{
return(DatabaseFactory.CreateDatabase(DatabaseEnum.SITBusinessDB));
}
public HelpCategoryDao()
{
this.database = DatabaseFactory.CreateDatabase();
}
/// <summary>
/// Constructor
/// </summary>
public CBPaymentDetailDAL()
{
db = DatabaseFactory.CreateDatabase("POS");
}
public void TaskSchedulerManager_UpdateLicense()
{
var dbFactory = new DatabaseFactory();
var dvsContext = dbFactory.Get<DVSContext>();
var appraiserUserRepository = new AppraiserUserRepository(dbFactory);
var user = appraiserUserRepository.GetByUserId(TestConstants.TestAppraiserUser);
user.Licenses.Add(new License() { LicenseNumber = "2", State = "NY", Status = LicenseStatus.Active, ExpirationDate = DateTime.Now.AddYears(-1) });
appraiserUserRepository.Update(user);
dvsContext.SaveChanges();
var task = new Task();
task.CreateDate = DateTime.Now;
task.TaskTypeEnum = TaskTypeEnum.LicenseDailyUpdateByAscGov;
task.TaskStatus = TaskStatus.Pending;
_repository.Add(task);
_taskQueueManager.ProcessQueue();
int maxIterationsCount = 10;
while (maxIterationsCount-- != 0)
{
using (var dc = new TaskQueueContext())
{
var tTask = dc.Tasks.Single(t => t.Id == task.Id);
if (tTask.TaskStatus != TaskStatus.Executing)
break;
Thread.Sleep(1000 * 60);
}
}
dbFactory = new DatabaseFactory();
user = (new AppraiserUserRepository(dbFactory)).GetByUserId(TestConstants.TestAppraiserUser);
user.Licenses.Where(e => e.LicenseNumber == "2").Should().HaveCount(1);
user.Licenses.Single(e => e.LicenseNumber == "2").Status.Should().Be(LicenseStatus.Expired);
}
public UpdatedEventSetups(DatabaseFactory databaseFactory)
{
this.databaseFactory = databaseFactory;
}
public CustomerDAL()
{
m_CustomerDb = DatabaseFactory.CreateDatabase();
}
//=====================================================================
// FunctionName : UpdateByAnyCondition
/// <summary>
/// 以任意条件更新记录
/// </summary>
//=====================================================================
public override void UpdateByAnyCondition()
{
// 创建数据库连接
Database db = DatabaseFactory.CreateDatabase("strConnManager");
string strProcName = "SP_UpdateDictionaryByAnyCondition";
DbCommand cmdProc = db.GetStoredProcCommand(strProcName);
// 设定存储过程输入参数
db.AddInParameter(cmdProc, "@QueryType", DbType.String);
db.AddInParameter(cmdProc, "@ObjectID", DbType.String);
db.AddInParameter(cmdProc, "@ObjectIDBatch", DbType.String);
db.AddInParameter(cmdProc, "@ObjectIDValue", DbType.String);
db.AddInParameter(cmdProc, "@DM", DbType.String);
db.AddInParameter(cmdProc, "@DMBatch", DbType.String);
db.AddInParameter(cmdProc, "@DMValue", DbType.String);
db.AddInParameter(cmdProc, "@LX", DbType.String);
db.AddInParameter(cmdProc, "@LXBatch", DbType.String);
db.AddInParameter(cmdProc, "@LXValue", DbType.String);
db.AddInParameter(cmdProc, "@MC", DbType.String);
db.AddInParameter(cmdProc, "@MCBatch", DbType.String);
db.AddInParameter(cmdProc, "@MCValue", DbType.String);
db.AddInParameter(cmdProc, "@SJDM", DbType.String);
db.AddInParameter(cmdProc, "@SJDMBatch", DbType.String);
db.AddInParameter(cmdProc, "@SJDMValue", DbType.String);
db.AddInParameter(cmdProc, "@SM", DbType.String);
db.AddInParameter(cmdProc, "@SMBatch", DbType.String);
db.AddInParameter(cmdProc, "@SMValue", DbType.String);
// 设定存储过程输出参数
db.AddOutParameter(cmdProc, "@RecordCount", DbType.Int32, 4);
// 对存储过程参数赋值
db.SetParameterValue(cmdProc, "@QueryType", AppData.QueryType);
db.SetParameterValue(cmdProc, "@QueryKeywords", AppData.QueryKeywords);
db.SetParameterValue(cmdProc, "@ObjectID", AppData.ObjectID);
db.SetParameterValue(cmdProc, "@ObjectIDBatch", AppData.ObjectIDBatch);
db.SetParameterValue(cmdProc, "@ObjectIDValue", AppData.ObjectIDValue);
db.SetParameterValue(cmdProc, "@DM", AppData.DM);
db.SetParameterValue(cmdProc, "@DMBatch", AppData.DMBatch);
db.SetParameterValue(cmdProc, "@DMValue", AppData.DMValue);
db.SetParameterValue(cmdProc, "@LX", AppData.LX);
db.SetParameterValue(cmdProc, "@LXBatch", AppData.LXBatch);
db.SetParameterValue(cmdProc, "@LXValue", AppData.LXValue);
db.SetParameterValue(cmdProc, "@MC", AppData.MC);
db.SetParameterValue(cmdProc, "@MCBatch", AppData.MCBatch);
db.SetParameterValue(cmdProc, "@MCValue", AppData.MCValue);
db.SetParameterValue(cmdProc, "@SJDM", AppData.SJDM);
db.SetParameterValue(cmdProc, "@SJDMBatch", AppData.SJDMBatch);
db.SetParameterValue(cmdProc, "@SJDMValue", AppData.SJDMValue);
db.SetParameterValue(cmdProc, "@SM", AppData.SM);
db.SetParameterValue(cmdProc, "@SMBatch", AppData.SMBatch);
db.SetParameterValue(cmdProc, "@SMValue", AppData.SMValue);
// 执行存储过程
AppData.ResultSet = (DataSet)db.ExecuteDataSet(cmdProc);
// 得到更新的记录数
AppData.RecordCount = db.GetParameterValue(cmdProc, "@RecordCount") == DBNull.Value ? 0 : (Int32)db.GetParameterValue(cmdProc, "@RecordCount");
}
//=====================================================================
// FunctionName : SelectByAnyCondition
/// <summary>
/// 以任意条件查询记录
/// </summary>
//=====================================================================
public override void SelectByAnyCondition()
{
// 创建数据库连接
Database db = DatabaseFactory.CreateDatabase("strConnManager");
string strProcName = "SP_SelectDictionaryByAnyCondition";
DbCommand cmdProc = db.GetStoredProcCommand(strProcName);
// 设定存储过程输入参数
db.AddInParameter(cmdProc, "@QueryField", DbType.String);
db.AddInParameter(cmdProc, "@QueryType", DbType.String);
db.AddInParameter(cmdProc, "@Sort", DbType.Boolean);
db.AddInParameter(cmdProc, "@SortField", DbType.String);
db.AddInParameter(cmdProc, "@PageSize", DbType.Int32);
db.AddInParameter(cmdProc, "@CurrentPage", DbType.Int32);
// 主表
db.AddInParameter(cmdProc, "@ObjectID", DbType.String);
db.AddInParameter(cmdProc, "@ObjectIDBatch", DbType.String);
db.AddInParameter(cmdProc, "@DM", DbType.String);
db.AddInParameter(cmdProc, "@DMBatch", DbType.String);
db.AddInParameter(cmdProc, "@LX", DbType.String);
db.AddInParameter(cmdProc, "@LXBatch", DbType.String);
db.AddInParameter(cmdProc, "@MC", DbType.String);
db.AddInParameter(cmdProc, "@MCBatch", DbType.String);
db.AddInParameter(cmdProc, "@SJDM", DbType.String);
db.AddInParameter(cmdProc, "@SJDMBatch", DbType.String);
db.AddInParameter(cmdProc, "@SM", DbType.String);
db.AddInParameter(cmdProc, "@SMBatch", DbType.String);
// 一对一相关表
// 设定存储过程输出参数
db.AddOutParameter(cmdProc, "@RecordCount", DbType.Int32, 4);
// 对存储过程参数赋值
db.SetParameterValue(cmdProc, "@QueryField", AppData.QueryField);
db.SetParameterValue(cmdProc, "@QueryType", AppData.QueryType);
db.SetParameterValue(cmdProc, "@Sort", AppData.Sort);
db.SetParameterValue(cmdProc, "@SortField", AppData.SortField);
db.SetParameterValue(cmdProc, "@PageSize", AppData.PageSize);
db.SetParameterValue(cmdProc, "@CurrentPage", AppData.CurrentPage);
// 主表
db.SetParameterValue(cmdProc, "@ObjectID", AppData.ObjectID);
db.SetParameterValue(cmdProc, "@ObjectIDBatch", AppData.ObjectIDBatch);
db.SetParameterValue(cmdProc, "@DM", AppData.DM);
db.SetParameterValue(cmdProc, "@DMBatch", AppData.DMBatch);
db.SetParameterValue(cmdProc, "@LX", AppData.LX);
db.SetParameterValue(cmdProc, "@LXBatch", AppData.LXBatch);
db.SetParameterValue(cmdProc, "@MC", AppData.MC);
db.SetParameterValue(cmdProc, "@MCBatch", AppData.MCBatch);
db.SetParameterValue(cmdProc, "@SJDM", AppData.SJDM);
db.SetParameterValue(cmdProc, "@SJDMBatch", AppData.SJDMBatch);
db.SetParameterValue(cmdProc, "@SM", AppData.SM);
db.SetParameterValue(cmdProc, "@SMBatch", AppData.SMBatch);
// 一对一相关表
// 执行存储过程
AppData.ResultSet = (DataSet)db.ExecuteDataSet(cmdProc);
// 得到返回记录数
AppData.RecordCount = db.GetParameterValue(cmdProc, "@RecordCount") == DBNull.Value ? 0 : (Int32)db.GetParameterValue(cmdProc, "@RecordCount");
}
private Database db;//数据库操作对象。
/// <summary>
/// 构造函数。
/// </summary>
public SampEngine()
{
db = DatabaseFactory.CreateDatabase();
}
//=====================================================================
// FunctionName : GetValueByFixCondition
/// <summary>
/// 以指定条件查询指定字段
/// </summary>
//=====================================================================
public override object GetValueByFixCondition(string strConditionField, object strConditionValue, string strValueField, string strFixConditionField = null, object strFixConditionValue = null)
{
object objReturn = new object();
StringBuilder sbReturn = new StringBuilder();
if ((strConditionValue == DBNull.Value || strConditionValue == null) == false)
{
string[] strArrayConditionValue = strConditionValue.ToString().Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
bool boolFirstItem = true;
foreach (string strValue in strArrayConditionValue)
{
DataSet dsTemp = new DataSet();
// 创建数据库连接
Database db = DatabaseFactory.CreateDatabase("strConnManager");
string strProcName = "SP_SelectDictionaryByAnyCondition";
DbCommand cmdProc = db.GetStoredProcCommand(strProcName);
// 设定存储过程输入参数
db.AddInParameter(cmdProc, "@Sort", DbType.Boolean);
db.AddInParameter(cmdProc, "@SortField", DbType.String);
db.AddInParameter(cmdProc, "@PageSize", DbType.Int32);
db.AddInParameter(cmdProc, "@CurrentPage", DbType.Int32);
db.AddInParameter(cmdProc, "@ObjectID", DbType.String);
db.AddInParameter(cmdProc, "@DM", DbType.String);
db.AddInParameter(cmdProc, "@LX", DbType.String);
db.AddInParameter(cmdProc, "@MC", DbType.String);
db.AddInParameter(cmdProc, "@SJDM", DbType.String);
db.AddInParameter(cmdProc, "@SM", DbType.String);
// 设定存储过程输出参数
db.AddOutParameter(cmdProc, "@RecordCount", DbType.Int32, 4);
// 对存储过程参数赋值
db.SetParameterValue(cmdProc, "@" + strConditionField, strValue);
if (!strFixConditionField.IsNullOrWhiteSpace())
{
if (strFixConditionField != "null")
{
db.SetParameterValue(cmdProc, "@" + strFixConditionField, strFixConditionValue);
}
}
// 执行存储过程
dsTemp = (DataSet)db.ExecuteDataSet(cmdProc);
if ((Int32)db.GetParameterValue(cmdProc, "@RecordCount") > 0)
{
if (boolFirstItem == false)
{
sbReturn.Append(",");
sbReturn.Append(dsTemp.Tables[0].Rows[0][strValueField]);
}
else if (boolFirstItem == true)
{
sbReturn.Append(dsTemp.Tables[0].Rows[0][strValueField]);
boolFirstItem = false;
}
}
}
objReturn = (object)sbReturn.ToString();
}
else
{
objReturn = (object)string.Empty;
}
return(objReturn);
}
public UserBranchDAL()
{
db = DatabaseFactory.CreateDatabase("PickC");
}
