public GetSettlementByConditionResponse GetHiltiList(WMS_HiltibjSettled wc, DateTime?StartSettlementdate, DateTime?EndSettlementdate, string DateTime1, int Cid, out int rowCount) { string Mess = string.Empty; rowCount = 0; GetSettlementByConditionResponse response = new GetSettlementByConditionResponse(); try { DataTable dt = new DataTable(); #region 查询条件 //string sb = string.Empty; StringBuilder sb = new StringBuilder(); if (Cid != 0) { sb.Append(" and c.WarehouseID=" + Cid + ""); } if (StartSettlementdate != null) { sb.Append(" AND c.CreateTime >='").Append(StartSettlementdate.DateTimeToString("d")).Append(" 00:00:00'"); } if (EndSettlementdate != null) { sb.Append(" AND c.CreateTime <='").Append(EndSettlementdate.DateTimeToString("d")).Append(" 23:59:59'"); } if (!string.IsNullOrEmpty(wc.SettlementNumber)) { sb.Append(" and c.SettlementNumber like '%" + wc.SettlementNumber.Trim() + "%' "); } if (!string.IsNullOrEmpty(DateTime1)) { sb.Append(" and c.str1 ='" + DateTime1.Trim() + "' "); } #endregion int tempRowCount = 0; DbParam[] dbParams = new DbParam[] { new DbParam("@Where", DbType.String, sb.ToString(), ParameterDirection.Input), new DbParam("@PageIndex", DbType.Int32, wc.PageIndex, ParameterDirection.Input), new DbParam("@PageSize", DbType.Int32, wc.PageSize, ParameterDirection.Input), new DbParam("@RowCount", DbType.Int32, tempRowCount, ParameterDirection.Output) }; DataSet ds = ExecuteDataSet("Proc_GetHiltiByCondition", dbParams); rowCount = (int)dbParams[3].Value; response.HilSettlementCollection = ds.Tables[0].ConvertToEntityCollection <WMS_HiltibjSettled>(); return(response); } catch (Exception ex) { return(null); } }
public DataTable ExportSettlement(WMS_HiltibjSettled wc, DateTime?StartSettlementdate, DateTime?EndSettlementdate, string DateTime1, out int rowCount) { string Mess = string.Empty; rowCount = 0; GetSettlementByConditionResponse response = new GetSettlementByConditionResponse(); try { DataTable dt = new DataTable(); #region 查询条件 //string sb = string.Empty; StringBuilder sb = new StringBuilder(); if (StartSettlementdate != null) { sb.Append(" AND c.CreateTime >='").Append(StartSettlementdate.DateTimeToString("d")).Append(" 00:00:00'"); } if (EndSettlementdate != null) { sb.Append(" AND c.CreateTime <='").Append(EndSettlementdate.DateTimeToString("d")).Append(" 23:59:59'"); } if (!string.IsNullOrEmpty(wc.SettlementNumber)) { sb.Append(" and c.SettlementNumber like '%" + wc.SettlementNumber.Trim() + "%' "); } if (!string.IsNullOrEmpty(DateTime1)) { sb.Append(" and c.str1 ='" + DateTime1.Trim() + "' "); } #endregion int tempRowCount = 0; DbParam[] dbParams = new DbParam[] { new DbParam("@Where", DbType.String, sb.ToString(), ParameterDirection.Input), new DbParam("@PageIndex", DbType.Int32, wc.PageIndex, ParameterDirection.Input), new DbParam("@PageSize", DbType.Int32, wc.PageSize, ParameterDirection.Input), new DbParam("@RowCount", DbType.Int32, tempRowCount, ParameterDirection.Output) }; return(base.ExecuteDataTable("Proc_GetHiltiByCondition", dbParams)); } catch (Exception ex) { return(null); } }
/// <summary> /// 20190315 /// </summary> /// <param name="cb"></param> /// <returns></returns> public Response <bool> AddInfoHiltiNewTab(WMS_HiltibjSettled cb) { Response <bool> response = new Response <bool>(); try { new SettlementAccessor().AddInfoHiltiNewTab(cb); response.IsSuccess = true; } catch (Exception ex) { response.IsSuccess = false; response.ErrorCode = ErrorCode.Technical; } return(response); }
public void AddInfoHiltiNewTab(WMS_HiltibjSettled cb) { using (SqlConnection conn = new SqlConnection(BaseAccessor._dataBase.ConnectionString)) { SqlCommand cmd = new SqlCommand("Proc_AddHilti", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@WarehouseID", cb.WarehouseID); cmd.Parameters[0].SqlDbType = SqlDbType.BigInt; cmd.Parameters.AddWithValue("@WarehouseName", cb.WarehouseName); cmd.Parameters[1].SqlDbType = SqlDbType.NVarChar; cmd.Parameters.AddWithValue("@SettlementNumber", cb.SettlementNumber); cmd.Parameters[2].SqlDbType = SqlDbType.NVarChar; cmd.Parameters.AddWithValue("@OutsourcingTotalSum", cb.OutsourcingTotalSum); cmd.Parameters[3].SqlDbType = SqlDbType.Decimal; cmd.Parameters.AddWithValue("@OutsourcingAveragecost", cb.OutsourcingAveragecost); cmd.Parameters[4].SqlDbType = SqlDbType.Decimal; cmd.Parameters.AddWithValue("@Number1", cb.Number1); cmd.Parameters[5].SqlDbType = SqlDbType.BigInt; cmd.Parameters.AddWithValue("@Number2", cb.Number2); cmd.Parameters[6].SqlDbType = SqlDbType.BigInt; cmd.Parameters.AddWithValue("@Number3", cb.Number3); cmd.Parameters[7].SqlDbType = SqlDbType.BigInt; cmd.Parameters.AddWithValue("@Number4", cb.Number4); cmd.Parameters[8].SqlDbType = SqlDbType.BigInt; cmd.Parameters.AddWithValue("@OperationCost1", cb.OperationCost1); cmd.Parameters[9].SqlDbType = SqlDbType.Decimal; cmd.Parameters.AddWithValue("@OperationCost2", cb.OperationCost2); cmd.Parameters[10].SqlDbType = SqlDbType.Decimal; cmd.Parameters.AddWithValue("@OperationCost3", cb.OperationCost3); cmd.Parameters[11].SqlDbType = SqlDbType.Decimal; cmd.Parameters.AddWithValue("@OperationCost4", cb.OperationCost4); cmd.Parameters[12].SqlDbType = SqlDbType.Decimal; cmd.Parameters.AddWithValue("@OperationTotal", cb.OperationTotal); cmd.Parameters[13].SqlDbType = SqlDbType.Decimal; cmd.Parameters.AddWithValue("@Num1", cb.Num1); cmd.Parameters[14].SqlDbType = SqlDbType.BigInt; cmd.Parameters.AddWithValue("@Num2", cb.Num2); cmd.Parameters[15].SqlDbType = SqlDbType.BigInt; cmd.Parameters.AddWithValue("@Num3", cb.Num3); cmd.Parameters[16].SqlDbType = SqlDbType.BigInt; cmd.Parameters.AddWithValue("@Num4", cb.Num4); cmd.Parameters[17].SqlDbType = SqlDbType.BigInt; cmd.Parameters.AddWithValue("@Num5", cb.Num5); cmd.Parameters[18].SqlDbType = SqlDbType.BigInt; cmd.Parameters.AddWithValue("@Num6", cb.Num6); cmd.Parameters[19].SqlDbType = SqlDbType.BigInt; cmd.Parameters.AddWithValue("@Num7", cb.Num7); cmd.Parameters[20].SqlDbType = SqlDbType.BigInt; cmd.Parameters.AddWithValue("@Cost1", cb.Cost1); cmd.Parameters[21].SqlDbType = SqlDbType.Decimal; cmd.Parameters.AddWithValue("@Cost2", cb.Cost2); cmd.Parameters[22].SqlDbType = SqlDbType.Decimal; cmd.Parameters.AddWithValue("@Cost3", cb.Cost3); cmd.Parameters[23].SqlDbType = SqlDbType.Decimal; cmd.Parameters.AddWithValue("@Cost4", cb.Cost4); cmd.Parameters[24].SqlDbType = SqlDbType.Decimal; cmd.Parameters.AddWithValue("@Cost5", cb.Cost5); cmd.Parameters[25].SqlDbType = SqlDbType.Decimal; cmd.Parameters.AddWithValue("@Cost6", cb.Cost6); cmd.Parameters[26].SqlDbType = SqlDbType.Decimal; cmd.Parameters.AddWithValue("@Cost7", cb.Cost7); cmd.Parameters[27].SqlDbType = SqlDbType.Decimal; cmd.Parameters.AddWithValue("@TotalCost", cb.TotalCost); cmd.Parameters[28].SqlDbType = SqlDbType.Decimal; cmd.CommandTimeout = 2000; conn.Open(); cmd.ExecuteNonQuery(); } string message = ""; using (SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["TWSS"].ConnectionString.ToString())) { conn.Open(); // SqlTransaction sqlTransaction = conn.BeginTransaction();//事物 try { if (cb.WarehouseID == 53) { decimal?OperationCost2 = 0; decimal?OperationCost4 = 0; decimal?Cost2 = 0; decimal?Cost4 = 0; decimal?Cost5 = 0; decimal?Cost6 = 0; decimal?totalcoat = 0; OperationCost2 = cb.OperationCost2; //仓库收入 OperationCost4 = cb.OperationCost4; //入库管理费 Cost2 = cb.Cost2; //门店出库管理费 Cost4 = cb.Cost4; //门店出库理货费 Cost5 = cb.Cost5; //电商出货拣货费 Cost6 = cb.Cost6; //门店出库拣货费 totalcoat = cb.TotalCost; //总费用 string sql = $"INSERT INTO dbo.CTRSettledStorage values(53,'" + cb.SettlementNumber.ToString() + "' ,0 ,null , null ,0 ,98 ,'HABA',null ,null,null,null ,null , null ,null ,null,null , null , null,0, null , null ,null ,null ," + OperationCost2 + " ," + OperationCost4 + " ,null , null ," + Cost2 + "," + Cost4 + "," + Cost5 + "," + Cost6 + ", 0 ," + totalcoat + ", 1, 'HABA上海仓', '" + DateTime.Now.ToString("yyyy-MM-dd") + "', null, null, null, null,'haba','" + DateTime.Now.ToString("yyyy-MM-dd") + "', 0, 98, 1, 0 )"; SqlCommand cmdd = new SqlCommand(sql, conn); int rows = cmdd.ExecuteNonQuery(); } else { int? number = cb.Number1 + cb.Number2; decimal?total = cb.OutsourcingAveragecost + cb.OperationTotal + cb.TotalCost; decimal?outsourcing = 0; decimal?averagecost = 0; decimal?operationtotal = 0; decimal?totalcoat = 0; outsourcing = cb.OutsourcingTotalSum; averagecost = cb.OutsourcingAveragecost; operationtotal = cb.OperationTotal; totalcoat = cb.TotalCost; string sql = $"INSERT INTO dbo.CTRSettledStorage values(50,'" + cb.SettlementNumber.ToString() + "' ,0 ,null , null ,0 ,96 ,'喜利得北京',null , null,null,null ,null , null ,null ,null,null , null , null," + number + ", null , null ,null ,null , 0 , 0 ,null , null ," + outsourcing + "," + averagecost + "," + operationtotal + "," + totalcoat + ", 0 ," + total + ", 1, '喜利得北京仓', '" + DateTime.Now.ToString("yyyy-MM-dd") + "', null, null, null, null,'hilti','" + DateTime.Now.ToString("yyyy-MM-dd") + "', 0, 96, 1, 0 )"; SqlCommand cmdd = new SqlCommand(sql, conn); //cmdd.Transaction = sqlTransaction;//再次绑定事物 int rows = cmdd.ExecuteNonQuery(); //if (rows > 0) //{ // sqlTransaction.Commit();//提交事物 //} //else //{ // message = "插入数据失败,请重试!"; // sqlTransaction.Rollback();//回滚事物 //} } } catch (Exception ex) { message = ex.Message; // sqlTransaction.Rollback();//回滚事物 } conn.Close(); } }