コード例 #1
0
ファイル: OrderDAL.cs プロジェクト: feidu/XSEMS
 public void CreateOrder(Order order)
 {
     SqlParameter[] param = new SqlParameter[] {
         SqlUtilities.GenerateInputIntParameter("@client_id", order.Client.Id),
         //SqlUtilities.GenerateInputIntParameter("@company_id", order.CompanyId),
         //SqlUtilities.GenerateInputNVarcharParameter("@company_name", 50, order.CompanyName),
         //SqlUtilities.GenerateInputIntParameter("@user_id", order.UserId),
         SqlUtilities.GenerateInputVarcharParameter("@encode", 50,order.Encode),
         SqlUtilities.GenerateInputParameter("@status", SqlDbType.TinyInt, (byte)order.Status),
         SqlUtilities.GenerateInputParameter("@costs", SqlDbType.Money, order.Costs),
         SqlUtilities.GenerateInputParameter("@self_costs", SqlDbType.Money, order.SelfCosts),
         SqlUtilities.GenerateInputDateTimeParameter("@receive_date",order.ReceiveDate),
         //SqlUtilities.GenerateInputParameter("@type", SqlDbType.TinyInt, (byte)order.Type),
         SqlUtilities.GenerateInputDateTimeParameter("@create_time",order.CreateTime),
         //SqlUtilities.GenerateInputIntParameter("@calculate_type", order.CalculateType),
         //SqlUtilities.GenerateInputNVarcharParameter("@receive_type", 20, order.ReceiveType),
         //SqlUtilities.GenerateInputIntParameter("@create_user_id", order.CreateUser.Id),
         //SqlUtilities.GenerateInputIntParameter("@receive_user_id", order.ReceiveUserId),
         SqlUtilities.GenerateInputNVarcharParameter("@remark", 500, order.Remark),
         SqlUtilities.GenerateInputNVarcharParameter("@to_username", 50, order.ToUsername),
         SqlUtilities.GenerateInputNVarcharParameter("@to_phone", 50, order.ToPhone),
         SqlUtilities.GenerateInputNVarcharParameter("@to_email", 50, order.ToEmail),
         SqlUtilities.GenerateInputNVarcharParameter("@to_city", 50, order.ToCity),
         SqlUtilities.GenerateInputNVarcharParameter("@to_country", 50, order.ToCountry),
         SqlUtilities.GenerateInputNVarcharParameter("@to_address", 200, order.ToAddress),
         SqlUtilities.GenerateInputNVarcharParameter("@to_postcode", 50, order.ToPostcode),
         SqlUtilities.GenerateInputParameter("@is_quick_order", SqlDbType.Bit, order.IsQuickOrder)
     };
     string sql = "INSERT INTO orders(client_id, encode, status, costs, self_costs, receive_date, create_time, remark, to_username, to_phone, to_email, to_city, to_country, to_address, to_postcode, is_quick_order) VALUES(@client_id, @encode, @status, @costs, @self_costs, @receive_date, @create_time, @remark, @to_username, @to_phone, @to_email, @to_city, @to_country, @to_address, @to_postcode, @is_quick_order)";
     SqlHelper.ExecuteNonQuery(CommandType.Text, sql, param);
 }
コード例 #2
0
ファイル: OrderDAL.cs プロジェクト: feidu/XSEMS
 public void UpdateOrderStatus(Order order)
 {
     SqlParameter[] param = new SqlParameter[] {
         SqlUtilities.GenerateInputIntParameter("@id", order.Id),
         SqlUtilities.GenerateInputParameter("@status", SqlDbType.TinyInt, (byte)order.Status)
      };
     string sql = " UPDATE orders SET status = @status WHERE id = @id";
     SqlHelper.ExecuteNonQuery(CommandType.Text, sql, param);
 }
コード例 #3
0
ファイル: OrderDAL.cs プロジェクト: feidu/XSEMS
 public void UpdateOrderReason(Order order)
 {
     SqlParameter[] param = new SqlParameter[] {
         SqlUtilities.GenerateInputIntParameter("@id", order.Id),
         SqlUtilities.GenerateInputNVarcharParameter("@reason", 500, order.Reason)
      };
     string sql = " UPDATE orders SET reason = @reason WHERE id = @id";
     SqlHelper.ExecuteNonQuery(CommandType.Text, sql, param);
 }
コード例 #4
0
ファイル: OrderDAL.cs プロジェクト: feidu/XSEMS
 public void UpdateOrderIsMailSend(Order order)
 {
     SqlParameter[] param = new SqlParameter[] {
         SqlUtilities.GenerateInputIntParameter("@id", order.Id),
         SqlUtilities.GenerateInputParameter("@is_mail_send", SqlDbType.Bit, order.IsMailSend)
      };
     string sql = " UPDATE orders SET is_mail_send = @is_mail_send WHERE id = @id";
     SqlHelper.ExecuteNonQuery(CommandType.Text, sql, param);
 }
