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();
}
}
