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