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 }