public static void DeleteOrderDetailById(int id)
{
OrderDetail od = dal.GetOrderDetailById(id);
Order order = new OrderDAL().GetOrderById(od.OrderId);
order.Costs = order.Costs - od.TotalCosts;
order.SelfCosts = order.SelfCosts - od.SelfTotalCosts;
OrderOperation.UpdateOrder(order);
dal.DeleteOrderDetailById(id);
}
public static void DeleteOrderDetailByIds(string ids)
{
string[] array = ids.Split(',');
foreach (string sId in array)
{
int id = 0;
if(int.TryParse(sId, out id))
{
OrderDetail od = dal.GetOrderDetailById(id);
if (od.TotalCosts > 0)
{
Order order = new OrderDAL().GetOrderById(od.OrderId);
order.Costs = order.Costs - od.TotalCosts;
order.SelfCosts = order.SelfCosts - od.SelfTotalCosts;
OrderOperation.UpdateOrder(order);
}
dal.DeleteOrderDetailById(id);
}
}
}
public PaginationQueryResult<WrongOrder> GetWrongOrderByClientId(PaginationQueryCondition condition, int clientId)
{
PaginationQueryResult<WrongOrder> result = new PaginationQueryResult<WrongOrder>();
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputIntParameter("@client_id", clientId)
};
string sql = "SELECT TOP " + condition.PageSize + " WO.id, WO.order_id, WO.company_id, WO.company_name, WO.encode, WO.status, WO.reason, WO.[type], WO.create_time, WO.create_user_id, WO.last_update_time FROM wrong_orders AS WO INNER JOIN orders AS O ON WO.order_id = O.id WHERE O.client_id = @client_id AND WO.is_delete = 0 AND O.is_delete = 0";
if (condition.CurrentPage > 1)
{
sql += " AND WO.id< (SELECT MIN(id) FROM (SELECT TOP " + condition.PageSize * (condition.CurrentPage - 1) + " WO.id FROM wrong_orders AS WO INNER JOIN orders AS O ON WO.order_id = O.id WHERE O.client_id = @client_id AND WO.is_delete = 0 AND O.is_delete = 0 ORDER BY WO.id DESC) AS W)";
}
sql += " ORDER BY id DESC; SELECT COUNT(*) FROM wrong_orders AS WO INNER JOIN orders AS O ON WO.order_id = O.id WHERE O.client_id = @client_id AND WO.is_delete = 0 AND O.is_delete = 0";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
WrongOrder wo = new WrongOrder();
wo.Id = dr.GetInt32(0);
if (!dr.IsDBNull(1))
{
Order order = new OrderDAL().GetOrderById(dr.GetInt32(1));
wo.Order = order;
}
wo.CompanyId = dr.GetInt32(2);
wo.CompanyName = dr.GetString(3);
wo.Encode = dr.GetString(4);
wo.Status = EnumConvertor.ConvertToWrongOrderStatus(dr.GetByte(5));
wo.Reason = dr.GetString(6);
wo.Type = dr.GetString(7);
wo.CreateTime = dr.GetDateTime(8);
wo.CreateUserId = dr.GetInt32(9);
wo.LastUpdateCreateTime = dr.GetDateTime(10);
result.Results.Add(wo);
}
dr.NextResult();
while (dr.Read())
{
result.TotalCount = dr.GetInt32(0);
}
}
return result;
}
public WrongOrder GetWrongOrderById(int id)
{
WrongOrder wo = null;
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputIntParameter("@id", id)
};
string sql = "SELECT id, order_id, company_id, company_name, encode, status, reason, type, create_time, create_user_id, last_update_time FROM wrong_orders WHERE id = @id";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
wo = new WrongOrder();
wo.Id = dr.GetInt32(0);
if (!dr.IsDBNull(1))
{
Order order = new OrderDAL().GetOrderById(dr.GetInt32(1));
wo.Order = order;
}
wo.CompanyId = dr.GetInt32(2);
wo.CompanyName = dr.GetString(3);
wo.Encode = dr.GetString(4);
wo.Status = EnumConvertor.ConvertToWrongOrderStatus(dr.GetByte(5));
wo.Reason = dr.GetString(6);
wo.Type = dr.GetString(7);
wo.CreateTime = dr.GetDateTime(8);
wo.CreateUserId = dr.GetInt32(9);
wo.LastUpdateCreateTime = dr.GetDateTime(10);
}
}
return wo;
}
public PaginationQueryResult<WrongOrder> GetWrongOrderByCompanyIdAndDate(PaginationQueryCondition condition, int companyId, DateTime startDate, DateTime endDate)
{
PaginationQueryResult<WrongOrder> result = new PaginationQueryResult<WrongOrder>();
DateTime minTime = new DateTime(1999, 1, 1);
string sqlTime = "";
if (startDate > minTime && endDate > minTime)
{
sqlTime = " AND create_time BETWEEN @start_date AND @end_date";
}
else if (startDate > minTime && endDate <= minTime)
{
sqlTime = " AND create_time >= @start_date ";
}
else
{
sqlTime = " AND create_time <= @end_date";
}
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputIntParameter("@company_id", companyId),
SqlUtilities.GenerateInputDateTimeParameter("@start_date", startDate),
SqlUtilities.GenerateInputDateTimeParameter("@end_date", endDate)
};
string sql = "SELECT TOP " + condition.PageSize + " id, order_id, company_id, company_name, encode, status, reason, type, create_time, create_user_id, last_update_time FROM wrong_orders WHERE is_delete = 0 AND company_id = @company_id" + sqlTime;
if (condition.CurrentPage > 1)
{
sql += " AND id< (SELECT MIN(id) FROM (SELECT TOP " + condition.PageSize * (condition.CurrentPage - 1) + " id FROM wrong_orders WHERE is_delete = 0 AND company_id = @company_id "+sqlTime+" ORDER BY id DESC) AS W)";
}
sql += " ORDER BY id DESC; SELECT COUNT(*) FROM wrong_orders WHERE is_delete = 0 AND company_id = @company_id"+sqlTime;
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
WrongOrder wo = new WrongOrder();
wo.Id = dr.GetInt32(0);
if (!dr.IsDBNull(1))
{
Order order = new OrderDAL().GetOrderById(dr.GetInt32(1));
wo.Order = order;
}
wo.CompanyId = dr.GetInt32(2);
wo.CompanyName = dr.GetString(3);
wo.Encode = dr.GetString(4);
wo.Status = EnumConvertor.ConvertToWrongOrderStatus(dr.GetByte(5));
wo.Reason = dr.GetString(6);
wo.Type = dr.GetString(7);
wo.CreateTime = dr.GetDateTime(8);
wo.CreateUserId = dr.GetInt32(9);
wo.LastUpdateCreateTime = dr.GetDateTime(10);
result.Results.Add(wo);
}
dr.NextResult();
while (dr.Read())
{
result.TotalCount = dr.GetInt32(0);
}
}
return result;
}
public Liability GetLiabilityById(int id)
{
Liability ly = null;
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputIntParameter("@id", id)
};
string sql = "SELECT id, company_id, encode, order_encode, bar_code, currency_type, create_user, create_time, event_type, correct_status, status, fill_user, fill_time, detail, result, total_money, zr_department, zr_dt_money, zr_user, zr_ur_money, client_name, client_pt_eadu, eadu_pt_client, carrier_name, carrier_pt_eadu, eadu_pt_carrier, jl_department, jl_dt_money, jl_user, jl_ur_money, liability_user, correct_user, finance_user, cashier_user FROM liabilities WHERE id = @id";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
ly = new Liability();
ly.Id = dr.GetInt32(0);
ly.CompanyId = dr.GetInt32(1);
ly.Encode = dr.GetString(2);
Order order = new OrderDAL().GetOrderByEncode(dr.GetString(3));
ly.Order = order;
if (!dr.IsDBNull(4))
{
ly.BarCode = dr.GetString(4);
}
if (!dr.IsDBNull(5))
{
ly.CurrencyType = dr.GetString(5);
}
ly.CreateUser = dr.GetString(6);
ly.CreateTime = dr.GetDateTime(7);
ly.EventType = EnumConvertor.ConvertToLiabilityEventType(dr.GetByte(8));
ly.CorrectStatus = dr.GetBoolean(9);
ly.Status = EnumConvertor.ConvertToLiabilityStatus(dr.GetByte(10));
ly.FillUser = dr.GetString(11);
ly.FillTime = dr.GetDateTime(12);
ly.Detail = dr.GetString(13);
ly.Result = dr.GetString(14);
ly.TotalMoney = dr.GetDecimal(15);
if (!dr.IsDBNull(16))
{
ly.ZrDepartment = dr.GetString(16);
}
if (!dr.IsDBNull(17))
{
ly.ZrDtMoney = dr.GetDecimal(17);
}
if (!dr.IsDBNull(18))
{
ly.ZrUser = dr.GetString(18);
}
if (!dr.IsDBNull(19))
{
ly.ZrUrMoney = dr.GetDecimal(19);
}
ly.ClientName = dr.GetString(20);
ly.ClientPtEadu = dr.GetDecimal(21);
ly.EaduPtClient = dr.GetDecimal(22);
if (!dr.IsDBNull(23))
{
ly.CarrierName = dr.GetString(23);
}
ly.CarrierPtEadu = dr.GetDecimal(24);
ly.EaduPtCarrier = dr.GetDecimal(25);
if (!dr.IsDBNull(26))
{
ly.JlDepartment = dr.GetString(26);
}
if (!dr.IsDBNull(27))
{
ly.JlDtMoney = dr.GetDecimal(27);
}
if (!dr.IsDBNull(28))
{
ly.JlUser = dr.GetString(28);
}
if (!dr.IsDBNull(29))
{
ly.JlUrMoney = dr.GetDecimal(29);
}
if (!dr.IsDBNull(30))
{
ly.LiabilityUser = dr.GetString(30);
}
if (!dr.IsDBNull(31))
{
ly.CorrectUser = dr.GetString(31);
}
if (!dr.IsDBNull(32))
{
ly.FinanceUser = dr.GetString(32);
}
if (!dr.IsDBNull(33))
{
ly.CashierUser = dr.GetString(33);
}
}
}
return ly;
}
public PaginationQueryResult<Liability> GetFinishedLiabilityByParameters(PaginationQueryCondition condition, int companyId, DateTime startDate, DateTime endDate)
{
PaginationQueryResult<Liability> result = new PaginationQueryResult<Liability>();
DateTime minTime = new DateTime(1999, 1, 1);
string sqlParam = "";
if (companyId > 0)
{
sqlParam += " AND company_id = @company_id";
}
if (startDate > minTime && endDate > minTime)
{
sqlParam = " AND create_time BETWEEN @start_date AND @end_date";
}
else if (startDate > minTime && endDate <= minTime)
{
sqlParam = " AND create_time >= @start_date ";
}
else if (startDate <= minTime && endDate > minTime)
{
sqlParam = " AND create_time <= @end_date";
}
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputIntParameter("@company_id", companyId),
SqlUtilities.GenerateInputDateTimeParameter("@start_date", startDate),
SqlUtilities.GenerateInputDateTimeParameter("@end_date", endDate)
};
string sql = "SELECT TOP " + condition.PageSize + " id, company_id, encode, order_encode, bar_code, currency_type, create_user, create_time, event_type, correct_status, status, fill_user, fill_time, detail, result, total_money, zr_department, zr_dt_money, zr_user, zr_ur_money, client_name, client_pt_eadu, eadu_pt_client, carrier_name, carrier_pt_eadu, eadu_pt_carrier, jl_department, jl_dt_money, jl_user, jl_ur_money, liability_user, correct_user, finance_user, cashier_user FROM liabilities WHERE is_delete = 0 AND status = 5" + sqlParam;
if (condition.CurrentPage > 1)
{
sql += " AND id< (SELECT MIN(id) FROM (SELECT TOP " + condition.PageSize * (condition.CurrentPage - 1) + " id FROM liabilities WHERE is_delete = 0 AND status = 5 " + sqlParam + " ORDER BY id DESC) AS L)";
}
sql += " ORDER BY id DESC; SELECT COUNT(*) FROM liabilities WHERE is_delete = 0 AND status = 5" + sqlParam;
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
Liability ly = new Liability();
ly.Id = dr.GetInt32(0);
ly.CompanyId = dr.GetInt32(1);
ly.Encode = dr.GetString(2);
Order order = new OrderDAL().GetOrderByEncode(dr.GetString(3));
ly.Order = order;
if (!dr.IsDBNull(4))
{
ly.BarCode = dr.GetString(4);
}
if (!dr.IsDBNull(5))
{
ly.CurrencyType = dr.GetString(5);
}
ly.CreateUser = dr.GetString(6);
ly.CreateTime = dr.GetDateTime(7);
ly.EventType = EnumConvertor.ConvertToLiabilityEventType(dr.GetByte(8));
ly.CorrectStatus = dr.GetBoolean(9);
ly.Status = EnumConvertor.ConvertToLiabilityStatus(dr.GetByte(10));
ly.FillUser = dr.GetString(11);
ly.FillTime = dr.GetDateTime(12);
ly.Detail = dr.GetString(13);
ly.Result = dr.GetString(14);
ly.TotalMoney = dr.GetDecimal(15);
if (!dr.IsDBNull(16))
{
ly.ZrDepartment = dr.GetString(16);
}
if (!dr.IsDBNull(17))
{
ly.ZrDtMoney = dr.GetDecimal(17);
}
if (!dr.IsDBNull(18))
{
ly.ZrUser = dr.GetString(18);
}
if (!dr.IsDBNull(19))
{
ly.ZrUrMoney = dr.GetDecimal(19);
}
ly.ClientName = dr.GetString(20);
ly.ClientPtEadu = dr.GetDecimal(21);
ly.EaduPtClient = dr.GetDecimal(22);
if (!dr.IsDBNull(23))
{
ly.CarrierName = dr.GetString(23);
}
ly.CarrierPtEadu = dr.GetDecimal(24);
ly.EaduPtCarrier = dr.GetDecimal(25);
if (!dr.IsDBNull(26))
{
ly.JlDepartment = dr.GetString(26);
}
if (!dr.IsDBNull(27))
{
ly.JlDtMoney = dr.GetDecimal(27);
}
if (!dr.IsDBNull(28))
{
ly.JlUser = dr.GetString(28);
}
if (!dr.IsDBNull(29))
{
ly.JlUrMoney = dr.GetDecimal(29);
}
if (!dr.IsDBNull(30))
{
ly.LiabilityUser = dr.GetString(30);
}
if (!dr.IsDBNull(31))
{
ly.CorrectUser = dr.GetString(31);
}
if (!dr.IsDBNull(32))
{
ly.FinanceUser = dr.GetString(32);
}
if (!dr.IsDBNull(33))
{
ly.CashierUser = dr.GetString(33);
}
result.Results.Add(ly);
}
dr.NextResult();
while (dr.Read())
{
result.TotalCount = dr.GetInt32(0);
}
}
return result;
}