コード例 #5
0
ファイル: OrderDAL.cs プロジェクト: feidu/XSEMS
 public void UpdateOrderCheckInfo(Order order)
 {
     SqlParameter[] param = new SqlParameter[] {
         SqlUtilities.GenerateInputIntParameter("@id", order.Id),
         SqlUtilities.GenerateInputIntParameter("@check_user_id", order.CheckUserId),
         SqlUtilities.GenerateInputDateTimeParameter("@check_time", order.CheckTime)
      };
     string sql = " UPDATE orders SET check_user_id = @check_user_id, check_time = @check_time WHERE id = @id";
     SqlHelper.ExecuteNonQuery(CommandType.Text, sql, param);
 }
コード例 #6
0
ファイル: OrderOperation.cs プロジェクト: sd1362168638/XSEMS
 public static void UpdateOrderAuditInfo(Order order)
 {
     dal.UpdateOrderAuditInfo(order);
 }
コード例 #7
0
ファイル: OrderDAL.cs プロジェクト: feidu/XSEMS
 public PaginationQueryResult<Order> GetOrderByStatus(PaginationQueryCondition condition, OrderStatus status)
 {
     PaginationQueryResult<Order> result = new PaginationQueryResult<Order>();
     SqlParameter[] param = new SqlParameter[] {
         SqlUtilities.GenerateInputParameter("@status", SqlDbType.TinyInt, (byte)status)
     };
     string sql = "SELECT TOP " + condition.PageSize + " id, client_id, encode, status, costs, create_time, remark, is_mail_send, audit_user_id, audit_time, check_user_id, check_time, create_time FROM orders WHERE is_delete = 0 AND status = @status";
     if (condition.CurrentPage > 1)
     {
         sql += " AND id< (SELECT MIN(id) FROM (SELECT TOP " + condition.PageSize * (condition.CurrentPage - 1) + " id FROM orders WHERE is_delete = 0  AND status = @status ORDER BY id DESC) AS O) ";
     }
     sql += " ORDER BY id DESC; SELECT COUNT(*) FROM orders WHERE is_delete = 0 AND status = @status";
     using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
     {
         while (dr.Read())
         {
             Order order = new Order();
             order.Id = dr.GetInt32(0);
             Client client = new ClientDAL().GetClientById(dr.GetInt32(1));
             order.Client = client;
             order.Encode = dr.GetString(2);
             order.Status = EnumConvertor.ConvertToOrderStatus(dr.GetByte(3));
             order.Costs = dr.GetDecimal(4);
             order.CreateTime = dr.GetDateTime(5);
             order.Remark = dr.GetString(6);
             order.IsMailSend = dr.GetBoolean(7);
             if (!dr.IsDBNull(8))
             {
                 order.AuditUserId = dr.GetInt32(8);
             }
             if (!dr.IsDBNull(9))
             {
                 order.AuditTime = dr.GetDateTime(9);
             }
             if (!dr.IsDBNull(10))
             {
                 order.CheckUserId = dr.GetInt32(10);
             }
             if (!dr.IsDBNull(11))
             {
                 order.CheckTime = dr.GetDateTime(11);
             }
             order.CreateTime = dr.GetDateTime(12);
             result.Results.Add(order);
         }
         dr.NextResult();
         while (dr.Read())
         {
             result.TotalCount = dr.GetInt32(0);
         }
     }
     return result;
 }
