/// <summary>
/// 按明细
/// </summary>
/// <param name="one"></param>
/// <param name="index"></param>
private void sellprofit_to_grid(SellProfit one, int index)
{
this.dataGridView1.Rows[index].Cells["serial"].Value = one.serial;
this.dataGridView1.Rows[index].Cells["sell_time"].Value = one.sell_time.ToShortDateString();
this.dataGridView1.Rows[index].Cells["customer"].Value = one.customer;
this.dataGridView1.Rows[index].Cells["type"].Value = ProductCirculation.CirculationTypeConfs[one.type - 1].name;
this.dataGridView1.Rows[index].Cells["product"].Value = one.product;
this.dataGridView1.Rows[index].Cells["unit"].Value = one.unit;
this.dataGridView1.Rows[index].Cells["sell_cnt"].Value = one.cnt.ToString("+#;-#;0");
this.dataGridView1.Rows[index].Cells["sell_price"].Value = one.price;
this.dataGridView1.Rows[index].Cells["sell_sum_price"].Value = one.sum_price.ToString("+0.00;-0.00;0");
//cost在数据库的小数位数不限制，但是显示出来只保留到分
this.dataGridView1.Rows[index].Cells["cost"].Value = one.cost.ToString("0.00");
this.dataGridView1.Rows[index].Cells["profit"].Value = one.profit;
this.dataGridView1.Rows[index].Cells["profit_margin"].Value = one.profit_margin;
this.dataGridView1.Rows[index].Cells["sum_cost"].Value = one.sum_cost.ToString("+0.00;-0.00;0");
this.dataGridView1.Rows[index].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
// 设置亏损，显示为红色
if (Convert.ToDouble(this.dataGridView1.Rows[index].Cells["profit"].Value) <= 0.0000)
{
this.dataGridView1.Rows[index].DefaultCellStyle.ForeColor = Color.Red;
}
// 记录统计信息
statistic_record.ID++;
statistic_record.cnt += one.cnt;
statistic_record.sum_cost += one.sum_cost;
statistic_record.sum_price += one.sum_price;
statistic_record.profit += one.profit;
}
protected override void updateCostAndProfit(ProductCirculation cir, ProductCirculationRecord record)
{
/*********更新数量和成本总价**********/
ProductStainlessDao stainlessDao = cirDao.getProductDao() as ProductStainlessDao;
ProductStainless stainless = stainlessDao.FindByID(record.ProductID);
double leftNum = stainless.Num + conf.productDirection * record.TotalNum;
//只有采购入货才需要更新成本价，通过成本价重新计算来抵冲收付的差额
//销售、销售退货、采购退货通过利润的计算来抵消收付的差额
//采购退货之所以不重新计算成本价，是因为如果退货价格很高的话，可能导致成本价为负数
if (conf.type == ProductCirculation.CirculationType.purchase)
{
double totalCost = stainless.PriceCost * stainless.Num + conf.productDirection * record.Price * record.TotalNum;
if (leftNum != 0)
{
double cost = totalCost / leftNum;
stainless.PriceCost = cost;
}
}
//double乘以int会出现0.9999999的问题
stainless.Num = (double)((decimal)stainless.Num + conf.productDirection * (decimal)record.TotalNum);
stainlessDao.Update(stainless);
/*************增加利润表记录**********/
if (conf.type == ProductCirculation.CirculationType.sell || conf.type == ProductCirculation.CirculationType.sellBack || conf.type == ProductCirculation.CirculationType.purchaseBack)
{
SellProfit profit = new SellProfit(cir, record, stainless.PriceCost);
SellProfitDao.getInstance().Insert(profit);
}
}
/// <summary>
/// 按产品统计
/// </summary>
private void initProducts()
{
List <SellProfit> done_profit_ls = SellProfitDao.getInstance().FindList(this.start_time.Value, this.end_time.Value.AddDays(1), this.textBox_product.Text, this.textBox_customer.Text);
SellProfit sell_profit_obj = new SellProfit();
// 根据货品进行分组
Dictionary <int, List <SellProfit> > group = SellProfitDao.getInstance().group_by_product_id(done_profit_ls);
foreach (KeyValuePair <int, List <SellProfit> > one in group)
{
int product_id = one.Key;
List <SellProfit> sell_profit_ls = one.Value;
SellProfit merge = new SellProfit();
foreach (SellProfit sell in sell_profit_ls)
{
merge.type = sell.type;
merge.product = sell.product;
merge.unit = sell.unit;
merge.cnt += sell.cnt;
merge.sum_price += sell.sum_price;
merge.sum_cost += sell.sum_cost;
merge.profit += sell.profit;
}
//List<double> profit = sell_profit_obj.get_profit(merge.sum_cost, merge.sum_price);
//这个地方没有考虑退货
merge.profit_margin = (merge.sum_price - merge.sum_cost) / merge.sum_cost * 100;
int index = this.dataGridView1.Rows.Add();
sellprofit_to_grid(merge, index);
}
}
public List <SellProfit> FindList(DateTime startTime, DateTime endTime, string product, string customer)
{
StringBuilder commandText = new StringBuilder();
//查找的字段名可以都使用<原始表名>.<字段>，至于结果，可以参考access查找出来的记
string temp = "select SellProfit.*, ProductStainlessCirculationRecord.unit, ProductStainlessCirculation.code, ProductStainlessCirculation.type, ProductStainlessCirculation.circulationTime, ProductStainless.serial, ProductStainless.name, ProductStainless.ID, Customer.name, Customer.ID"
+ " from SellProfit, ProductStainlessCirculationRecord, (select * from ProductStainlessCirculation left join Customer on Customer.ID = ProductStainlessCirculation.customerID ) circulation, ProductStainless"
+ " where SellProfit.record_id = ProductStainlessCirculationRecord.ID and"
+ " ProductStainlessCirculationRecord.circulationID = ProductStainlessCirculation.ID"
+ " and ProductStainlessCirculationRecord.productID = ProductStainless.ID"
+ " and ProductStainlessCirculation.circulationTime between #{0}# and #{1}#";
commandText.Append(string.Format(temp, startTime.ToString("yyyy-MM-dd"), endTime.ToString("yyyy-MM-dd")));
if (!string.IsNullOrEmpty(product))
{
commandText.Append(string.Format(" and ProductStainless.name like '%{0}%'", product));
}
else if (!string.IsNullOrEmpty(customer))
{
commandText.Append(string.Format(" and Customer.name like '%{0}%'", customer));
}
commandText.Append(" order by SellProfit.ID desc");
DataTable data = DbHelperAccess.executeQuery(commandText.ToString());
List <SellProfit> ls = new List <SellProfit>();
foreach (DataRow dr in data.Rows)
{
SellProfit sell = new SellProfit();
if (dr != null)
{
sell.ID = (int)dr["SellProfit.ID"];
sell.serial = dr["code"] as string;
sell.type = (int)dr["type"];
sell.sell_time = (DateTime)dr["circulationTime"];
sell.customerID = (int)dr["Customer.ID"];
sell.customer = dr["circulation.name"] as string;
sell.productID = (int)dr["ProductStainless.ID"];
sell.product = dr["ProductStainless.name"] as string;
sell.unit = dr["unit"] as string;
sell.cnt = (int)dr["cnt"];
sell.price = (double)dr["price"];
sell.sum_price = (double)dr["sum_price"];
sell.cost = (double)dr["cost"];
sell.profit = (double)dr["profit"];
sell.profit_margin = (double)dr["profit_margin"];
sell.sum_cost = (double)dr["sum_cost"];
sell.record_id = (int)dr["record_id"];
ls.Add(sell);
}
}
return(ls);
}
private void initListProduct()
{
statistic_record = new SellProfit();
this.dataGridView1.Rows.Clear();
// 使用已经计算好的SellProfit表来统计就可以了
initProducts();
initProductStatisticLine();
}
private void initListRecord()
{
statistic_record = new SellProfit();
this.dataGridView1.Rows.Clear();
List <SellProfit> done_profit_ls = SellProfitDao.getInstance().FindList(this.start_time.Value, this.end_time.Value.AddDays(1), this.textBox_product.Text, this.textBox_customer.Text);
initRecords(done_profit_ls);
initRecordStatisticLine();
}
public int Insert(SellProfit one)
{
try
{
string commandText = string.Format("insert into SellProfit(cnt, price, sum_price, cost, profit, profit_margin, sum_cost, record_id) values({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7})",
one.cnt, one.price, one.sum_price, one.cost, one.profit, one.profit_margin, one.sum_cost, one.record_id);
return(DbHelperAccess.executeNonQuery(commandText));
}
catch (Exception ex)
{
throw ex;
}
}