📄 baseinfo.cs
字号:
/// <param name="AddTableName_trueName">数据库中数据表名称</param>
/// <returns></returns>
public int AddTableMainWarehouse(cBillInfo billinfo, string AddTableName_trueName)
{
SqlParameter[] prams = {
data.MakeInParam("@billdate", SqlDbType.DateTime, 8, billinfo.BillDate),
data.MakeInParam("@billcode", SqlDbType.VarChar, 20,billinfo.BillCode),
data.MakeInParam("@units", SqlDbType.VarChar, 30, billinfo.Units),
data.MakeInParam("@handle", SqlDbType.VarChar, 10, billinfo.Handle),
data.MakeInParam("@summary", SqlDbType.VarChar, 100, billinfo.Summary),
data.MakeInParam("@fullpayment", SqlDbType.Float, 8, billinfo.FullPayment),
data.MakeInParam("@payment", SqlDbType.Float, 8, billinfo.Payment),
};
return (data.RunProc("INSERT INTO " + AddTableName_trueName + " (billdate, billcode, units, handle, summary, fullpayment,payment) VALUES (@billdate,@billcode,@units,@handle,@summary,@fullpayment,@payment)", prams));
}
/// <summary>
/// 处理进货退货单和销售单-数据---向主表中添加数据
/// </summary>
/// <param name="billinfo">过账单据数据结构类对象</param>
/// <param name="AddTableName_trueName">数据库中数据表名称</param>
/// <returns></returns>
public int AddTableMainSellhouse(cBillInfo billinfo, string AddTableName_trueName)
{
SqlParameter[] prams = {
data.MakeInParam("@billdate", SqlDbType.DateTime, 8, billinfo.BillDate),
data.MakeInParam("@billcode", SqlDbType.VarChar, 20,billinfo.BillCode),
data.MakeInParam("@units", SqlDbType.VarChar, 30, billinfo.Units),
data.MakeInParam("@handle", SqlDbType.VarChar, 10, billinfo.Handle),
data.MakeInParam("@summary", SqlDbType.VarChar, 100, billinfo.Summary),
data.MakeInParam("@fullpayment", SqlDbType.Float, 8, billinfo.FullPayment),
data.MakeInParam("@payment", SqlDbType.Float, 8, billinfo.Payment),
};
return (data.RunProc("INSERT INTO " + AddTableName_trueName + " (billdate, billcode, units, handle, summary, fullgathering,gathering) VALUES (@billdate,@billcode,@units,@handle,@summary,@fullpayment,@payment)", prams));
}
/// <summary>
/// 向明细表中添加数据－进货单－销售退货单－销售单－进货退货单
/// </summary>
/// <param name="billinfo">过账单据数据结构类对象</param>
/// <param name="AddTableName_trueName">数据库中数据表名称</param>
/// <returns></returns>
public int AddTableDetailedWarehouse(cBillInfo billinfo, string AddTableName_trueName)
{
SqlParameter[] prams = {
data.MakeInParam("@billcode", SqlDbType.VarChar, 20, billinfo.BillCode),
data.MakeInParam("@tradecode", SqlDbType.VarChar, 20,billinfo.TradeCode),
data.MakeInParam("@fullname", SqlDbType.VarChar, 20, billinfo.FullName),
data.MakeInParam("@unit", SqlDbType.VarChar, 10, billinfo.TradeUnit),
data.MakeInParam("@qty", SqlDbType.Float, 8, billinfo.Qty),
data.MakeInParam("@price", SqlDbType.Float, 8, billinfo.Price),
data.MakeInParam("@tsum", SqlDbType.Float, 8, billinfo.TSum),
data.MakeInParam("@billdate", SqlDbType.DateTime, 8, billinfo.BillDate),
};
return (data.RunProc("INSERT INTO " + AddTableName_trueName + " (billcode, tradecode, fullname, unit, qty, price,tsum,billdate) VALUES (@billcode,@tradecode,@fullname,@unit,@qty,@price,@tsum,@billdate)", prams));
}
/// <summary>
/// 修改库存数量和加权平均价格
/// </summary>
/// <param name="stock">库存商品数据结构类对象</param>
/// <returns></returns>
public int UpdateStock_QtyAndAveragerprice(cStockInfo stock)
{
SqlParameter[] prams = {
data.MakeInParam("@tradecode", SqlDbType.VarChar, 5, stock.TradeCode),
data.MakeInParam("@qty", SqlDbType.Float, 30,stock.Qty),
data.MakeInParam("@price", SqlDbType.Float, 30,stock.Price),
data.MakeInParam("@averageprice", SqlDbType.Float, 10, stock.AveragePrice),
};
return (data.RunProc("update tb_stock set qty=@qty,price=@averageprice,averageprice=@averageprice where tradecode=@tradecode", prams));
}
/// <summary>
/// 修改销售商品和进货退货商品--后的库存商品数量
/// </summary>
/// <param name="stock"></param>
/// <returns></returns>
public int UpdateSaleStock_Qty(cStockInfo stock)
{
SqlParameter[] prams = {
data.MakeInParam("@tradecode", SqlDbType.VarChar, 5, stock.TradeCode),
data.MakeInParam("@qty", SqlDbType.Float, 30,stock.Qty),
};
return (data.RunProc("update tb_stock set qty=@qty where tradecode=@tradecode", prams));
}
/// <summary>
/// 修改库存数量和销售（和进货退货）最后一次价格
/// </summary>
/// <param name="stock">库存商品数据结构类对象</param>
/// <returns></returns>
public int UpdateStock_Qty(cStockInfo stock)
{
SqlParameter[] prams = {
data.MakeInParam("@tradecode", SqlDbType.VarChar, 5, stock.TradeCode),
data.MakeInParam("@qty", SqlDbType.Float, 30,stock.Qty),
data.MakeInParam("@price", SqlDbType.Float, 30,stock.SalePrice),
};
return (data.RunProc("update tb_stock set qty=@qty,saleprice=@price where tradecode=@tradecode", prams));
}
/// <summary>
/// 根据商品编号TradeCode,主要得到数量和加权平均价格，用于对其更新。
/// </summary>
/// <param name="stock">库存商品数据结构类对象</param>
/// <param name="tbName">映射虚拟表名称</param>
/// <returns></returns>
public DataSet GetStockByTradeCode(cStockInfo stock, string tbName)
{
SqlParameter[] prams = {
data.MakeInParam("@tradecode",SqlDbType.VarChar, 30, stock.TradeCode),
};
return (data.RunProcReturn("select * from tb_stock where tradecode like @tradecode", prams, tbName));
}
#endregion
#region 商品进销存---往来账明细表
/// <summary>
/// 添加数据---往来账本明细表
/// </summary>
/// <param name="currentAccount"></param>
/// <returns></returns>
public int AddCurrentAccount(cCurrentAccount currentAccount)
{
SqlParameter[] prams = {
data.MakeInParam("@billdate", SqlDbType.DateTime, 8, currentAccount.BillDate),
data.MakeInParam("@billcode", SqlDbType.VarChar, 20,currentAccount.BillCode),
data.MakeInParam("@addgathering", SqlDbType.Float, 8, currentAccount.AddGathering),
data.MakeInParam("@factaddfee", SqlDbType.Float, 8,currentAccount.FactAddFee),
data.MakeInParam("@reducegathering", SqlDbType.Float, 8,currentAccount.ReduceGathering),
data.MakeInParam("@factfee", SqlDbType.Float, 8, currentAccount.FactReduceGathering),
data.MakeInParam("@balance", SqlDbType.Float, 8, currentAccount.Balance),
data.MakeInParam("@units", SqlDbType.VarChar, 20,currentAccount.Units),
};
return (data.RunProc("INSERT INTO tb_currentaccount (billdate, billcode, addgathering, factaddfee, reducegathering, factfee,balance,units) VALUES (@billdate,@billcode,@addgathering,@factaddfee,@reducegathering,@factfee,@balance,@units)", prams));
}
#endregion
#region 进货管理--进货分析
/// <summary>
/// 商品进货分析--不含进货退货
/// </summary>
/// <param name="tbName"></param>
/// <returns></returns>
public DataSet BuyStockAnalyse(string tbName)
{
return (data.RunProcReturn("SELECT a.tradecode, a.fullname, a.averageprice as price, b.qty, b.tsum FROM tb_stock a INNER JOIN (SELECT SUM(qty) AS qty, SUM(tsum) AS tsum, fullname FROM tb_rewarehouse_detailed GROUP BY fullname) b ON a.fullname = b.fullname WHERE (a.price > 0)", tbName));
}
/// <summary>
/// 商品进货分析（含退货）
/// </summary>
/// <param name="tbName"></param>
/// <returns></returns>
public DataSet BuyAllStockAnalyse(string tbName)
{
return (data.RunProcReturn("select tradecode,fullname,AVG(price) AS price,sum(qty) as qty,sum(tsum) as tsum from tb_warehouse_detailed group by tradecode,fullname", tbName));
}
#endregion
#region 进货管理--进货统计
/// <summary>
/// 进货商品－－详细统计
/// </summary>
/// <param name="billinfo"></param>
/// <param name="tbName"></param>
/// <param name="starDateTime"></param>
/// <param name="endDateTime"></param>
/// <returns></returns>
public DataSet BuyStockSumDetailed(cBillInfo billinfo, string tbName, DateTime starDateTime, DateTime endDateTime)
{
SqlParameter[] prams = {
data.MakeInParam("@units", SqlDbType.VarChar, 30, "%"+billinfo.Units+"%"),
data.MakeInParam("@handle", SqlDbType.VarChar, 10,"%"+ billinfo.Handle+"%"),
};
return (data.RunProcReturn("SELECT b.tradecode AS 商品编号, b.fullname AS 商品名称, SUM(b.qty) AS 进货数量,SUM(b.tsum) AS 进货金额 FROM tb_warehouse_main a INNER JOIN (SELECT billcode, tradecode, fullname, SUM(qty) AS qty, SUM(tsum) AS tsum FROM tb_warehouse_detailed GROUP BY tradecode, billcode, fullname) b ON a.billcode = b.billcode AND a.units LIKE @units AND a.handle LIKE @handle WHERE (a.billdate BETWEEN '" + starDateTime + "' AND '" + endDateTime + "') GROUP BY b.tradecode, b.fullname",prams, tbName));
}
/// <summary>
/// 进货商品－－统计所有
/// </summary>
/// <param name="billinfo"></param>
/// <param name="tbName"></param>
/// <param name="starDateTime"></param>
/// <param name="endDateTime"></param>
/// <returns></returns>
public DataSet BuyStockSum(string tbName)
{
return (data.RunProcReturn("select tradecode as 商品编号,fullname as 商品名称,sum(qty) as 进货数量,sum(tsum)as 进货金额 from tb_warehouse_detailed group by tradecode, fullname", tbName));
}
#endregion
#region 销售管理--销售统计
/// <summary>
/// 销售商品－－详细统计
/// </summary>
/// <param name="billinfo"></param>
/// <param name="tbName"></param>
/// <param name="starDateTime"></param>
/// <param name="endDateTime"></param>
/// <returns></returns>
public DataSet SellStockSumDetailed(cBillInfo billinfo, string tbName, DateTime starDateTime, DateTime endDateTime)
{
SqlParameter[] prams = {
data.MakeInParam("@units", SqlDbType.VarChar, 30,"%"+ billinfo.Units+"%"),
data.MakeInParam("@handle", SqlDbType.VarChar, 10,"%"+ billinfo.Handle+"%"),
};
return (data.RunProcReturn("SELECT b.tradecode AS 商品编号, b.fullname AS 商品名称, SUM(b.qty) AS 销售数量,SUM(b.tsum) AS 销售金额 FROM tb_sell_main a INNER JOIN (SELECT billcode, tradecode, fullname, SUM(qty) AS qty, SUM(tsum) AS tsum FROM tb_sell_detailed GROUP BY tradecode, billcode, fullname) b ON a.billcode = b.billcode AND a.units LIKE @units AND a.handle LIKE @units WHERE (a.billdate BETWEEN '" +starDateTime+ "' AND '" +endDateTime+ "') GROUP BY b.tradecode, b.fullname", prams, tbName));
}
/// <summary>
/// 销售商品－－统计所有
/// </summary>
/// <param name="billinfo"></param>
/// <param name="tbName"></param>
/// <param name="starDateTime"></param>
/// <param name="endDateTime"></param>
/// <returns></returns>
public DataSet SellStockSum(string tbName)
{
return (data.RunProcReturn("select tradecode as 商品编号,fullname as 商品名称,sum(qty) as 销售数量,sum(tsum) as 销售金额 from tb_sell_detailed group by tradecode, fullname", tbName));
}
#endregion
#region 销售管理--月销售状况
/// <summary>
/// 统计商品销售状况
/// </summary>
/// <param name="tbName"></param>
/// <returns></returns>
public DataSet SellStockStatusSum(string tbName)
{
return (data.RunProcReturn("select a.tradecode as 商品编号,a.fullname as 商品名称,a.qty as 销售数量,a.price AS 销售均价,a.tsum as 销售金额,b.qty2 as '退货数量',b.tsum2 as '退货金额' from (SELECT tradecode,fullname,avg(price)as price,sum(qty) AS qty, sum(tsum) as tsum from tb_sell_detailed group by tradecode,fullname) a left join (SELECT tradecode,fullname,sum(qty) AS qty2, sum(tsum) as tsum2 from tb_resell_detailed group by tradecode,fullname) b on a.tradecode=b.tradecode ", tbName));
}
/// <summary>
/// 明细账本－－－‘商品销售’和‘商品销售退货’
/// </summary>
/// <param name="strTradeCode"></param>
/// <param name="starDateTime"></param>
/// <param name="endDateTime"></param>
/// <param name="tbName"></param>
/// <returns></returns>
public DataSet SellStockDetailed(string strTradeCode,DateTime starDateTime,DateTime endDateTime,string tbName)
{
return (data.RunProcReturn("SELECT billdate as 销售日期, billcode as 单据编号, tradecode as 商品编号, fullname as 商品名称, price as 销售价格, qty as 销售数量, tsum as 销售金额 FROM "+tbName+" where tradecode = '" + strTradeCode + "' AND billdate BETWEEN '" +starDateTime+ "' AND '" +endDateTime+"'", tbName));
}
#endregion
#region 销售管理--商品销售排行
/// <summary>
/// 设置排行榜条件－－往来单位-下拉列表
/// </summary>
/// <param name="tbName"></param>
/// <returns></returns>
public DataSet SetUnitsList(string tbName)
{
return (data.RunProcReturn("select * from tb_units", tbName));
}
/// <summary>
/// 设置排行榜条件－－经手人-下拉列表
/// </summary>
/// <param name="tbName"></param>
/// <returns></returns>
public DataSet SetHandleList(string tbName)
{
return (data.RunProcReturn("select * from tb_employee", tbName));
}
/// <summary>
/// 按销售金额排行
/// </summary>
/// <param name="handle"></param>
/// <param name="units"></param>
/// <param name="StarDateTime"></param>
/// <param name="EndDateTime"></param>
/// <returns></returns>
public DataSet GetTSumDesc(string handle,string units,DateTime StarDateTime,DateTime EndDateTime,string tbName)
{
return (data.RunProcReturn("SELECT * FROM (SELECT b.tradecode AS 商品编号, b.fullname AS 商品名称, SUM(b.qty) AS 销售数量, SUM(b.tsum) AS 销售金额 FROM tb_sell_main a INNER JOIN (SELECT billcode, tradecode, fullname, SUM(qty) AS qty, SUM(tsum) AS tsum FROM tb_sell_detailed GROUP BY tradecode, billcode, fullname) b ON a.billcode = b.billcode AND a.units LIKE '%" + units + "%' AND a.handle LIKE '%" + handle + "%' WHERE (a.billdate BETWEEN '" + StarDateTime + "' AND '" + EndDateTime + "')GROUP BY b.tradecode, b.fullname) DERIVEDTBL ORDER BY 销售金额 DESC", tbName));
}
/// <summary>
/// 按销售数量排行
/// </summary>
/// <param name="handle"></param>
/// <param name="units"></param>
/// <param name="StarDateTime"></param>
/// <param name="EndDateTime"></param>
/// <returns></returns>
public DataSet GetQtyDesc(string handle, string units, DateTime StarDateTime, DateTime EndDateTime, string tbName)
{
return (data.RunProcReturn("SELECT * FROM (SELECT b.tradecode AS 商品编号, b.fullname AS 商品名称, SUM(b.qty) AS 销售数量, SUM(b.tsum) AS 销售金额 FROM tb_sell_main a INNER JOIN (SELECT billcode, tradecode, fullname, SUM(qty) AS qty, SUM(tsum) AS tsum FROM tb_sell_detailed GROUP BY tradecode, billcode, fullname) b ON a.billcode = b.billcode AND a.units LIKE '%" + units + "%' AND a.handle LIKE '%" + handle + "%' WHERE (a.billdate BETWEEN '" + StarDateTime + "' AND '" + EndDateTime + "')GROUP BY b.tradecode, b.fullname) DERIVEDTBL ORDER BY 销售数量 DESC", tbName));
}
#endregion
#region 销售管理--商品销售成本明细
/// <summary>
/// 根据单据编号--得到销售明细表中数据
/// </summary>
/// <param name="stock"></param>
/// <param name="tbName"></param>
/// <returns></returns>
public DataSet GetDetailedkByBillCode(string billCode, string tbName)
{
return (data.RunProcReturn("SELECT tradecode,fullname,price,tsum,SUM(qty) AS qty FROM tb_sell_detailed WHERE (billcode = '" + billCode + "')group by tradecode,fullname,price,tsum",tbName));
}
/// <summary>
/// 根据单据编号--得到销售明细表中数据
/// </summary>
/// <param name="stock"></param>
/// <param name="tbName"></param>
/// <returns></returns>
public DataSet GetStockByTradeCode(string tradeCode, string tbName)
{
return (data.RunProcReturn("select * from tb_stock where tradecode ='" +tradeCode + "'", tbName));
}
/// <summary>
/// 根据日期－－查询销售主表中数据
/// </summary>
/// <param name="starDataTime"></param>
/// <param name="endDataTime"></param>
/// <returns></returns>
public DataSet FindSellStock(DateTime starDataTime,DateTime endDataTime)
{
return (data.RunProcReturn("select * from tb_sell_main where (billdate BETWEEN '" +starDataTime+ " ' AND '" +endDataTime+ " ')", "tb_sell_main"));
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -