public static DataSet LatelyAllSell(int InDaycount)
{
string strSql = @"select sell.*,round(sell.outcount * sell.outprice,2) as outstat,round(sell.outcount * (sell.outprice-purchas.inprice),2) as profit,";
strSql += "purchas.goodsname,purchas.goodscode,purchas.fixprice,goodsunit.UnitName,GoodsType.TypeName,Providers.ProviderName ";
strSql += " from Sell,purchas,goodsunit,Providers,GoodsType ";
strSql += " where Purchas.unitno = goodsunit.UnitNO and Providers.ProviderNO=purchas.providerno ";
strSql += " and GoodsType.typeno=purchas.typeno and Sell.goodsno=purchas.goodsno";
// -1, 0, 1, 5
if (InDaycount > 0)
{
strSql += " and datediff('d',sell.outtime,now) < " + InDaycount.ToString();
}
strSql += " order by sell.Outtime desc";
DataSet ds2 = Foundation.ReadDataSet(strSql);
return(ds2);
}
public static bool DeleteSellNO(string SellNO)
{//删除销售单
DataSet dsSell = Foundation.ReadDataSet("select * from Sell where sellno=" + SellNO);
OleDbConnection conn = Foundation.CreateInstance();
OleDbTransaction trans = conn.BeginTransaction();
string strSql = @"delete from Sell where sellno=@sellno;";
OleDbCommand cmd = new OleDbCommand(strSql, conn);
cmd.Parameters.AddWithValue("@sellno", SellNO);
cmd.Transaction = trans;
List <OleDbCommand> listCommand = new List <OleDbCommand>();
foreach (DataRow row in dsSell.Tables[0].Rows)
{//要减去销售数量 (已作废的不要再减数量了)
if (row["useing"].ToString() == "1")
{
string strSql2 = @"update purchas set outcount = outcount - @outcount where goodsno=@goodsno;";
OleDbCommand cmdUpdate = new OleDbCommand(strSql2, conn);
cmdUpdate.Parameters.AddWithValue("@outcount", row["OutCount"].ToString());
cmdUpdate.Parameters.AddWithValue("@goodsno", row["GoodsNO"].ToString());
cmdUpdate.Transaction = trans;
listCommand.Add(cmdUpdate);
}
}
try
{
cmd.ExecuteNonQuery();
listCommand.ForEach(c => c.ExecuteNonQuery());
trans.Commit();
return(true);
}
catch (Exception er)
{
Console.Write(er.Message + er.StackTrace);
trans.Rollback();
return(false);
}
}
public static bool CancelSellDateTime(string DateTimeString)
{
DataSet dsSell = Foundation.ReadDataSet("select * from Sell where outtime='" + DateTimeString + "'");
OleDbConnection conn = Foundation.CreateInstance();
OleDbTransaction trans = conn.BeginTransaction();
string strSql = @"update Sell set useing = 0 where Outtime=@Outtime;";
OleDbCommand cmd = new OleDbCommand(strSql, conn);
cmd.Parameters.AddWithValue("@Outtime", DateTimeString);
cmd.Transaction = trans;
List <OleDbCommand> listCommand = new List <OleDbCommand>();
foreach (DataRow row in dsSell.Tables[0].Rows)
{
string strSql2 = @"update purchas set outcount = outcount - @outcount where goodsno=@goodsno;";
OleDbCommand cmdUpdate = new OleDbCommand(strSql2, conn);
cmdUpdate.Parameters.AddWithValue("@outcount", row["OutCount"].ToString());
cmdUpdate.Parameters.AddWithValue("@goodsno", row["GoodsNO"].ToString());
cmdUpdate.Transaction = trans;
listCommand.Add(cmdUpdate);
}
try
{
cmd.ExecuteNonQuery();
listCommand.ForEach(c => c.ExecuteNonQuery());
trans.Commit();
return(true);
}
catch (Exception er)
{
Console.Write(er.Message + er.StackTrace);
trans.Rollback();
return(false);
}
}