コード例 #8
0
ファイル: OrderDAL.cs プロジェクト: feidu/XSEMS
 public PaginationQueryResult<Order> GetAuditOrderByConsignType(PaginationQueryCondition condition, int consignType)
 {
     PaginationQueryResult<Order> result = new PaginationQueryResult<Order>();
     SqlParameter[] param = new SqlParameter[] {
         SqlUtilities.GenerateInputParameter("@status", SqlDbType.TinyInt, (byte)OrderStatus.WAIT_AUDIT)
     };
     string sql = "";
     switch (consignType)
     {
         case 1:
             sql = "SELECT TOP " + condition.PageSize + " id, client_id, encode, status, costs, create_time, remark FROM orders WHERE is_delete = 0 AND status = @status";
             if (condition.CurrentPage > 1)
             {
                 sql += " AND id< (SELECT MIN(id) FROM (SELECT TOP " + condition.PageSize * (condition.CurrentPage - 1) + " id FROM orders WHERE is_delete = 0 AND status = @status ORDER BY id DESC) AS O) ";
             }
             sql += " ORDER BY id DESC; SELECT COUNT(*) FROM orders WHERE status = @status";
             break;
         case 2:
             sql = "SELECT TOP " + condition.PageSize + " OS.id, OS.client_id, OS.encode, OS.status, OS.costs, OS.create_time, OS.remark, CS.balance FROM orders AS OS INNER JOIN clients AS CS ON  OS.client_id = CS.id WHERE OS.is_delete = 0 AND OS.status = @status AND (CS.balance + CS.credit) >= OS.costs";
             if (condition.CurrentPage > 1)
             {
                 sql += " AND OS.id< (SELECT MIN(id) FROM (SELECT TOP " + condition.PageSize * (condition.CurrentPage - 1) + " OS.id FROM orders WHERE OS.is_delete = 0 AND OS.status = @status AND (CS.balance + CS.credit) >= OS.costs ORDER BY OS.id DESC) AS O) ";
             }
             sql += " ORDER BY OS.id DESC; SELECT COUNT(*) FROM orders AS OS INNER JOIN clients AS CS ON  OS.client_id = CS.id WHERE OS.is_delete = 0 AND OS.status = @status AND (CS.balance + CS.credit) >= OS.costs";
             break;
         case 3:
             sql = "SELECT TOP " + condition.PageSize + " OS.id, OS.client_id, OS.encode, OS.status, OS.costs, OS.create_time, OS.remark, CS.balance FROM orders AS OS INNER JOIN clients AS CS ON  OS.client_id = CS.id WHERE OS.is_delete = 0 AND OS.status = @status AND (CS.balance + CS.credit) < OS.costs";
             if (condition.CurrentPage > 1)
             {
                 sql += " AND OS.id< (SELECT MIN(id) FROM (SELECT TOP " + condition.PageSize * (condition.CurrentPage - 1) + " OS.id FROM orders WHERE OS.is_delete = 0 AND OS.status = @status AND (CS.balance + CS.credit) < OS.costs ORDER BY OS.id DESC) AS O) ";
             }
             sql += " ORDER BY OS.id DESC; SELECT COUNT(*) FROM orders AS OS INNER JOIN clients AS CS ON  OS.client_id = CS.id WHERE OS.is_delete = 0 AND OS.status = @status AND (CS.balance + CS.credit) < OS.costs";
             break;
     }
     using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
     {
         while (dr.Read())
         {
             Order order = new Order();
             order.Id = dr.GetInt32(0);
             Client client = new ClientDAL().GetClientById(dr.GetInt32(1));
             order.Client = client;
             order.Encode = dr.GetString(2);
             order.Status = EnumConvertor.ConvertToOrderStatus(dr.GetByte(3));
             order.Costs = dr.GetDecimal(4);
             order.CreateTime = dr.GetDateTime(5);
             order.Remark = dr.GetString(6);
             result.Results.Add(order);
         }
         dr.NextResult();
         while (dr.Read())
         {
             result.TotalCount = dr.GetInt32(0);
         }
     }
     return result;
 }
コード例 #9
0
ファイル: OrderDAL.cs プロジェクト: sd1362168638/XSEMS
 public PaginationQueryResult<Order> GetOrderByClientId(PaginationQueryCondition condition, int cId)
 {
     PaginationQueryResult<Order> result = new PaginationQueryResult<Order>();
     SqlParameter[] param = new SqlParameter[] {
         SqlUtilities.GenerateInputIntParameter("@client_id", cId)
     };
     string sql = "SELECT TOP " + condition.PageSize + " id, client_id, company_id, company_name, user_id, encode, status, costs, receive_date, type, create_time, calculate_type, receive_type, create_user_id, receive_user_id, remark, is_mail_send, audit_user_id, audit_time, check_user_id, check_time FROM orders WHERE is_delete = 0 AND client_id = @client_id";
     if (condition.CurrentPage > 1)
     {
         sql += " AND id< (SELECT MIN(id) FROM (SELECT TOP " + condition.PageSize * (condition.CurrentPage - 1) + " id FROM orders WHERE is_delete = 0 AND client_id = @client_id ORDER BY id DESC) AS O) ";
     }
     sql += " ORDER BY id DESC; SELECT COUNT(*) FROM orders WHERE is_delete = 0 AND client_id = @client_id ";
     using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
     {
         while (dr.Read())
         {
             Order order = new Order();
             order.Id = dr.GetInt32(0);
             Client client = new ClientDAL().GetClientById(dr.GetInt32(1));
             order.Client = client;
             order.CompanyId = dr.GetInt32(2);
             order.CompanyName = dr.GetString(3);
             if (!dr.IsDBNull(4))
             {
                 order.UserId = dr.GetInt32(4);
             }
             order.Encode = dr.GetString(5);
             order.Status = EnumConvertor.ConvertToOrderStatus(dr.GetByte(6));
             order.Costs = dr.GetDecimal(7);
             if (!dr.IsDBNull(8))
             {
                 order.ReceiveDate = dr.GetDateTime(8);
             }
             order.Type = EnumConvertor.ConvertToOrderType(dr.GetByte(9));
             order.CreateTime = dr.GetDateTime(10);
             order.CalculateType = dr.GetInt32(11);
             order.ReceiveType = dr.GetString(12);
             if (!dr.IsDBNull(13))
             {
                 User user = new UserDAL().GetUserById(dr.GetInt32(13));
                 order.CreateUser = user;
             }
             if (!dr.IsDBNull(14))
             {
                 order.ReceiveUserId = dr.GetInt32(14);
             }
             order.Remark = dr.GetString(15);
             order.IsMailSend = dr.GetBoolean(16);
             if (!dr.IsDBNull(17))
             {
                 order.AuditUserId = dr.GetInt32(17);
             }
             if (!dr.IsDBNull(18))
             {
                 order.AuditTime = dr.GetDateTime(18);
             }
             if (!dr.IsDBNull(19))
             {
                 order.CheckUserId = dr.GetInt32(19);
             }
             if (!dr.IsDBNull(20))
             {
                 order.CheckTime = dr.GetDateTime(20);
             }
             result.Results.Add(order);
         }
         dr.NextResult();
         while (dr.Read())
         {
             result.TotalCount = dr.GetInt32(0);
         }
     }
     return result;
 }
