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());
}