public static bool WithdrawTransaction(string catalog, bool isStockTransferRequest, long transactionMasterId, int userId, string reason, short status) { string sql = @"UPDATE transactions.transaction_master SET verification_status_id=@Status, verified_by_user_id=@UserId, verification_reason=@Reason, last_verified_on = NOW() WHERE transactions.transaction_master.transaction_master_id=@TransactionMasterId;"; if (isStockTransferRequest) { sql = @"UPDATE transactions.inventory_transfer_requests SET authorization_status_id=@Status, authorized_by_user_id=@UserId, authorization_reason=@Reason, authorized_on = NOW() WHERE transactions.inventory_transfer_requests.inventory_transfer_request_id=@TransactionMasterId;"; } using (NpgsqlCommand command = new NpgsqlCommand(sql)) { command.Parameters.AddWithValue("@Status", status); command.Parameters.AddWithValue("@UserId", userId); command.Parameters.AddWithValue("@Reason", reason); command.Parameters.AddWithValue("@TransactionMasterId", transactionMasterId); return(DbOperation.ExecuteNonQuery(catalog, command)); } }
internal static void AddMenus(IEnumerable <ReportMenu> menus) { menus = menus.Where(m => !string.IsNullOrWhiteSpace(m.MenuCode) && !string.IsNullOrWhiteSpace(m.ParentMenuCode)); foreach (ReportMenu menu in menus) { string url = Config.ReportUrlExpression.Replace("{ReportName}", menu.FileName); const string sql = @"SELECT * FROM core.create_menu(@MenuText, @Url, @MenuCode, 2, core.get_menu_id_by_menu_code(@ParentMenuCode));"; using (NpgsqlCommand command = new NpgsqlCommand(sql)) { command.Parameters.AddWithValue("@MenuText", menu.Text); command.Parameters.AddWithValue("@Url", url); command.Parameters.AddWithValue("@MenuCode", menu.MenuCode); command.Parameters.AddWithValue("@ParentMenuCode", menu.ParentMenuCode); foreach (string catalog in GetCatalogs()) { if (DbOperation.IsServerAvailable(catalog)) { DbOperation.ExecuteNonQuery(catalog, command); } } } } }
private static void InstallUnitTests() { string sql = GetScript(); using (NpgsqlCommand command = new NpgsqlCommand(sql)) { DbOperation.ExecuteNonQuery(command); } }
public static void Vacuum() { const string sql = "VACUUM;"; using (NpgsqlCommand command = new NpgsqlCommand(sql)) { command.CommandTimeout = 3600; DbOperation.ExecuteNonQuery(command); } }
/// <summary> /// Asks the database server to analyze and collect statistics of the current database. /// For further information, http://www.postgresql.org/docs/9.4/static/sql-analyze.html /// </summary> public void Analyze(string catalog) { const string sql = "ANALYZE;"; using (NpgsqlCommand command = new NpgsqlCommand(sql)) { command.CommandTimeout = 3600; DbOperation.ExecuteNonQuery(catalog, command); } }
/// <summary> /// The vacuum full command frees the storage space of the database server against the /// dead/inactive database tuples. For further information, /// http://www.postgresql.org/docs/9.4/static/sql-vacuum.html. /// </summary> public void VacuumFull(string catalog) { const string sql = "VACUUM FULL;"; using (NpgsqlCommand command = new NpgsqlCommand(sql)) { command.CommandTimeout = 3600; DbOperation.ExecuteNonQuery(catalog, command); } }
public static void Initialize(int userId, int officeId) { const string sql = "SELECT * FROM transactions.initialize_eod_operation(@UserId, @OfficeId, transactions.get_value_date(@OfficeId));"; using (NpgsqlCommand command = new NpgsqlCommand(sql)) { command.Parameters.AddWithValue("@UserId", userId); command.Parameters.AddWithValue("@OfficeId", officeId); DbOperation.ExecuteNonQuery(command); } }
public static void Initialize(string catalog, int userId, int officeId, DateTime valueDate) { const string sql = "SELECT * FROM transactions.initialize_eod_operation(@UserId::integer, @OfficeId::integer, @ValueDate::date);"; using (NpgsqlCommand command = new NpgsqlCommand(sql)) { command.Parameters.AddWithValue("@UserId", userId); command.Parameters.AddWithValue("@OfficeId", officeId); command.Parameters.AddWithValue("@ValueDate", valueDate); DbOperation.ExecuteNonQuery(catalog, command); } }
public static bool WithdrawTransaction(long transactionMasterId, int userId, string reason, short status) { const string sql = "UPDATE transactions.transaction_master SET verification_status_id=@Status, verified_by_user_id=@UserId, verification_reason=@Reason WHERE transactions.transaction_master.transaction_master_id=@TransactionMasterId;"; using (NpgsqlCommand command = new NpgsqlCommand(sql)) { command.Parameters.AddWithValue("@Status", status); command.Parameters.AddWithValue("@UserId", userId); command.Parameters.AddWithValue("@Reason", reason); command.Parameters.AddWithValue("@TransactionMasterId", transactionMasterId); return(DbOperation.ExecuteNonQuery(command)); } }
private bool CreateDb() { string sql = "CREATE DATABASE {0} WITH ENCODING='UTF8' TEMPLATE=template0 LC_COLLATE='C' LC_CTYPE='C';"; sql = string.Format(CultureInfo.InvariantCulture, sql, Sanitizer.SanitizeIdentifierName(this.Catalog.ToLower())); string catalog = Factory.MetaDatabase; string connectionString = ConnectionString.GetSuperUserConnectionString(catalog); using (var command = new NpgsqlCommand(sql)) { return(DbOperation.ExecuteNonQuery(this.Catalog, command, connectionString)); } }
public static bool Save(long loginId, int userId, int officeId, Collection <Models.Reorder> details) { string sql = string.Format(CultureInfo.InvariantCulture, "SELECT * FROM transactions.post_purhcase_reorder(transactions.get_value_date(@OfficeId::integer)::date, @LoginId::bigint, @UserId::integer, @OfficeId::integer, ARRAY[{0}]);", CreatePurchaseReorderTypeParameter(details)); using (NpgsqlCommand command = new NpgsqlCommand(sql)) { command.Parameters.AddWithValue("@LoginId", loginId); command.Parameters.AddWithValue("@UserId", userId); command.Parameters.AddWithValue("@OfficeId", officeId); command.Parameters.AddRange(AddPurchaseReorderTypeParameter(details).ToArray()); return(DbOperation.ExecuteNonQuery(command)); } }
public static void Verify(long tranId, int officeId, int userId, long loginId, short verificationStatusId, string reason) { const string sql = "SELECT * FROM transactions.verify_transaction(@TranId::bigint, @OfficeId, @UserId, @LoginId::bigint, @VerificationStatusId::smallint, @Reason);"; using (NpgsqlCommand command = new NpgsqlCommand(sql)) { command.Parameters.AddWithValue("@TranId", tranId); command.Parameters.AddWithValue("@OfficeId", officeId); command.Parameters.AddWithValue("@UserId", userId); command.Parameters.AddWithValue("@LoginId", loginId); command.Parameters.AddWithValue("@VerificationStatusId", verificationStatusId); command.Parameters.AddWithValue("@Reason", reason); DbOperation.ExecuteNonQuery(command); } }
public override void Run() { string extractedPath = this.Config.GetExtractDirectoryDestination(); string path = PathHelper.Combine(extractedPath, "db/patch.sql"); if (File.Exists(path)) { this.OnProgress(new ProgressInfo(this.Description, Labels.PatchingDatabase)); string sql = File.ReadAllText(path, Encoding.UTF8); DbOperation.ExecuteNonQuery(AppUsers.GetCurrentUserDB(), sql); this.OnProgress(new ProgressInfo(this.Description, Labels.PatchedDatabase)); } }
private static void RunInstallScript() { bool run = Conversion.TryCastBoolean(ConfigurationManager.AppSettings["RunInstallScript"]); if (!run) { return; } string script = ConfigurationManager.AppSettings["InstallScriptPath"]; using (NpgsqlCommand command = new NpgsqlCommand(script)) { DbOperation.ExecuteNonQuery(command); } }
public static void InstallReport(string catalog, string menuCode, string parentMenuCode, int level, string menuText, string path) { const string sql = @"INSERT INTO core.menus(menu_text, url, menu_code, level, parent_menu_id) SELECT @MenuText, @Path, @MenuCode, @Level, core.get_menu_id(@ParentMenuCode) WHERE NOT EXISTS(SELECT * FROM core.menus WHERE menu_code=@MenuCode);"; using (NpgsqlCommand command = new NpgsqlCommand(sql)) { command.Parameters.AddWithValue("@MenuCode", menuCode); command.Parameters.AddWithValue("@MenuText", menuText); command.Parameters.AddWithValue("@Path", path); command.Parameters.AddWithValue("@Level", level); command.Parameters.AddWithValue("@ParentMenuCode", parentMenuCode); DbOperation.ExecuteNonQuery(catalog, command); } }
internal static bool TestConnection() { const string sql = "SELECT 1;"; using (NpgsqlCommand command = new NpgsqlCommand(sql)) { try { DbOperation.ExecuteNonQuery(command); return(true); } catch (NpgsqlException) { //Swallow } } return(false); }
public void Install() { _trace.TraceInformation("Start installing SignalR SQL objects"); if (!IsSqlEditionSupported(_connectionString)) { throw new PlatformNotSupportedException(Resources.Error_UnsupportedSqlEdition); } var script = GetType().Assembly.StringResource("Microsoft.AspNet.SignalR.SqlServer.install.sql"); script = script.Replace("SET @SCHEMA_NAME = 'SignalR';", "SET @SCHEMA_NAME = '" + SqlMessageBus.SchemaName + "';"); script = script.Replace("SET @SCHEMA_TABLE_NAME = 'Schema';", "SET @SCHEMA_TABLE_NAME = '" + SchemaTableName + "';"); script = script.Replace("SET @TARGET_SCHEMA_VERSION = 1;", "SET @TARGET_SCHEMA_VERSION = " + SchemaVersion + ";"); script = script.Replace("SET @MESSAGE_TABLE_COUNT = 3;", "SET @MESSAGE_TABLE_COUNT = " + _tableCount + ";"); script = script.Replace("SET @MESSAGE_TABLE_NAME = 'Messages';", "SET @MESSAGE_TABLE_NAME = '" + _messagesTableNamePrefix + "';"); var operation = new DbOperation(_connectionString, script, _trace); operation.ExecuteNonQuery(); _trace.TraceInformation("SignalR SQL objects installed"); }
public void OpenDispose_Basic_Success() { bool[] dbReaderReads = { true, true, false }; var fakeDbReader = A.Fake <IDataReader>(); var fakeDbReaderReadCall = A.CallTo(() => fakeDbReader.Read()); fakeDbReaderReadCall.ReturnsNextFromSequence(dbReaderReads); var fakeDbCommand = A.Fake <IDbCommand>(); A.CallTo(() => fakeDbCommand.ExecuteNonQuery()).Returns(2); A.CallTo(() => fakeDbCommand.ExecuteScalar()).Returns(3); A.CallTo(() => fakeDbCommand.ExecuteReader()).Returns(fakeDbReader); var fakeConnection = A.Fake <IDbConnection>(); var fakeConnectionDisposeCall = A.CallTo(() => fakeConnection.Dispose()); var fakeConnectionOpenCall = A.CallTo(() => fakeConnection.Open()); A.CallTo(() => fakeConnection.CreateCommand()) .Returns(fakeDbCommand); IDbProviderFactory fakeDbProviderFactory = A.Fake <IDbProviderFactory>(); var createConnectionCall = A.CallTo(() => fakeDbProviderFactory.CreateConnection()); createConnectionCall.Returns(fakeConnection); DbOperation dbOperation = new DbOperation(string.Empty, string.Empty, new TraceSource("ss"), fakeDbProviderFactory); dbOperation.ExecuteNonQuery(); int readCount = dbOperation.ExecuteReader(A.Fake <Action <IDataRecord, IDbOperation> >()); Assert.AreEqual(dbReaderReads.Length - 1, readCount); createConnectionCall.MustHaveHappened(Repeated.Exactly.Twice); fakeConnectionOpenCall.MustHaveHappened(Repeated.Exactly.Twice); fakeConnectionDisposeCall.MustHaveHappened(Repeated.Exactly.Twice); }
public static void CreateFlag(int userId, int flagTypeId, string resourceName, string resourceKey, Collection <string> resourceIds) { if (resourceIds == null) { return; } const string sql = "SELECT core.create_flag(@UserId, @FlagTypeId, @Resource, @ResourceKey, @ResourceId);"; foreach (string resourceId in resourceIds) { using (NpgsqlCommand command = new NpgsqlCommand(sql)) { command.Parameters.AddWithValue("@UserId", userId); command.Parameters.AddWithValue("@FlagTypeId", flagTypeId); command.Parameters.AddWithValue("@Resource", resourceName); command.Parameters.AddWithValue("@ResourceKey", resourceKey); command.Parameters.AddWithValue("@ResourceId", resourceId); DbOperation.ExecuteNonQuery(command); } } }
public static bool DeleteRecord(string catalog, string tableSchema, string tableName, string keyColumn, string keyColumnValue) { string sql = "DELETE FROM @TableSchema.@TableName WHERE @KeyColumn=@KeyValue"; using (NpgsqlCommand command = new NpgsqlCommand()) { sql = sql.Replace("@TableSchema", DbFactory.Sanitizer.SanitizeIdentifierName(tableSchema)); sql = sql.Replace("@TableName", DbFactory.Sanitizer.SanitizeIdentifierName(tableName)); sql = sql.Replace("@KeyColumn", DbFactory.Sanitizer.SanitizeIdentifierName(keyColumn)); command.CommandText = sql; command.Parameters.AddWithValue("@KeyValue", keyColumnValue); try { return(DbOperation.ExecuteNonQuery(catalog, command)); } catch (NpgsqlException ex) { Log.Warning("{Sql}/{Exception}.", sql, ex); throw new MixERPException(ex.Message, ex); } } }
public void DbExceptionThrown() { string msg = Guid.NewGuid().ToString("N"); var fakeDbCommand = A.Fake <IDbCommand>(); A.CallTo(() => fakeDbCommand.ExecuteNonQuery()).Throws(new Exception(msg)); A.CallTo(() => fakeDbCommand.ExecuteScalar()).Throws(new Exception(msg)); A.CallTo(() => fakeDbCommand.ExecuteReader()).Throws(new Exception(msg)); var fakeConnection = A.Fake <IDbConnection>(); A.CallTo(() => fakeConnection.CreateCommand()) .Returns(fakeDbCommand); IDbProviderFactory fakeDbProviderFactory = A.Fake <IDbProviderFactory>(); var createConnectionCall = A.CallTo(() => fakeDbProviderFactory.CreateConnection()); createConnectionCall.Returns(fakeConnection); DbOperation dbOperation = new DbOperation(string.Empty, string.Empty, new TraceSource("ss"), fakeDbProviderFactory); try { dbOperation.ExecuteNonQuery(); Assert.Fail("Expected exception was not thrown."); } catch (Exception e) { Assert.AreEqual(msg, e.Message); } try { dbOperation.ExecuteScalar(); Assert.Fail("Expected exception was not thrown."); } catch (Exception e) { Assert.AreEqual(msg, e.Message); } try { dbOperation.ExecuteReader(A.Fake <Action <IDataRecord, IDbOperation> >()); Assert.Fail("Expected exception was not thrown."); } catch (Exception e) { Assert.AreEqual(msg, e.Message); } try { dbOperation.ExecuteNonQueryAsync().Wait(); Assert.Fail("Expected exception was not thrown."); } catch (Exception e) { Assert.AreEqual(msg, e.Message); } }
public override void Run() { string sql = string.Format(@"select store_id,item_id,sku_id from ( select ROW_NUMBER() over(order by store_id desc) row_index ,store_id, item_id,sku_id from storesku_mid where not exists(select 1 from storeskulist where cast(storeskulist.store_id as varchar(50))+ cast(storeskulist.item_id as varchar(50))+ cast(storeskulist.sku_id as varchar(50)) = cast(storesku_mid.store_id as varchar(50))+ cast(storesku_mid.item_id as varchar(50)) + cast(storesku_mid.sku_id as varchar(50))) ) a where row_index>=0 and row_index<=100 " ); //string sql = "select * from storesku_mid where id ='8'"; DataTable dataTable = BusinessDbUtil.GetDataTable(sql); if (dataTable != null && dataTable.Rows.Count > 0) { string app_key = ConfigUtil.App_key; string app_secret = ConfigUtil.App_secret; string iposApiUrl = ConfigUtil.IposApiUrl; ITopClient topClient = new DefaultTopClient(iposApiUrl, app_key, app_secret, "json"); RetailIfashionSkuinfoGetRequest retailIfashionSkuinfoGetRequest = new RetailIfashionSkuinfoGetRequest(); DbOperation dbOperation = new DbOperation(ConfigUtil.ConnectionString); string strCmd = "select ID,store_id,item_id,sku_id,sku_bar_code,shop_name,seller_nick,item_title,item_pic,item_price,color,size,short_url,current_amount from storeskulist where 1=0"; DataTable dataTable2 = dbOperation.ExecuteQuery(strCmd).Tables[0]; dataTable2.TableName = "storeskulist"; foreach (DataRow dataRow in dataTable.Rows) { string accessToken_QT = InvoicesManage.GetAccessToken_QT(dataRow["store_id"].ToString()); retailIfashionSkuinfoGetRequest.SkuId = dataRow["sku_id"].ToString(); retailIfashionSkuinfoGetRequest.ItemId = dataRow["item_id"].ToString(); RetailIfashionSkuinfoGetResponse retailIfashionSkuinfoGetResponse = topClient.Execute <RetailIfashionSkuinfoGetResponse>(retailIfashionSkuinfoGetRequest, accessToken_QT); if (retailIfashionSkuinfoGetResponse != null && retailIfashionSkuinfoGetResponse.Result.SuccessAndHasValue && retailIfashionSkuinfoGetResponse.Result.Data != null) { LogUtil.WriteInfo(this, "SetSearchOnlineGoodsInfoByAccurate : Body 成功记录", retailIfashionSkuinfoGetResponse.Body); RetailIfashionSkuinfoGetResponse.SkuInfoDomain data = retailIfashionSkuinfoGetResponse.Result.Data; DataTable dataTable3 = JsonHelper.SetDataTableFromQT <RetailIfashionSkuinfoGetResponse.SkuInfoDomain>(data, "storeskulist"); foreach (DataRow dataRow2 in dataTable3.Rows) { string sqlFlag = string.Format(@"select top 1 id from storeskulist where cast(store_id as varchar(50)) ='{0}' and cast(item_id as varchar(50))='{1}' and cast(sku_id as varchar(50)) ='{2}' ", dataRow2["store_id"].ToString(), dataRow2["item_id"].ToString(), dataRow2["sku_id"].ToString()); var Flag = BusinessDbUtil.ExecuteScalar(sqlFlag); //如果存在那就不能重复插入 if (Flag != null && Flag.ToString() != "") { continue; } if (dataRow2["item_id"] != null && dataRow2["item_id"].ToString() != "" && dataRow2["color"] != null && dataRow2["color"].ToString() != "" && (dataRow2["size"] != null & dataRow2["size"].ToString() != "")) { StringBuilder stringBuilder = new StringBuilder(); //先插入商品档案 string str = string.Format(@"IF NOT EXISTS(SELECT 1 FROM shangpin WHERE SPDM='{0}') BEGIN INSERT INTO shangpin(SPDM,SPMC,DWMC,fjsx1,fjsx2,fjsx3,fjsx4,fjsx5,fjsx6,fjsx7,fjsx8,fjsx9,fjsx10,BZHU,BZSJ,SJ1,SJ2,SJ3,SJ4,BZJJ,JJ1,JJ2 ,TZSY,BYZD11,BYZD1,BYZD2,BYZD12,BYZD13,BYZD9,BYZD10,JSJM,BYZD4,BYZD5,BYZD3,BZDW,BYZD14,BYZD15) VALUES('{0}','{1}','未定义','000','000','000','000','000','000','000','000','000','000','蜻蜓平台同步','{2}','{2}','{2}','{2}','{2}','{2}','{2}','{2}' ,0,0,0,2,1,1,0,0,0,'000','000','000',0,GETDATE(),GETDATE()) END ", dataRow2["item_id"].ToString(), dataRow2["item_title"].ToString(), Convert.ToInt32(dataRow2["item_price"]) / 100); stringBuilder.Append(str + "\n"); //颜色档案 str = string.Format(@"IF NOT EXISTS(SELECT 1 FROM dbo.GUIGE1 WHERE GGMC='{0}') BEGIN DECLARE @ID_GUIGE1 INT = 0 SELECT @ID_GUIGE1 = isnull(max(ID_VALUE),0) FROM ID_CODEID WHERE ID_NAME = 'GUIGE1_QT' IF @ID_GUIGE1 = 0 BEGIN insert into GUIGE1(GGDM,GGMC,TYBJ) values('QT_' + CAST(@ID_GUIGE1 AS VARCHAR(6)),'{0}',0) INSERT INTO ID_CODEID (ID_NAME, ID_VALUE) VALUES ('GUIGE1_QT', 1) END ELSE BEGIN insert into GUIGE1(GGDM,GGMC,TYBJ) values('QT_' + CAST(@ID_GUIGE1 AS VARCHAR(6)),'{0}',0) UPDATE ID_CODEID SET ID_VALUE = ID_VALUE + 1 WHERE ID_NAME = 'GUIGE1_QT' END END " , dataRow2["color"].ToString()); stringBuilder.Append(str + "\n"); //尺码档案 str = string.Format(@"IF NOT EXISTS(SELECT 1 FROM dbo.GUIGE2 WHERE GGMC='{0}') BEGIN DECLARE @GGWZ1 INT = 1,@GGWZ2 INT =1 DECLARE @flag INT = 0 DECLARE @ID_GUIGE2 INT = 0 SELECT @ID_GUIGE2 = isnull(max(ID_VALUE),0) FROM ID_CODEID WHERE ID_NAME = 'GUIGE2_QT' IF @ID_GUIGE2 = 0 BEGIN WHILE @GGWZ1 < 11 BEGIN WHILE @GGWZ2 < 11 BEGIN IF NOT EXISTS(SELECT 1 FROM GUIGE2 WHERE GGWZ1=@GGWZ1 AND GGWZ2=@GGWZ2) BEGIN SET @flag = 1 insert into GUIGE2(GGDM,GGMC,TYBJ,GGWZ1,GGWZ2) values('QT_' + CAST(@ID_GUIGE2 AS VARCHAR(6)),'{0}',0,@GGWZ1,@GGWZ2) BREAK END SET @GGWZ2 = @GGWZ2 + 1 END IF @flag =1 BEGIN BREAK END ELSE BEGIN SET @GGWZ1 = @GGWZ1 + 1 SET @GGWZ2 = 1 END END INSERT INTO ID_CODEID (ID_NAME, ID_VALUE) VALUES ('GUIGE2_QT', 1) END ELSE BEGIN WHILE @GGWZ1<11 BEGIN WHILE @GGWZ2<11 BEGIN IF NOT EXISTS(SELECT 1 FROM GUIGE2 WHERE GGWZ1=@GGWZ1 AND GGWZ2=@GGWZ2) BEGIN SET @flag = 1 insert into GUIGE2(GGDM,GGMC,TYBJ,GGWZ1,GGWZ2) values('QT_' + CAST(@ID_GUIGE2 AS VARCHAR(6)),'{0}',0,@GGWZ1,@GGWZ2) BREAK END SET @GGWZ2 = @GGWZ2 + 1 END IF @flag =1 BEGIN BREAK END ELSE BEGIN SET @GGWZ1 = @GGWZ1 + 1 SET @GGWZ2 = 1 END END UPDATE ID_CODEID SET ID_VALUE = ID_VALUE + 1 WHERE ID_NAME = 'GUIGE2_QT' END END " , dataRow2["size"].ToString()); stringBuilder.Append(str + "\n"); //插入商品规则1 str = string.Format(@" IF NOT EXISTS(SELECT 1 FROM SPGG1 INNER JOIN dbo.GUIGE1 ON SPGG1.GGDM= GUIGE1.GGDM WHERE SPDM='{0}' AND dbo.GUIGE1.GGMC='{1}') BEGIN DECLARE @GGDM_GUIGE1 VARCHAR(10) = '' SELECT TOP 1 @GGDM_GUIGE1 = GGDM from GUIGE1 WHERE GGMC = '{1}' IF @GGDM_GUIGE1 <> '' INSERT INTO SPGG1(SPDM, GGDM, BYZD2, BYZD3) VALUES('{0}', '' + @GGDM_GUIGE1 + '', '000', '1') END " , dataRow2["item_id"].ToString(), dataRow2["color"].ToString()); stringBuilder.Append(str + "\n"); //插入商品规则2 str = string.Format(@"IF NOT EXISTS(SELECT 1 FROM SPGG2 INNER JOIN dbo.GUIGE2 ON SPGG2.GGDM = GUIGE2.GGDM WHERE SPDM='{0}' AND dbo.GUIGE2.GGMC='{1}') BEGIN DECLARE @GGDM_GUIGE2 VARCHAR(10)='' SELECT TOP 1 @GGDM_GUIGE2=GGDM from GUIGE2 WHERE GGMC='{1}' IF @GGDM_GUIGE2<>'' INSERT INTO SPGG2(SPDM,GGDM,BYZD3) VALUES('{0}',''+@GGDM_GUIGE2+'','1') END " , dataRow2["item_id"].ToString(), dataRow2["size"].ToString()); stringBuilder.Append(str + "\n"); // 插入到TMDZB 第一次插入SPTM 针对的是 sku_id str = string.Format(@"IF NOT EXISTS(SELECT 1 FROM dbo.TMDZB WHERE SPTM='{0}') BEGIN DECLARE @GG1DM_TMDZB VARCHAR(50)= '' DECLARE @GG2DM_TMDZB VARCHAR(50)= '' SELECT TOP 1 @GG1DM_TMDZB=GGDM from dbo.GUIGE1 WHERE GGMC='{2}' SELECT TOP 1 @GG2DM_TMDZB=GGDM from dbo.GUIGE2 WHERE GGMC='{3}' INSERT INTO TMDZB(SPTM,SPDM,GG1DM,GG2DM) VALUES('{0}','{1}',''+@GG1DM_TMDZB+'',''+@GG2DM_TMDZB+'') END " , dataRow2["sku_id"].ToString(), dataRow2["item_id"].ToString(), dataRow2["color"].ToString(), dataRow2["size"].ToString()); // 插入到TMDZB 第一次插入SPTM 针对的是 short_url str = string.Format(@"IF NOT EXISTS(SELECT 1 FROM dbo.TMDZB WHERE SPTM='{0}') BEGIN DECLARE @GG1DM_TMDZB VARCHAR(50)= '' DECLARE @GG2DM_TMDZB VARCHAR(50)= '' SELECT TOP 1 @GG1DM_TMDZB=GGDM from dbo.GUIGE1 WHERE GGMC='{2}' SELECT TOP 1 @GG2DM_TMDZB=GGDM from dbo.GUIGE2 WHERE GGMC='{3}' INSERT INTO TMDZB(SPTM,SPDM,GG1DM,GG2DM) VALUES('{0}','{1}',''+@GG1DM_TMDZB+'',''+@GG2DM_TMDZB+'') END " , dataRow["short_url"].ToString(), dataRow["item_id"].ToString(), dataRow["color"].ToString(), dataRow["size"].ToString()); stringBuilder.Append(str + "\n"); stringBuilder.Append(str + "\n"); //插入客户代码 str = string.Format(@"IF NOT EXISTS(SELECT 1 FROM kehu WHERE khdm='{0}') BEGIN INSERT INTO KEHU(KHDM,KHMC,LBDM,QDDM,QYDM,YGDM,BYZD2,JGSD,TJSD,ZK,CKDM,XZDM,TZSY,BYZD25) VALUES('{0}','{0}','000','000','000','000','1','BZSJ','BZSJ',1,'{0}','2','0',getdate()) END " , dataRow2["store_id"].ToString()); stringBuilder.Append(str + "\n"); // 插入仓库代码 str = string.Format(@"IF NOT EXISTS(SELECT 1 FROM dbo.CANGKU WHERE CKDM='{0}') BEGIN INSERT INTO CANGKU(CKDM,CKMC,QDDM,YGDM,LBDM,QYDM,XZDM,DH2,JGSD,ZK,TJSD,TZSY) VALUES('{0}','{0}','000','000','000','000','1','1','BZSJ',1,'BZSJ','0') END " , dataRow2["store_id"].ToString()); // 插入仓库库位 str = string.Format(@" IF NOT EXISTS(SELECT 1 FROM dbo.CKKW WHERE CKDM='{0}') BEGIN insert into CKKW(CKDM,KWDM,INUSE,INZK,OUTUSE,OUTZK,BYZD2) values('{0}','000','0',1,'0','1',1) END ", dataRow2["store_id"].ToString()); stringBuilder.Append(str + "\n"); stringBuilder.Append(str + "\n"); //插入供货商代码 str = string.Format(@"IF NOT EXISTS(SELECT 1 FROM dbo.GONGHUOSHANG WHERE GHSMC='{0}') BEGIN DECLARE @ID_GONGHUOSHANG INT = 0 SELECT @ID_GONGHUOSHANG = isnull(max(ID_VALUE),0) FROM ID_CODEID WHERE ID_NAME = 'GONGHUOSHANG_QT' IF @ID_GONGHUOSHANG = 0 BEGIN INSERT INTO GONGHUOSHANG(GHSDM,GHSMC,XZDM,QDDM,LBDM,QYDM,YGDM,DH2,JGSD,ZK,FPLX,TZSY,CreateDate) VALUES('QT_GHSDM' + CAST(@ID_GONGHUOSHANG AS VARCHAR(6)),'{0}','0','000','000','000','000','1','BZSJ','1','000','0',getdate()) INSERT INTO ID_CODEID (ID_NAME, ID_VALUE) VALUES ('GONGHUOSHANG_QT', 1) END ELSE BEGIN INSERT INTO GONGHUOSHANG(GHSDM,GHSMC,XZDM,QDDM,LBDM,QYDM,YGDM,DH2,JGSD,ZK,FPLX,TZSY,CreateDate) VALUES('QT_GHSDM' + CAST(@ID_GONGHUOSHANG AS VARCHAR(6)),'{0}','0','000','000','000','000','1','BZSJ','1','000','0',getdate()) UPDATE ID_CODEID SET ID_VALUE = ID_VALUE + 1 WHERE ID_NAME = 'GONGHUOSHANG_QT' END END " , dataRow2["shop_name"].ToString()); stringBuilder.Append(str + "\n"); //插入SPKCB //str = string.Format(@"DECLARE @GG1DM VARCHAR(50) = '',@GG2DM VARCHAR(50) ='' // SELECT TOP 1 @GG1DM=GGDM FROM dbo.GUIGE1 WHERE GGMC='{2}' // SELECT TOP 1 @GG2DM=GGDM FROM dbo.GUIGE2 WHERE GGMC='{3}' // IF NOT EXISTS(SELECT 1 FROM dbo.SPKCB WHERE SPDM+GG1DM+GG2DM='{1}'+''+@GG1DM+''+''+@GG2DM+'' AND KWDM='000' AND CKDM='{0}') // BEGIN // INSERT INTO SPKCB(CKDM,KWDM,SPDM,GG1DM,GG2DM,SL) VALUES('{0}','000','{1}',''+@GG1DM+'',''+@GG2DM+'','{4}') // END // ELSE // BEGIN // UPDATE SPKCB SET SL = SL + {4} WHERE CKDM = '{0}' AND SPDM='{1}' AND GG1DM = ''+@GG1DM+'' AND GG2DM =''+@GG2DM+'' END ", // dataRow["store_id"].ToString(), dataRow2["item_id"].ToString(), dataRow2["color"].ToString(), dataRow2["size"].ToString(), dataRow2["current_amount"].ToString()); //stringBuilder.Append(str + "\n"); if (stringBuilder.ToString() != "") { try { dbOperation.ExecuteNonQuery(stringBuilder.ToString()); } catch (Exception ex) { LogUtil.WriteError(this, "插入商品基础档单 : sql", "sql : " + stringBuilder.ToString() + "错误日志 :" + ex.Message); } } #region //新增库存调整单 if (dataRow2["current_amount"].ToString() != "0") { try { string TableName = "CKTZD"; string DJBH = ""; var NoticesName = string.Empty; Dictionary <string, DataTable> dic = new Dictionary <string, DataTable>(); Dictionary <string, DataTable> dicMX = new Dictionary <string, DataTable>(); List <Dictionary <string, DataTable> > BusinessList = new List <Dictionary <string, DataTable> >(); List <YanShouInfo> ListNameInfoFACHU = new List <YanShouInfo>(); List <YanShouInfo> ListNameInfoYANSHOU = new List <YanShouInfo>(); var exists = false; lock (Onlock) { if (!exists) { Regulation shopinfo = new Regulation(); shopinfo.DM2 = dataRow2["store_id"].ToString(); shopinfo.SHR = "QT"; shopinfo.DM1 = "999"; shopinfo.RQ = DateTime.Now.ToShortDateString(); shopinfo.YDJH = dataRow2["store_id"].ToString() + dataRow2["item_id"].ToString() + dataRow2["sku_id"].ToString(); shopinfo.BZ = "蜻蜓平台对接-库存初始化"; shopinfo.JE = Math.Ceiling((Convert.ToDouble(dataRow2["item_price"]) / 100) * Convert.ToDouble(dataRow2["current_amount"])).ToString(); shopinfo.SL = dataRow2["current_amount"].ToString(); shopinfo.ZDR = "QT"; dic = DataTableBusiness.SetBusinessDataTable <Regulation>(shopinfo, TableName, "Regulation", TableName, out DJBH); dicMX = DataTableBusiness.SetEntryOrderDetail_QT_2(DJBH, TableName, dataRow2, dataRow2["store_id"].ToString()); YanShouInfo infoYS = new YanShouInfo(); try { infoYS = InvoicesManage.GetYsInfo(DJBH, TableName, "P_API_Oper_CKTZD_SH", "QT"); } catch (System.Exception ex) { LogUtil.WriteError(this, "库存调整单 执行失败P_API_Oper_CKTZD_SH ;DJBH:" + DJBH); } ListNameInfoYANSHOU.Add(infoYS); } if (dic.Count > 0 || dicMX.Count > 0) { if (dic != null && dicMX != null) { BusinessList.Add(dic); BusinessList.Add(dicMX); } } if (BusinessList.Count > 0) { var resultList = DataTableBusiness.SavaBusinessData_SqlParameter(BusinessList, ListNameInfoYANSHOU); if (resultList) { sql = string.Format("UPDATE " + TableName + " SET JE=(SELECT SUM(JE) FROM " + TableName + "MX WHERE DJBH='{0}')" + ",SL=(SELECT SUM(SL) FROM " + TableName + "MX WHERE DJBH='{0}')WHERE DJBH='{0}'", DJBH); BusinessDbUtil.ExecuteNonQuery(sql); LogUtil.WriteInfo(this, string.Format(@"ERP业务单据{0}创建成功!对应的电商系统的调整单号:{1}保存成功", DJBH, DJBH), string.Format(@"ERP业务单据{0}创建成功!对应的电商系统的调整单号:{1}保存成功", DJBH, DJBH)); } else { LogUtil.WriteError(this, "仓库调整单保存失败"); } } else { LogUtil.WriteError(this, "仓库调整单保存失败"); } } } catch (Exception ex) { LogUtil.WriteError(this, "仓库调整单保存失败" + ex.Message); } } #endregion } DataRow dataRow3 = dataTable2.NewRow(); dataRow3.BeginEdit(); foreach (DataColumn dataColumn in dataTable2.Columns) { if (dataColumn.ColumnName.ToString() != "ID") { dataRow3[dataColumn.ColumnName] = dataRow2[dataColumn.ColumnName]; } } dataRow3.EndEdit(); dataTable2.Rows.Add(dataRow3); } } else { LogUtil.WriteInfo(this, "SetSearchOnlineGoodsInfoByAccurate : Body", "SetSearchOnlineGoodsInfoByAccurate - Body : " + retailIfashionSkuinfoGetResponse.Body); } } try { if (dataTable2.Rows.Count > 0 && dbOperation.SqlBulkCopy(dataTable2, "storeskulist")) { LogUtil.WriteInfo(this, "新增成功", "新增商品档案成功"); } } catch (Exception ex) { LogUtil.WriteError(this, "error:" + ex.Message); } } }
public static bool UpdateRecord(string catalog, int userId, string tableSchema, string tableName, Collection <KeyValuePair <string, object> > data, string keyColumn, string keyColumnValue, string imageColumn, string[] exclusion) { if (data == null) { return(false); } string columns = string.Empty; int counter = 0; //Adding the current user to the column collection. KeyValuePair <string, object> auditUserId = new KeyValuePair <string, object>("audit_user_id", userId); data.Add(auditUserId); foreach (KeyValuePair <string, object> pair in data) { if (!exclusion.Contains(pair.Key.ToUpperInvariant())) { counter++; if (counter.Equals(1)) { columns += DbFactory.Sanitizer.SanitizeIdentifierName(pair.Key) + "=@" + pair.Key; } else { columns += ", " + DbFactory.Sanitizer.SanitizeIdentifierName(pair.Key) + "=@" + pair.Key; } } } string sql = "UPDATE @TableSchema.@TableName SET " + columns + ", audit_ts=NOW() WHERE @KeyColumn=@KeyValue;"; using (NpgsqlCommand command = new NpgsqlCommand()) { sql = sql.Replace("@TableSchema", DbFactory.Sanitizer.SanitizeIdentifierName(tableSchema)); sql = sql.Replace("@TableName", DbFactory.Sanitizer.SanitizeIdentifierName(tableName)); sql = sql.Replace("@KeyColumn", DbFactory.Sanitizer.SanitizeIdentifierName(keyColumn)); command.CommandText = sql; foreach (KeyValuePair <string, object> pair in data) { if (!exclusion.Contains(pair.Key.ToUpperInvariant())) { if (pair.Value == null) { command.Parameters.AddWithValue("@" + pair.Key, DBNull.Value); } else { if (pair.Key.Equals(imageColumn)) { FileStream stream = new FileStream(pair.Value.ToString(), FileMode.Open, FileAccess.Read); try { using (BinaryReader reader = new BinaryReader(new BufferedStream(stream))) { byte[] byteArray = reader.ReadBytes(Convert.ToInt32(stream.Length)); command.Parameters.AddWithValue("@" + pair.Key, byteArray); } } finally { stream.Close(); } } else { command.Parameters.AddWithValue("@" + pair.Key, pair.Value); } } } } command.Parameters.AddWithValue("@KeyValue", keyColumnValue); try { return(DbOperation.ExecuteNonQuery(catalog, command)); } catch (NpgsqlException ex) { Log.Warning("{Sql}/{Data}/{Exception}.", sql, data, ex); throw new MixERPException(ex.Message, ex, ex.ConstraintName); } } }
private void ws_OnMessage(object obj, MessageEventArgs args) { if (args.Data == "\n") { try { this.ws.Send("\n"); this.throwMessage("PONG"); } catch (Exception) { this.connected = false; } } else { this.throwMessage(string.Format("Message from Server: {0}", args.Data)); if (args.Data.IndexOf("RECEIPT") >= 0) { this.subScribeSuccessed = true; this.throwMessage("Subscribe successed."); } if (args.Data.IndexOf("LOT_JUDGEMENT") >= 0) { //»ØÖ´id string ackid = args.Data.Substring(args.Data.IndexOf("ack:") + 4, args.Data.IndexOf("subscription:") - 5 - args.Data.IndexOf("ack:")); int index = args.Data.IndexOf("{\"LOT_JUDGEMENT"); string jsonStr = args.Data.Substring(index, (args.Data.Length - index) - 1); string fileName = string.Format(@"{0}\{1}_{2}.json", this.configuration.MessageInDirectory, DateTime.Now.ToString("yyyyMMddHHmmss"), Guid.NewGuid().ToString()); EventService.AppendToLogFileToAbsFile(fileName, args.Data); IList <string> messages = new List <string>(); Lot lot = new Lot(); DbOperation dbops = new DbOperation(); try { //lot = LotService.ReadLotFromJson(this.configuration.OSATID, jsonStr, messages); //foreach (string str3 in messages) //{ // this.throwMessage(str3); //} //LotService.SaveLotAndInformQA_AND_PE(lot); string insertLogSQL = @"INSERT INTO [dbo].[LOTSImportLogs] ([FilePath] ,[CreateTime] ,[FormatStatus] ,[osatID],[Type]) VALUES ('{0}' ,GETDATE() ,'Pending' ,'{1}' ,'{2}')"; dbops.ExecuteNonQuery(string.Format(insertLogSQL, fileName, this.configuration.OSATID, this.configuration.Type)); //·¢ËÍ»ØÖ´id if (!string.IsNullOrEmpty(ackid)) { string ackData = "ACK\nid:" + ackid + "\n\n\0"; this.ws.Send(ackData); this.throwMessage(ackData); } } catch (Exception) { this.throwMessage(string.Format("LOT_JUDGEMENT process failed. filename is {0}", fileName)); foreach (string str4 in messages) { this.throwMessage(str4); } try { string insertSQL = @"INSERT INTO [dbo].[EMAILS] ([EmailID] ,[Sender] ,[Recipients] ,[Subject] ,[Body] ,[Priority] ,[State] ,[NextTryTime] ,[TryTime]) VALUES (NEWID() ,'*****@*****.**' ,'*****@*****.**' ,'{0}' ,'{1}' ,0 ,'Unsend' ,'{2}' ,10) "; string insertLogSQL = @"INSERT INTO [dbo].[LOTSImportLogs] ([FilePath] ,[CreateTime] ,[FormatStatus] ,[osatID],[Type]) VALUES ('{0}' ,GETDATE() ,'Pending' ,'{1},'{2}')"; dbops.ExecuteNonQuery(string.Format(insertSQL, this.configuration.OSATID + " Import lot judgement failed-" + lot.AutoJudgeResult + '-' + lot.SubconLot, this.configuration.OSATID + " Import lot judgement failed, filename is:" + fileName, DateTime.Now.AddSeconds(30.0))); dbops.ExecuteNonQuery(string.Format(insertLogSQL, fileName, this.configuration.OSATID, this.configuration.Type)); } catch (Exception ex) { this.throwMessage("Send failed mail error:" + ex.Message); } } } } }
public void clientCallBack() { string app_key = ConfigUtil.App_key; string app_secret = ConfigUtil.App_secret; TmcClient tmcClient = new TmcClient(app_key, app_secret, "default"); LogUtil.WriteInfo(this, "clientCallBack : 消息监听中", "appkey:" + app_key + " ;appsecret : " + app_secret); tmcClient.OnMessage += delegate(object s, MessageArgs e) { try { DbOperation dbOperation = new DbOperation(ConfigUtil.ConnectionString); LogUtil.WriteInfo(this, "e.Message.Topic:", e.Message.Topic); LogUtil.WriteInfo(this, "e.Message.Content:", e.Message.Content); //消息通知-商品基础消息创建 "taobao_ifashion_ItemInfoCreate" if (e.Message.Topic == "taobao_ifashion_ItemInfoCreate") { try { QT_GoodsInfo qT_GoodsInfo = JsonParser.FromJson <QT_GoodsInfo>(e.Message.Content); string text = string.Format(@"if not exists (select 1 from storesku_mid WITH(NOLOCK) where store_id='{0}' and item_id='{1}' and sku_id='{2}' ) begin insert into storesku_mid(store_id,item_id,sku_id) values('{0}','{1}','{2}') end" , qT_GoodsInfo.store_id, qT_GoodsInfo.item_id, qT_GoodsInfo.sku_id ); dbOperation.ExecuteNonQuery(text); } catch (Exception ex) { LogUtil.WriteError(this, "e.Message.Topic: taobao_ifashion_ItemInfoCreate ", "入参数据传入错误,请检查数据是否正确." + ex.Message); } } //消息通知-库存变更 "taobao_ifashion_ItemAmountChanged" //{"amount":2,"item_id":604769130652,"msg_id":"300101a2c3f8445dade85057db0a31fc","current_amount":2,"store_id":947,"sku_id":4407918687796,"order_id":1064001,"type":"qt-stock-in"} else if (e.Message.Topic == "taobao_ifashion_ItemAmountChanged") { try { lock (TmcClientInstance.lockobject) { QT_GoodsInfo qT_GoodsInfo = JsonParser.FromJson <QT_GoodsInfo>(e.Message.Content); string text = string.Format(@"if not exists (select 1 from ItemAmountChanged WITH(NOLOCK) where item_id='{0}' and store_id='{4}' and sku_id='{5}' and order_id='{6}') begin insert into ItemAmountChanged(amount,current_amount,item_id,store_id,sku_id,order_id,order_type) values('{1}','{2}','{3}','{4}','{5}','{6}','{7}') end" , qT_GoodsInfo.item_id, qT_GoodsInfo.amount, qT_GoodsInfo.current_amount, qT_GoodsInfo.item_id, qT_GoodsInfo.store_id, qT_GoodsInfo.sku_id, qT_GoodsInfo.order_id, qT_GoodsInfo.type ); dbOperation.ExecuteNonQuery(text); } } catch (Exception ex) { LogUtil.WriteError(this, "e.Message.Topic: taobao_ifashion_ItemAmountChanged ", "入参数据传入错误,请检查数据是否正确." + ex.Message); } } //消息通知-创建单据 "taobao_ifashion_OrderCreate" else if (e.Message.Topic == "taobao_ifashion_OrderCreate") { try { lock (TmcClientInstance.lockobject) { QT_GoodsInfo qT_GoodsInfo2 = JsonParser.FromJson <QT_GoodsInfo>(e.Message.Content); string text = string.Format(@"if not exists (select 1 from OrderCreate WITH(NOLOCK) where storeId='{0}' and orderId='{1}' ) begin insert into OrderCreate(storeId,orderId,orderType) values('{0}','{1}','{2}') end", qT_GoodsInfo2.store_id, qT_GoodsInfo2.order_id, qT_GoodsInfo2.type ); dbOperation.ExecuteNonQuery(text); } } catch (Exception ex) { LogUtil.WriteError(this, "e.Message.Topic: taobao_ifashion_OrderCreate ", "入参数据传入错误,请检查数据是否正确." + ex.Message + " \n e.Message.Content : " + e.Message.Content); } } } catch (Exception ex2) { LogUtil.WriteError(this, "获取消息失败:message:" + ex2.Message); Console.WriteLine(ex2.StackTrace); e.Fail(); } }; tmcClient.Connect("ws://mc.api.taobao.com/"); Thread.Sleep(2000); }
internal static void AddPgTypeDefinitionFunction() { string sql = FileHelper.ReadSqlResource("pg_catalog.pg_get_typedef.sql"); DbOperation.ExecuteNonQuery(new NpgsqlCommand(sql)); }