コード例 #10
0
ファイル: OrderDAL.cs プロジェクト: sd1362168638/XSEMS
 public PaginationQueryResult<Order> GetAuditOrderByCompIdConsignTypeAndEncode(PaginationQueryCondition condition, int compId, int consignType, string encode)
 {
     PaginationQueryResult<Order> result = new PaginationQueryResult<Order>();
     SqlParameter[] param = new SqlParameter[] {
         SqlUtilities.GenerateInputIntParameter("@company_id", compId),
         SqlUtilities.GenerateInputNVarcharParameter("@encode", 50, encode),
         SqlUtilities.GenerateInputParameter("@status", SqlDbType.TinyInt, (byte)OrderStatus.WAIT_AUDIT)
     };
     string sql = "";
     switch (consignType)
     {
         case 1:
             sql = "SELECT TOP " + condition.PageSize + " id, client_id, company_id, company_name, user_id, encode, status, costs, receive_date, type, create_time, calculate_type, receive_type, create_user_id, receive_user_id, remark FROM orders WHERE is_delete = 0 AND company_id = @company_id AND status = @status AND encode = @encode";
             if (condition.CurrentPage > 1)
             {
                 sql += " AND id< (SELECT MIN(id) FROM (SELECT TOP " + condition.PageSize * (condition.CurrentPage - 1) + " id FROM orders WHERE is_delete = 0 AND company_id = @company_id AND status = @status AND encode = @encode ORDER BY id DESC) AS O) ";
             }
             sql += " ORDER BY id DESC; SELECT COUNT(*) FROM orders WHERE company_id = @company_id AND status = @status AND encode = @encode";
             break;
         case 2:
             sql = "SELECT TOP " + condition.PageSize + " OS.id, OS.client_id, OS.company_id, OS.company_name, OS.user_id, OS.encode,             OS.status, OS.costs, OS.receive_date, OS.type, OS.create_time, OS.calculate_type, OS.receive_type, OS.create_user_id, OS.receive_user_id,           OS.remark, CS.balance FROM orders AS OS INNER JOIN clients AS CS ON  OS.client_id = CS.id WHERE OS.is_delete = 0 AND OS.company_id = @company_id AND OS.status = @status AND OS.encode = @encode AND (CS.balance + CS.credit) >= OS.costs";
             if (condition.CurrentPage > 1)
             {
                 sql += " AND OS.id< (SELECT MIN(id) FROM (SELECT TOP " + condition.PageSize * (condition.CurrentPage - 1) + " OS.id FROM orders WHERE OS.is_delete = 0 AND OS.company_id = @company_id AND OS.status = @status AND OS.encode = @encode AND (CS.balance + CS.credit) >=       OS.costs ORDER BY   OS.id DESC) AS O) ";
             }
             sql += " ORDER BY OS.id DESC; SELECT COUNT(*) FROM orders AS OS INNER JOIN clients AS CS ON  OS.client_id = CS.id WHERE         OS.is_delete = 0 AND OS.company_id = @company_id AND OS.status = @status AND OS.encode = @encode AND (CS.balance + CS.credit) >= OS.costs";
             break;
         case 3:
             sql = "SELECT TOP " + condition.PageSize + " OS.id, OS.client_id, OS.company_id, OS.company_name, OS.user_id, OS.encode,             OS.status, OS.costs, OS.receive_date, OS.type, OS.create_time, OS.calculate_type, OS.receive_type, OS.create_user_id, OS.receive_user_id,           OS.remark, CS.balance FROM orders AS OS INNER JOIN clients AS CS ON  OS.client_id = CS.id WHERE OS.is_delete = 0 AND OS.company_id = @company_id AND OS.status = @status AND OS.encode = @encode AND (CS.balance + CS.credit) < OS.costs";
             if (condition.CurrentPage > 1)
             {
                 sql += " AND OS.id< (SELECT MIN(id) FROM (SELECT TOP " + condition.PageSize * (condition.CurrentPage - 1) + " OS.id FROM orders WHERE OS.is_delete = 0 AND OS.company_id = @company_id AND OS.status = @status AND OS.encode = @encode AND (CS.balance + CS.credit) < OS.costs ORDER BY    OS.id DESC) AS O) ";
             }
             sql += " ORDER BY OS.id DESC; SELECT COUNT(*) FROM orders AS OS INNER JOIN clients AS CS ON  OS.client_id = CS.id WHERE         OS.is_delete = 0 AND OS.company_id = @company_id AND OS.status = @status AND OS.encode = @encode AND (CS.balance + CS.credit) < OS.costs";
             break;
     }
     using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
     {
         while (dr.Read())
         {
             Order order = new Order();
             order.Id = dr.GetInt32(0);
             Client client = new ClientDAL().GetClientById(dr.GetInt32(1));
             order.Client = client;
             order.CompanyId = dr.GetInt32(2);
             order.CompanyName = dr.GetString(3);
             if (!dr.IsDBNull(4))
             {
                 order.UserId = dr.GetInt32(4);
             }
             order.Encode = dr.GetString(5);
             order.Status = EnumConvertor.ConvertToOrderStatus(dr.GetByte(6));
             order.Costs = dr.GetDecimal(7);
             if (!dr.IsDBNull(8))
             {
                 order.ReceiveDate = dr.GetDateTime(8);
             }
             order.Type = EnumConvertor.ConvertToOrderType(dr.GetByte(9));
             order.CreateTime = dr.GetDateTime(10);
             order.CalculateType = dr.GetInt32(11);
             order.ReceiveType = dr.GetString(12);
             if (!dr.IsDBNull(13))
             {
                 User user = new UserDAL().GetUserById(dr.GetInt32(13));
                 order.CreateUser = user;
             }
             if (!dr.IsDBNull(14))
             {
                 order.ReceiveUserId = dr.GetInt32(14);
             }
             order.Remark = dr.GetString(15);
             result.Results.Add(order);
         }
         dr.NextResult();
         while (dr.Read())
         {
             result.TotalCount = dr.GetInt32(0);
         }
     }
     return result;
 }
