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