public static DataSet GetProviderData(string ProviderNO) { string strSql = @"select Purchas.*,round(incount * inprice,2) as instat,goodsunit.UnitName,GoodsType.TypeName,Providers.ProviderName "; strSql += "from Purchas,Providers,goodsunit,GoodsType "; strSql += "where Purchas.unitno = goodsunit.UnitNO and Providers.ProviderNO=purchas.providerno and GoodsType.typeno=purchas.typeno and Purchas.ProviderNO =" + ProviderNO + " order by intime desc"; OleDbCommand cmd = new OleDbCommand(strSql, Foundation.CreateInstance()); OleDbDataAdapter ad = new OleDbDataAdapter(cmd); DataSet ds = new DataSet(); ad.Fill(ds); return(ds); }
public bool ExistUser(int Userno, string NewUsername) { string strSql = string.Empty; strSql = @"select * from UserManage where Username=@Username and Userno <> " + Userno.ToString(); OleDbCommand cmd = new OleDbCommand(strSql, Foundation.CreateInstance()); cmd.Parameters.AddWithValue("@Username", NewUsername); OleDbDataAdapter da = new OleDbDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); return(ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0); }
private static int NewProviderTypeNO() { string strSql = @"select max(TypeNO) from ProviderType;"; OleDbCommand cmd = new OleDbCommand(strSql, Foundation.CreateInstance()); object o = cmd.ExecuteScalar(); if (o == DBNull.Value) { return(1); } else { return(1 + Convert.ToInt32(o)); } }
/// <summary> /// 销售新的单号 /// </summary> /// <returns></returns> public static int GetNewOrderNO() { string strSql = @"select max(OrderNO) from Sell;"; OleDbCommand cmd = new OleDbCommand(strSql, Foundation.CreateInstance()); object o = cmd.ExecuteScalar(); if (o == DBNull.Value) { return(1); } else { return(1 + Convert.ToInt32(o)); } }
private void NewProviderNO() { string strSql = @"select max(ProviderNO) from Providers;"; OleDbCommand cmd = new OleDbCommand(strSql, Foundation.CreateInstance()); object o = cmd.ExecuteScalar(); if (o == DBNull.Value) { ProviderNO = 1; } else { ProviderNO = 1 + Convert.ToInt32(o); } }
public static DataSet GetOrderNOData(string OrderNO) { 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.fixprice,purchas.fixprice,goodsunit.UnitName,GoodsType.TypeName,Providers.ProviderName "; strSql += " from Sell,purchas,goodsunit,Providers,GoodsType where Purchas.unitno = goodsunit.UnitNO and "; strSql += "Providers.ProviderNO=purchas.providerno and GoodsType.typeno=purchas.typeno and Sell.goodsno=purchas.goodsno and sell.orderno=" + OrderNO; OleDbCommand cmd = new OleDbCommand(strSql, Foundation.CreateInstance()); OleDbDataAdapter ad = new OleDbDataAdapter(cmd); DataSet ds = new DataSet(); ad.Fill(ds); return(ds); }
public void NewTypeNO() { string strSql = @"select max(TypeNO) from GoodsType;"; OleDbCommand cmd = new OleDbCommand(strSql, Foundation.CreateInstance()); object o = cmd.ExecuteScalar(); if (o == DBNull.Value) { this.TypeNO = 1; } else { this.TypeNO = 1 + Convert.ToInt32(o); } }
public static void ListType(ComboBox box) { box.Items.Clear(); string strSql = @"select * from GoodsType;"; 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) { box.Items.Add(new ComboBoxItem(dr["TypeName"].ToString(), dr["TypeNO"].ToString())); } } }
public static void LoadProviderType(ComboBox box) { box.Items.Clear(); string strSql = @"select * from ProviderType order by TypeNO asc"; OleDbCommand cmd = new OleDbCommand(strSql, Foundation.CreateInstance()); OleDbDataAdapter da = new OleDbDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); ComboBoxItem cbb; foreach (DataRow dr in ds.Tables[0].Rows) { cbb = new ComboBoxItem(dr["TypeDesc"].ToString(), dr["TypeNO"].ToString()); box.Items.Add(cbb); } }
private void NewUserNO() { string strSql = @"select max(userno) from UserManage"; OleDbCommand cmd = new OleDbCommand(strSql, Foundation.CreateInstance()); OleDbDataAdapter da = new OleDbDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); if (ds == null || ds.Tables.Count < 1 || ds.Tables[0].Rows.Count < 1) { this.UserNO = 1; } else { this.UserNO = 1 + Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString()); } }
public static void ListProviders(TreeView tree) { string strSql = @"select ProviderNO,ProviderName from Providers"; OleDbCommand cmd = new OleDbCommand(strSql, Foundation.CreateInstance()); OleDbDataAdapter da = new OleDbDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); tree.Nodes.Clear(); foreach (DataRow dr in ds.Tables[0].Rows) { tree.Nodes.Add(new TreeNode(dr["ProviderName"].ToString()) { Tag = dr["ProviderNO"].ToString() }); } tree.ExpandAll(); }
public static DataSet GetGoodsSell(int GoodsNO) { string strSql = "select Sell.*,round(sell.outcount * sell.outprice,2) as outstat,"; strSql += "round(sell.outcount * (sell.outprice-purchas.inprice),2) as profit,"; strSql += "(select fixprice from purchas where sell.goodsno = purchas.goodsno) as fixprice,"; strSql += "(select inprice from purchas where sell.goodsno = purchas.goodsno) as inprice,"; strSql += "(select goodsname from purchas "; strSql += "where sell.goodsno = purchas.goodsno) as goodsname from Sell,purchas where sell.goodsno=purchas.goodsno and purchas.goodsno = @goodsno"; OleDbCommand cmd = new OleDbCommand(strSql, Foundation.CreateInstance()); cmd.Parameters.AddWithValue("@goodsno", GoodsNO); OleDbDataAdapter ad = new OleDbDataAdapter(cmd); DataSet ds = new DataSet(); ad.Fill(ds); return(ds); }
public static void LoadProviderTypeList(ListView lvType) { string strSql = @"select * from ProviderType where TypeNO > 0"; OleDbCommand cmd = new OleDbCommand(strSql, Foundation.CreateInstance()); OleDbDataAdapter da = new OleDbDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); int iIndex = 0; foreach (DataRow dr in ds.Tables[0].Rows) { lvType.Items.Add(new ListViewItem(new string[] { iIndex++.ToString(), dr["TypeDesc"].ToString() }) { Tag = dr["TypeNO"].ToString() }); } }
public static DataSet GetSellTimeData(string SellTime, 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,purchas.fixprice,goodsunit.UnitName,GoodsType.TypeName,Providers.ProviderName "; strSql += " from Sell,purchas,goodsunit,Providers,GoodsType where Purchas.unitno = goodsunit.UnitNO and "; strSql += "Providers.ProviderNO=purchas.providerno and GoodsType.typeno=purchas.typeno and Sell.goodsno=purchas.goodsno and sell.outtime='" + SellTime + "'"; // -1, 0, 1, 5 if (InDaycount > 0) { strSql += " and datediff('d',sell.outtime,now) < " + InDaycount.ToString(); } OleDbCommand cmd = new OleDbCommand(strSql, Foundation.CreateInstance()); OleDbDataAdapter ad = new OleDbDataAdapter(cmd); DataSet ds = new DataSet(); ad.Fill(ds); return(ds); }
public static int AddProviderType(string ProviderTypeName) { string strSql = @"insert into ProviderType(TypeNO,TypeDesc)"; strSql += " values(@TypeNO,@TypeDesc);"; OleDbCommand cmd = new OleDbCommand(strSql, Foundation.CreateInstance()); int iReturn = NewProviderTypeNO(); cmd.Parameters.AddWithValue("@TypeNO", iReturn); cmd.Parameters.AddWithValue("@TypeDesc", ProviderTypeName); if (cmd.ExecuteNonQuery() > 0) { return(iReturn); } else { return(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 DataSet GetStockGoodsTypeData(string TypeNO, 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.TypeNO=" + TypeNO; OleDbCommand cmd = new OleDbCommand(strSql, Foundation.CreateInstance()); OleDbDataAdapter ad = new OleDbDataAdapter(cmd); DataSet ds = new DataSet(); ad.Fill(ds); return(ds); }
public bool Save() { string strSql = @"insert into Providers(ProviderNO,ProviderName,ProviderType,Header,Tel,Email,WebSite,Code,Fax,Address,Remarks)"; strSql += " values(@ProviderNO,@ProviderName,@ProviderType,@Header,@Tel,@Email,@WebSite,@Code,@Fax,@Address,@Remarks);"; OleDbCommand cmd = new OleDbCommand(strSql, Foundation.CreateInstance()); cmd.Parameters.AddWithValue("@ProviderNO", ProviderNO); cmd.Parameters.AddWithValue("@ProviderName", ProviderName); cmd.Parameters.AddWithValue("@ProviderType", ProviderType); cmd.Parameters.AddWithValue("@Header", Header); cmd.Parameters.AddWithValue("@Tel", Tel); cmd.Parameters.AddWithValue("@Email", Email); cmd.Parameters.AddWithValue("@WebSite", WebSite); cmd.Parameters.AddWithValue("@Code", Code); cmd.Parameters.AddWithValue("@Fax", Fax); cmd.Parameters.AddWithValue("@Address", Address); cmd.Parameters.AddWithValue("@Remarks", Remarks); return(cmd.ExecuteNonQuery() > 0); }
public bool Update() { string strSql = @"update Providers set ProviderName=@ProviderName,ProviderType=@ProviderType,Header=@Header,"; strSql += " Tel=@Tel,Email=@Email,WebSite=@WebSite,Code=@Code,Fax=@Fax,Address=@Address,Remarks=@Remarks where ProviderNO=@ProviderNO;"; OleDbCommand cmd = new OleDbCommand(strSql, Foundation.CreateInstance()); cmd.Parameters.AddWithValue("@ProviderName", ProviderName); cmd.Parameters.AddWithValue("@ProviderType", ProviderType); cmd.Parameters.AddWithValue("@Header", Header); cmd.Parameters.AddWithValue("@Tel", Tel); cmd.Parameters.AddWithValue("@Email", Email); cmd.Parameters.AddWithValue("@WebSite", WebSite); cmd.Parameters.AddWithValue("@Code", Code); cmd.Parameters.AddWithValue("@Fax", Fax); cmd.Parameters.AddWithValue("@Address", Address); cmd.Parameters.AddWithValue("@Remarks", Remarks); cmd.Parameters.AddWithValue("@ProviderNO", ProviderNO); return(cmd.ExecuteNonQuery() > 0); }
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 ListType(TreeView tree) { tree.Nodes.Clear(); string strSql = @"select * from GoodsType;"; 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) { tree.Nodes.Clear(); foreach (DataRow dr in ds.Tables[0].Rows) { tree.Nodes.Add(new TreeNode(dr["TypeName"].ToString()) { Tag = dr["TypeNO"].ToString() }); } tree.ExpandAll(); } }
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 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 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); }
/// <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); }
public static bool RunSql(string sql) { OleDbCommand cmd1 = new OleDbCommand(sql, Foundation.CreateInstance()); return(cmd1.ExecuteNonQuery() > 0); }
public static Object ReadObject(string sql) { OleDbCommand cmd = new OleDbCommand(sql, Foundation.CreateInstance()); return(cmd.ExecuteScalar()); }