コード例 #11
0
ファイル: OrderOperation.cs プロジェクト: sd1362168638/XSEMS
 public static void CreateOrder(Order order)
 {
     dal.CreateOrder(order);
 }
コード例 #12
0
ファイル: OrderOperation.cs プロジェクト: sd1362168638/XSEMS
 public static void UpdateOrderStatus(Order order)
 {
     dal.UpdateOrderStatus(order);
 }
コード例 #13
0
ファイル: OrderOperation.cs プロジェクト: sd1362168638/XSEMS
 public static void UpdateOrderReason(Order order)
 {
     dal.UpdateOrderReason(order);
 }
コード例 #14
0
ファイル: OrderOperation.cs プロジェクト: sd1362168638/XSEMS
 public static void UpdateOrderIsMailSend(Order order)
 {
     dal.UpdateOrderIsMailSend(order);
 }
コード例 #15
0
ファイル: OrderOperation.cs プロジェクト: sd1362168638/XSEMS
 public static void UpdateOrderCheckInfo(Order order)
 {
     dal.UpdateOrderCheckInfo(order);
 }
コード例 #16
0
ファイル: OrderDAL.cs プロジェクト: sd1362168638/XSEMS
 public Order GetOrderByClientIdEncodeAndStatus(int clientId, string encode, OrderStatus status)
 {
     Order order = null;
     SqlParameter[] param = new SqlParameter[] {
         SqlUtilities.GenerateInputIntParameter("@client_id", clientId),
         SqlUtilities.GenerateInputParameter("@status", SqlDbType.TinyInt, (byte)status),
         SqlUtilities.GenerateInputNVarcharParameter("@encode", 50, encode)
     };
     string sql = "SELECT id, client_id, company_id, company_name, user_id, encode, status, costs, receive_date, type, create_time, calculate_type, receive_type, create_user_id, receive_user_id, remark, is_mail_send, audit_user_id, audit_time, check_user_id, check_time, reason, to_username, to_phone, to_email, to_city, to_country, to_address, to_postcode, self_costs FROM orders WHERE is_delete = 0 AND client_id = @client_id AND encode = @encode AND status = @status";
     using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
     {
         while (dr.Read())
         {
             order = new Order();
             order.Id = dr.GetInt32(0);
             Client client = new ClientDAL().GetClientById(dr.GetInt32(1));
             order.Client = client;
             order.CompanyId = dr.GetInt32(2);
             order.CompanyName = dr.GetString(3);
             if (!dr.IsDBNull(4))
             {
                 order.UserId = dr.GetInt32(4);
             }
             order.Encode = dr.GetString(5);
             order.Status = EnumConvertor.ConvertToOrderStatus(dr.GetByte(6));
             order.Costs = dr.GetDecimal(7);
             if (!dr.IsDBNull(8))
             {
                 order.ReceiveDate = dr.GetDateTime(8);
             }
             order.Type = EnumConvertor.ConvertToOrderType(dr.GetByte(9));
             order.CreateTime = dr.GetDateTime(10);
             order.CalculateType = dr.GetInt32(11);
             order.ReceiveType = dr.GetString(12);
             if (!dr.IsDBNull(13))
             {
                 User user = new UserDAL().GetUserById(dr.GetInt32(13));
                 order.CreateUser = user;
             }
             if (!dr.IsDBNull(14))
             {
                 order.ReceiveUserId = dr.GetInt32(14);
             }
             order.Remark = dr.GetString(15);
             order.IsMailSend = dr.GetBoolean(16);
             if (!dr.IsDBNull(17))
             {
                 order.AuditUserId = dr.GetInt32(17);
             }
             if (!dr.IsDBNull(18))
             {
                 order.AuditTime = dr.GetDateTime(18);
             }
             if (!dr.IsDBNull(19))
             {
                 order.CheckUserId = dr.GetInt32(19);
             }
             if (!dr.IsDBNull(20))
             {
                 order.CheckTime = dr.GetDateTime(20);
             }
             if (!dr.IsDBNull(21))
             {
                 order.Reason = dr.GetString(21);
             }
             order.ToUsername = dr.GetString(22);
             order.ToPhone = dr.GetString(23);
             order.ToEmail = dr.GetString(24);
             order.ToCity = dr.GetString(25);
             order.ToCountry = dr.GetString(26);
             order.ToAddress = dr.GetString(27);
             order.ToPostcode = dr.GetString(28);
             order.SelfCosts = dr.GetDecimal(29);
         }
     }
     return order;
 }
