public List<ReceivableAccount> GetReceivableAccount()
{
List<ReceivableAccount> result = new List<ReceivableAccount>();
string sql = "SELECT id, company_id, payment_method_id, account_number, account_name, bank_name, remark FROM receivable_accounts";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, null))
{
while (dr.Read())
{
ReceivableAccount ra = new ReceivableAccount();
ra.Id = dr.GetInt32(0);
ra.CompanyId = dr.GetInt32(1);
Company comp = new CompanyDAL().GetCompanyById(ra.CompanyId);
ra.CompanyName = comp.Name;
ra.PaymentMethod = new PaymentMethod();
ra.PaymentMethod.Id = dr.GetInt32(2);
PaymentMethod pm = new PaymentMethodDAL().GetPaymentMethodById(ra.PaymentMethod.Id);
ra.PaymentMethod.Name = pm.Name;
ra.AccountNumber = dr.GetString(3);
ra.AccountName = dr.GetString(4);
ra.BankName = dr.GetString(5);
ra.Remark = dr.GetString(6);
result.Add(ra);
}
}
return result;
}
public List<ReceivableAccount> GetReceivableAccountByCompanyId(int compId)
{
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputIntParameter("@company_id", compId)
};
List<ReceivableAccount> result = new List<ReceivableAccount>();
string sql = "SELECT id, company_id, payment_method_id, account_number, account_name, bank_name, remark FROM receivable_accounts WHERE company_id = @company_id AND is_delete = 0";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
ReceivableAccount ra = new ReceivableAccount();
ra.Id = dr.GetInt32(0);
ra.CompanyId = dr.GetInt32(1);
Company comp = new CompanyDAL().GetCompanyById(ra.CompanyId);
ra.CompanyName = comp.Name;
ra.PaymentMethod = new PaymentMethod();
ra.PaymentMethod.Id = dr.GetInt32(2);
PaymentMethod pm = new PaymentMethodDAL().GetPaymentMethodById(ra.PaymentMethod.Id);
ra.PaymentMethod.Name = pm.Name;
ra.AccountNumber = dr.GetString(3);
ra.AccountName = dr.GetString(4);
ra.BankName = dr.GetString(5);
ra.Remark = dr.GetString(6);
result.Add(ra);
}
}
return result;
}
public List<CompanySales> GetCompanySalesStatistic(DateTime startDate, DateTime endDate, int companyId, int userId)
{
List<CompanySales> result = new List<CompanySales>();
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputDateTimeParameter("@start_date", startDate),
SqlUtilities.GenerateInputDateTimeParameter("@end_date", endDate),
SqlUtilities.GenerateInputIntParameter("@company_id", companyId),
SqlUtilities.GenerateInputIntParameter("@user_id", userId)
};
string sqlParam = "";
DateTime minTime = new DateTime(1999, 1, 1);
if (startDate > minTime && endDate > minTime)
{
sqlParam += " AND O.create_time BETWEEN @start_date AND @end_date";
}
else if (startDate > minTime && endDate <= minTime)
{
sqlParam += " AND O.create_time >= @start_date ";
}
else if (startDate <= minTime && endDate > minTime)
{
sqlParam += " AND O.create_time <= @end_date";
}
if (companyId > 0)
{
sqlParam += " AND O.company_id = @company_id";
}
if (userId > 0)
{
sqlParam += " AND O.user_id = @user_id";
}
string sql = "SELECT C.id, SUM(O.costs) AS money, SUM(O.costs-O.self_costs) AS profit FROM companies AS C INNER JOIN orders AS O ON C.id = O.company_id WHERE C.is_delete = 0 AND O.is_delete = 0 AND O.status IN(4,5) "+sqlParam+" GROUP BY C.id";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
CompanySales cs = new CompanySales();
Company company = new CompanyDAL().GetCompanyById(dr.GetInt32(0));
cs.Company = company;
cs.Money = dr.GetDecimal(1);
cs.Profit = dr.GetDecimal(2);
result.Add(cs);
}
}
return result;
}
public List<ClientOrder> GetClientOrderListByParameters(int clientId, DateTime startDate, DateTime endDate)
{
List<ClientOrder> result = new List<ClientOrder>();
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputDateTimeParameter("@start_date", startDate),
SqlUtilities.GenerateInputDateTimeParameter("@end_date", endDate),
SqlUtilities.GenerateInputIntParameter("@client_id", clientId)
};
string sqlParam = "";
DateTime minTime = new DateTime(1999, 1, 1);
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";
}
if (clientId >= 0)
{
sqlParam += " AND client_id = @client_id";
}
string sql = "SELECT id, client_id, client_address_id, real_name, phone, email, postcode, address, remark, create_time, country, city, encode FROM client_orders WHERE 1=1" + sqlParam + " ORDER BY create_time ASC";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
ClientOrder co = new ClientOrder();
co.Id = dr.GetInt32(0);
co.ClientId = dr.GetInt32(1);
co.ClientAddressId = dr.GetInt32(2);
co.RealName = dr.GetString(3);
co.Phone = dr.GetString(4);
co.Email = dr.GetString(5);
co.Postcode = dr.GetString(6);
co.Address = dr.GetString(7);
co.Remark = dr.GetString(8);
co.CreateTime = dr.GetDateTime(9);
co.Country = dr.GetString(10);
co.City = dr.GetString(11);
co.Encode = dr.GetString(12);
//ClientAddress ca = new ClientAddress();
//ca = new ClientAddressDAL().GetClientAddressById(co.ClientAddressId);
//if (ca != null)
//{
// co.PostAddress = ca.Address + ", " + ca.Province;
//}
//else
//{
// co.PostAddress = "";
//}
Client client = new ClientDAL().GetClientById(co.ClientId);
if (client != null)
{
Company comp = new CompanyDAL().GetCompanyById(client.CompanyId);
if (comp != null)
{
switch (comp.Name)
{
case "亿度物流宁波总公司":
co.PostAddress = "站前路238 (NB)";
break;
case "亿度物流义乌分公司":
co.PostAddress = "站前路238 (YW)";
break;
case "亿度物流杭州分公司":
co.PostAddress = "站前路238 (HZ)";
break;
case "亿度物流上海分公司":
co.PostAddress = "站前路238 (SH)";
break;
case "亿度物流深圳分公司":
co.PostAddress = "站前路238 (SZ)";
break;
default:
co.PostAddress = "站前路238";
break;
}
}
}
result.Add(co);
}
}
return result;
}
public ReceivableAccount GetReceivableAccountByNumber(string number)
{
ReceivableAccount ra = null;
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputNVarcharParameter("@account_number", 50, number)
};
string sql = "SELECT id, company_id, payment_method_id, account_number, account_name, bank_name, remark FROM receivable_accounts WHERE account_number = @account_number";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
ra = new ReceivableAccount();
ra.Id = dr.GetInt32(0);
ra.CompanyId = dr.GetInt32(1);
Company comp = new CompanyDAL().GetCompanyById(ra.CompanyId);
ra.CompanyName = comp.Name;
ra.PaymentMethod = new PaymentMethod();
ra.PaymentMethod.Id = dr.GetInt32(2);
PaymentMethod pm = new PaymentMethodDAL().GetPaymentMethodById(ra.PaymentMethod.Id);
ra.PaymentMethod.Name = pm.Name;
ra.AccountNumber = dr.GetString(3);
ra.AccountName = dr.GetString(4);
ra.BankName = dr.GetString(5);
ra.Remark = dr.GetString(6);
}
}
return ra;
}
