public static DataSet GetStockGoodsProviderData(string ProviderNO, bool ShowZeroStock)
{
string strSql = @"select Purchas.*,(incount - outcount) as stockcount,round(stockcount * inprice,2) as instat,goodsunit.UnitName,GoodsType.TypeName,Providers.ProviderName ";
strSql += @"from Purchas,Providers,goodsunit,GoodsType ";
if (ShowZeroStock)
{
strSql += "where Purchas.unitno = goodsunit.UnitNO and Providers.ProviderNO=purchas.providerno ";
}
else
{
strSql += "where Purchas.incount > Purchas.outcount and Purchas.unitno = goodsunit.UnitNO and Providers.ProviderNO=purchas.providerno ";
}
strSql += "and GoodsType.typeno=purchas.typeno and Purchas.ProviderNO=" + ProviderNO;
OleDbCommand cmd = new OleDbCommand(strSql, Foundation.CreateInstance());
OleDbDataAdapter ad = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
ad.Fill(ds);
return(ds);
}
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 void LoadProviderType(ListView box)
{
box.Items.Clear();
string strSql = @"select * from ProviderType where TypeNO > 0 order by TypeNO asc";
OleDbCommand cmd = new OleDbCommand(strSql, Foundation.CreateInstance());
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
foreach (DataRow dr in ds.Tables[0].Rows)
{
box.Items.Add(
new ListViewItem(
new string[] {
(box.Items.Count + 1).ToString().PadLeft(3, ' '),
dr["TypeDesc"].ToString()
})
{
Tag = dr["TypeNO"].ToString()
});
}
}
public bool Update()
{
string strSql1 = @"update ShopInfo set ItemValue=@shopaddress where ItemName = 'shopaddress';";
OleDbCommand cmd1 = new OleDbCommand(strSql1, Foundation.CreateInstance());
cmd1.Parameters.AddWithValue("@shopaddress", Shopaddress);
string strSql2 = @"update ShopInfo set ItemValue=@shoptel where ItemName = 'shoptel';";
OleDbCommand cmd2 = new OleDbCommand(strSql2, Foundation.CreateInstance());
cmd2.Parameters.AddWithValue("@shoptel", Shoptel);
string strSql3 = @"update ShopInfo set ItemValue=@shopname where ItemName = 'shopname';";
OleDbCommand cmd3 = new OleDbCommand(strSql3, Foundation.CreateInstance());
cmd3.Parameters.AddWithValue("@shopname", Shopname);
return(cmd1.ExecuteNonQuery() > 0 && cmd2.ExecuteNonQuery() > 0 && cmd3.ExecuteNonQuery() > 0);
}
public void ListUnit(ListView listview)
{
listview.Items.Clear();
string strSql = @"select * from GoodsUnit;";
OleDbCommand cmd = new OleDbCommand(strSql, Foundation.CreateInstance());
OleDbDataAdapter ad = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
ad.Fill(ds);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
listview.Items.Add(new ListViewItem(new string[] {
(listview.Items.Count + 1).ToString().PadLeft(3, ' '),
dr["UnitName"].ToString()
})
{
Tag = dr["UnitNO"].ToString()
});
}
}
}
public bool Add()
{
string strSql = @"insert into UserManage(userno,username,userpw,purchas,sell,stock,usermanage,remark,cancelsell,deletesell,providers,systemset,record,ShowProfit,SellInprice) ";
strSql += "values(@userno,@username,@userpw,@purchas,@sell,@stock,@usermanage,@remark,@cancelsell,@deletesell,@providers,@systemset,@record,@ShowProfit,@SellInprice);";
OleDbCommand cmd = new OleDbCommand(strSql, Foundation.CreateInstance());
cmd.Parameters.AddWithValue("@userno", UserNO);
cmd.Parameters.AddWithValue("@username", Username);
cmd.Parameters.AddWithValue("@userpw", Foundation.GetMD5String(PW));
cmd.Parameters.AddWithValue("@purchas", Purchas ? 1 : 0);
cmd.Parameters.AddWithValue("@sell", SellDay);
cmd.Parameters.AddWithValue("@stock", Stock ? 1 : 0);
cmd.Parameters.AddWithValue("@usermanage", Usermanage ? 1 : 0);
cmd.Parameters.AddWithValue("@remark", Remarks);
cmd.Parameters.AddWithValue("@cancelsell", CancelSell ? 1 : 0);
cmd.Parameters.AddWithValue("@deletesell", DeleteSell ? 1 : 0);
cmd.Parameters.AddWithValue("@providers", Providers ? 1 : 0);
cmd.Parameters.AddWithValue("@systemset", SystemSet ? 1 : 0);
cmd.Parameters.AddWithValue("@record", Record ? 1 : 0);
cmd.Parameters.AddWithValue("@ShowProfit", ShowProfit ? 1 : 0);
cmd.Parameters.AddWithValue("@SellInprice", SellInprice ? 1 : 0);
return(cmd.ExecuteNonQuery() > 0);
}
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);
}
}
public void Output()
{
Foundation.OutputCSV(this.lvUser, "用户" + System.DateTime.Now.ToString("yyyyMMddHHssmm") + ".csv");
}
public void Output()
{
Foundation.OutputCSV(this.lvProviders, "供应商" + System.DateTime.Now.ToString("yyyyMMddHHssmm") + ".csv");
}
/// <summary>
/// 设置软件版本号
/// </summary>
/// <param name="ver"></param>
/// <returns></returns>
public static bool SetAppVersion(decimal ver)
{
OleDbCommand cmdMax = new OleDbCommand("update otherinfo set itemvalue='" + ver.ToString() + "' where itemname = 'AppVersion'", Foundation.CreateInstance());
return(cmdMax.ExecuteNonQuery() > 0);
}
/// <summary>
/// 没有版本记录时返回 1.0
/// </summary>
/// <returns></returns>
public static decimal GetAppVersion()
{
string strSql = @"select itemvalue from OtherInfo where itemname = 'AppVersion'";
OleDbCommand cmd = new OleDbCommand(strSql, Foundation.CreateInstance());
object o = cmd.ExecuteScalar();
if (o == null)
{
OleDbCommand cmdMax = new OleDbCommand("select max(itemno) from otherinfo", Foundation.CreateInstance());
o = cmdMax.ExecuteScalar();
int i = Convert.ToInt32(o.ToString());
i = i + 1;
cmdMax = new OleDbCommand("insert into otherinfo values(" + i.ToString() + ",'AppVersion','1.0')", Foundation.CreateInstance());
cmdMax.ExecuteNonQuery();
return(1.0M);
}
else
{
return(Convert.ToDecimal(o.ToString()));
}
}
public static Object ReadObject(string sql)
{
OleDbCommand cmd = new OleDbCommand(sql, Foundation.CreateInstance());
return(cmd.ExecuteScalar());
}
public static bool RunSql(string sql)
{
OleDbCommand cmd1 = new OleDbCommand(sql, Foundation.CreateInstance());
return(cmd1.ExecuteNonQuery() > 0);
}
private void OtherInfo_Load(object sender, EventArgs e)
{//Purchas Stock sell
#region Purchas
DataSet dsPurchas = Foundation.GetGoodsPurchas(m_ID);
this.lvPurchas.Items.Clear();
foreach (DataRow dr in dsPurchas.Tables[0].Rows)
{
this.lvPurchas.Items.Add(new ListViewItem(new string[] {
dr["intime"].ToString(),
dr["goodsname"].ToString(),
dr["incount"].ToString(),
dr["inprice"].ToString(),
dr["instat"].ToString(),
dr["fixprice"].ToString()
}));
}
#endregion
#region sell
if (m_SystemUser.SellInprice == false)
{
this.lvPurchas.Columns[3].Width = 0;
this.lvPurchas.Columns[4].Width = 0;
this.lvSell.Columns[4].Width = 0;
this.lvStock.Columns[3].Width = 0;
this.lvStock.Columns[5].Width = 0;
}
DataSet dsSell = Foundation.GetGoodsSell(m_ID);
this.lvSell.Items.Clear();
decimal profitAll = 0;
int CountAll = 0;
foreach (DataRow dr in dsSell.Tables[0].Rows)
{
//decimal profit = Convert.ToDecimal(dr["outcount"]) * (Convert.ToDecimal(dr["outprice"]) - Convert.ToDecimal(dr["fixprice"]));
//profitAll += profit;
CountAll += Convert.ToInt32(dr["outcount"]);
this.lvSell.Items.Add(new ListViewItem(new string[] { (this.lvSell.Items.Count + 1).ToString().PadLeft(3, ' '),
dr["outtime"].ToString(),
dr["goodsname"].ToString(),
dr["outcount"].ToString(),
dr["inprice"].ToString(),
dr["fixprice"].ToString(),
dr["outprice"].ToString(),
dr["outstat"].ToString() }));
}
string strSell = Foundation.SellTotal(m_ID);
if (strSell != string.Empty)
{
this.lvSell.Items.Add(new ListViewItem(new string[] { "合计", "", "", CountAll.ToString(), "", "", "", strSell }));
}
#endregion
#region Stock
DataSet dsStock = Foundation.GetStockGoods(m_ID);
this.lvStock.Items.Clear();
foreach (DataRow dr in dsStock.Tables[0].Rows)
{
this.lvStock.Items.Add(new ListViewItem(new string[] {
dr["intime"].ToString(),
dr["goodsname"].ToString(),
dr["stockcount"].ToString(),
dr["inprice"].ToString(),
dr["fixprice"].ToString(),
dr["instat"].ToString()
})); //库存金额
}
#endregion
}