コード例 #17
0
ファイル: OrderDAL.cs プロジェクト: feidu/XSEMS
 public PaginationQueryResult<Order> GetOrderByClientIdAndDate(PaginationQueryCondition condition, int clientId, DateTime startDate, DateTime endDate)
 {
     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";
     }
     PaginationQueryResult<Order> result = new PaginationQueryResult<Order>();
     SqlParameter[] param = new SqlParameter[] {
         SqlUtilities.GenerateInputDateTimeParameter("@start_date", startDate),
         SqlUtilities.GenerateInputDateTimeParameter("@end_date", endDate),
         SqlUtilities.GenerateInputIntParameter("@client_id", clientId)
     };
     string sql = "SELECT TOP " + condition.PageSize + " id, client_id, encode, status, costs, create_time, remark, is_mail_send, audit_user_id, audit_time, check_user_id, check_time FROM orders WHERE is_delete = 0 AND client_id = @client_id "+sqlTime ;
     if (condition.CurrentPage > 1)
     {
         sql += " AND id< (SELECT MIN(id) FROM (SELECT TOP " + condition.PageSize * (condition.CurrentPage - 1) + " id FROM orders WHERE is_delete = 0 AND client_id = @client_id "+sqlTime+" ORDER BY id DESC) AS O) ";
     }
     sql += " ORDER BY id DESC; SELECT COUNT(*) FROM orders WHERE is_delete = 0 AND client_id = @client_id "+sqlTime;
     using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
     {
         while (dr.Read())
         {
             Order order = new Order();
             order.Id = dr.GetInt32(0);
             Client client = new ClientDAL().GetClientById(dr.GetInt32(1));
             order.Client = client;
             order.Encode = dr.GetString(2);
             order.Status = EnumConvertor.ConvertToOrderStatus(dr.GetByte(3));
             order.Costs = dr.GetDecimal(4);
             order.CreateTime = dr.GetDateTime(5);
             order.Remark = dr.GetString(6);
             order.IsMailSend = dr.GetBoolean(7);
             if (!dr.IsDBNull(8))
             {
                 order.AuditUserId = dr.GetInt32(8);
             }
             if (!dr.IsDBNull(9))
             {
                 order.AuditTime = dr.GetDateTime(9);
             }
             if (!dr.IsDBNull(10))
             {
                 order.CheckUserId = dr.GetInt32(10);
             }
             if (!dr.IsDBNull(11))
             {
                 order.CheckTime = dr.GetDateTime(11);
             }
             result.Results.Add(order);
         }
         dr.NextResult();
         while (dr.Read())
         {
             result.TotalCount = dr.GetInt32(0);
         }
     }
     return result;
 }
コード例 #18
0
ファイル: OrderDAL.cs プロジェクト: feidu/XSEMS
 public void UpdateClientOrder(Order order)
 {
     SqlParameter[] param = new SqlParameter[] {
         SqlUtilities.GenerateInputIntParameter("@id", order.Id),
         SqlUtilities.GenerateInputNVarcharParameter("@to_username", 50, order.ToUsername),
         SqlUtilities.GenerateInputNVarcharParameter("@to_phone", 50, order.ToPhone),
         SqlUtilities.GenerateInputNVarcharParameter("@to_email", 50, order.ToEmail),
         SqlUtilities.GenerateInputNVarcharParameter("@to_city", 50, order.ToCity),
         SqlUtilities.GenerateInputNVarcharParameter("@to_country", 50, order.ToCountry),
         SqlUtilities.GenerateInputNVarcharParameter("@to_address", 200, order.ToAddress),
         SqlUtilities.GenerateInputNVarcharParameter("@to_postcode", 50, order.ToPostcode),
         SqlUtilities.GenerateInputNVarcharParameter("@remark", 500, order.Remark)
     };
     string sql = "UPDATE orders SET to_username = @to_username, to_phone = @to_phone, to_email = @to_email, to_city = @to_city, remark = @remark, to_country = @to_country, to_address = @to_address, to_postcode = @to_postcode WHERE id = @id";
     SqlHelper.ExecuteNonQuery(CommandType.Text, sql, param);
 }
コード例 #19
0
ファイル: OrderDAL.cs プロジェクト: feidu/XSEMS
 public Order GetOrderById(int id)
 {
     Order order = null;
     SqlParameter[] param = new SqlParameter[] {
         SqlUtilities.GenerateInputIntParameter("@id", id)
     };
     string sql = "SELECT id, client_id, encode, status, costs, receive_date, create_time, remark, is_mail_send, audit_user_id, audit_time, check_user_id, check_time, reason, to_username, to_phone, to_email, to_city, to_country, to_address, to_postcode, self_costs FROM orders WHERE id = @id";
     using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
     {
         while (dr.Read())
         {
             order = new Order();
             order.Id = dr.GetInt32(0);
             Client client = new ClientDAL().GetClientById(dr.GetInt32(1));
             order.Client = client;
             order.Encode = dr.GetString(2);
             order.Status = EnumConvertor.ConvertToOrderStatus(dr.GetByte(3));
             order.Costs = dr.GetDecimal(4);
             if (!dr.IsDBNull(5))
             {
                 order.ReceiveDate = dr.GetDateTime(5);
             }
             order.CreateTime = dr.GetDateTime(6);
             order.Remark = dr.GetString(7);
             order.IsMailSend = dr.GetBoolean(8);
             if (!dr.IsDBNull(9))
             {
                 order.AuditUserId = dr.GetInt32(9);
             }
             if (!dr.IsDBNull(10))
             {
                 order.AuditTime = dr.GetDateTime(10);
             }
             if (!dr.IsDBNull(11))
             {
                 order.CheckUserId = dr.GetInt32(11);
             }
             if (!dr.IsDBNull(12))
             {
                 order.CheckTime = dr.GetDateTime(12);
             }
             if (!dr.IsDBNull(13))
             {
                 order.Reason = dr.GetString(13);
             }
             order.ToUsername = dr.GetString(14);
             order.ToPhone = dr.GetString(14);
             order.ToEmail = dr.GetString(16);
             order.ToCity = dr.GetString(17);
             order.ToCountry = dr.GetString(18);
             order.ToAddress = dr.GetString(19);
             order.ToPostcode = dr.GetString(20);
             order.SelfCosts = dr.GetDecimal(21);
         }
     }
     return order;
 }
コード例 #20
0
ファイル: OrderDAL.cs プロジェクト: feidu/XSEMS
 public void UpdateOrder(Order order)
 {
     SqlParameter[] param = new SqlParameter[] {
         SqlUtilities.GenerateInputIntParameter("@id", order.Id),
         //SqlUtilities.GenerateInputIntParameter("@create_user_id", order.CreateUser.Id),
         //SqlUtilities.GenerateInputDateTimeParameter("@receive_date", order.ReceiveDate),
         SqlUtilities.GenerateInputParameter("@costs", SqlDbType.Money, order.Costs),
         SqlUtilities.GenerateInputParameter("@self_costs", SqlDbType.Money, order.SelfCosts),
         //SqlUtilities.GenerateInputIntParameter("@receive_user_id", order.ReceiveUserId),
         SqlUtilities.GenerateInputParameter("@status", SqlDbType.TinyInt, (byte)order.Status),
         SqlUtilities.GenerateInputNVarcharParameter("@remark", 500, order.Remark)
     };
     string sql = "UPDATE orders SET costs = @costs, self_costs= @self_costs, remark = @remark, status = @status WHERE id = @id";
     SqlHelper.ExecuteNonQuery(CommandType.Text, sql, param);
 }
コード例 #21
0
ファイル: EmailHelper.cs プロジェクト: sd1362168638/XSEMS
        public static bool SendMailForConsign(Company company, Client client, Order order, out string msg)
        {
            StringBuilder sb = new StringBuilder();
            List<OrderDetail> result = OrderDetailOperation.GetOrderDetailByOrderId(order.Id);

            sb.Append("&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;�����������εĻ��˵���<br/><br/>");
            sb.Append("<table border='1' cellspacing='0' cellpadding='0' width='100%' style='font-size:12px;'>");
            sb.Append("<tr>");
            sb.Append("<td width='9%' align='right' valign='middle'>�ռ�����:</td><td width='36%' align='left' valign='middle'>&nbsp;" + order.Encode + "</td>");
            sb.Append("<td width='9%' align='right' valign='middle'>�ռ�����:</td><td width='18%' align='left' valign='middle'>&nbsp;" + order.ReceiveDate.ToShortDateString() + "</td>");
            sb.Append("<td width='9%' align='right' valign='middle'>�ͻ����:</td><td width='19%' align='left' valign='middle'>&nbsp;" + order.Client.Id + "</td></tr>");
            sb.Append("<tr>");
            sb.Append("<td align='right' valign='middle'>�ͻ�����:</td><td align='left' valign='middle'>&nbsp;" + order.Client.RealName + "</td>");
            sb.Append("<td align='right' valign='middle'>��ϵ��:</td><td align='left' valign='middle'>&nbsp;" + order.Client.RealName + "</td>");
            sb.Append("<td align='right' valign='middle'>��ϵ�绰:</td><td align='left' valign='middle'>&nbsp;" + order.Client.Phone + "</td></tr>");
            sb.Append("<tr>");
            sb.Append("<td align='right' valign='middle'>��ϵ��ַ:</td><td align='left' valign='middle'>&nbsp;" + order.Client.Address + "</td>");
            sb.Append("<td align='right' valign='middle'>Ӧ���ܼ�:</td><td colspan='3' align='left' valign='middle'>&nbsp;" + order.Costs + " Ԫ</td></tr>");
            sb.Append("<tr>");
            sb.Append("<td width='9%' align='right' valign='middle'>��ע:</td><td colspan='5' align='left' valign='middle'>&nbsp;"+order.Remark+"</td></tr>");
            sb.Append("<tr><td colspan='6' height='8'></td></tr>");

            sb.Append("<tr><td colspan='6' valign='top'>");
            sb.Append("<table border='1' cellspacing='0' cellpadding='0' width='100%' style='border-top:0px; border-bottom:0px; border-left:0px; border-right:0px; line-height:18px;font-size:12px;'>");
            sb.Append("<tr>");
            sb.Append("<td width='4%' align='center' valign='middle'>���</td><td width='8%' align='right' valign='middle'>������</td>");
            sb.Append("<td width='7%' align='right' valign='middle'>�ʼ�����</td><td width='8%' align='right' valign='middle'>����(KG)</td>");
            sb.Append("<td width='7%' align='right' valign='middle'>�˷�(��)</td><td width='8%' align='right' valign='middle'>�Һŷ�(��)</td>");
            sb.Append("<td width='7%' align='right' valign='middle'>ƫԶ��(��)</td><td width='7%' align='right' valign='middle'>�����(��)</td>");
            sb.Append("<td width='7%' align='right' valign='middle'>ȡ����(��)</td><td width='7%' align='right' valign='middle'>���Ϸ�(��)</td>");
            sb.Append("<td width='7%' align='right' valign='middle'>���۷�(��)</td><td width='7%' align='right' valign='middle'>������(��)</td>");
            sb.Append("<td width='7%' align='right' valign='middle'>ȼ�ͷ�(��)</td>");
            sb.Append("<td width='10%' align='right' valign='middle'>Ӧ������(��)</td></tr>");
            foreach (OrderDetail od in result)
            {
                int i=1;
                sb.Append("<tr>");
                sb.Append("<td align='center' valign='middle'>"+i.ToString()+"</td><td align='right' valign='middle'>"+od.CarrierEncode+"</td>");
                sb.Append("<td align='right' valign='middle'>"+od.Count.ToString()+"</td><td align='right' valign='middle'>"+od.Weight.ToString()+"</td>");
                sb.Append("<td align='right' valign='middle'>"+od.PostCosts.ToString()+"</td><td align='right' valign='middle'>"+od.RegisterCosts.ToString()+"</td>");
                sb.Append("<td align='right' valign='middle'>"+od.RemoteCosts.ToString()+"</td><td align='right' valign='middle'>"+od.DisposalCosts.ToString()+"</td>");
                sb.Append("<td align='right' valign='middle'>"+od.FetchCosts.ToString()+"</td><td align='right' valign='middle'>"+od.MaterialCosts.ToString()+"</td>");
                sb.Append("<td align='right' valign='middle'>"+od.InsureCosts.ToString()+"</td><td align='right' valign='middle'>"+od.OtherCosts.ToString()+"</td>");
                sb.Append("<td align='right' valign='middle'>" + od.FuelCosts.ToString() + "</td>");
                sb.Append("<td align='right' valign='middle'>"+od.TotalCosts.ToString()+"</td></tr>");
                i++;
            }
            sb.Append("</table></td></tr>");
            sb.Append("<tr><td colspan='6' height='10'></td></tr>");
            sb.Append("<tr>");
            sb.Append("<td colspan='6' align='left' style='line-height:20px;'>");
            sb.Append("&nbsp;���Ļ����Ѿ��ﵽ���ǵĴ������ģ�������Сʱ�Ĵ����Ժ󼴽�����.<br />");
            sb.Append("&nbsp;����ϸ�˶Ի��˵�������,��������������ҵ����Ա��ϵ.<br />");
            sb.Append("&nbsp;����ĸ����������������վ(<a href='http://www.eadu.com.cn' target='_blank'>http://www.eadu.com.cn</a>)�ϲ�ѯ. <br />");
            sb.Append("&nbsp;�����˻����: <span style='color:#0000FF;'>" + client.Balance.ToString() + "</span> Ԫ </td></tr></table>");

            return SendMail(company, client, "���˵�", sb.ToString(), out msg);
        }
コード例 #22
0
ファイル: OrderOperation.cs プロジェクト: sd1362168638/XSEMS
 public static void UpdateOrder(Order order)
 {
     dal.UpdateOrder(order);
 }