/// <summary>
/// 获取所有的发票明细
/// </summary>
/// <returns>返回一个DataTable表</returns>
public DataTable GetAllInvoiceDetail()
{
string sql = "SELECT * FROM invoiceinfo WHERE flag = '0'";
DataTable dt = SqliteConn.ExecuteTable(sql);
return(dt);
}
/// <summary>
/// 获取所有发票信息的唯一项 不包含删除的
/// </summary>
/// <returns>返回发票信息类实例的List</returns>
public DataTable GetAllInvoiceInfos()
{
//string sql = "SELECT id, invoicecode, invoicenumber, date, buyersid, productname, productnumber, unitprice, money, taxrate, " +
// "taxamount, totalamount, totaltaxamount, moneyupper, moneylow, sellersid, comment, payee, \"check\", drawer, invoicestate, returnmoney " +
// "FROM invoiceinfo WHERE flag = '0' GROUP BY invoicenumber";
string sql =
"SELECT MIN(a.id), a.invoicecode, a.invoicenumber, a.date, a.buyersid, b.commpanyname, b.taxnumber, b.address, b.bank, " +
"a.productname, a.productnumber, a.unitprice, a.money, a.taxrate, a.taxamount, a.totalamount, a.totaltaxamount, " +
"a.moneyupper, a.moneylow, a.sellersid, c.commpanyname AS 销售方名称, c.taxnumber AS 销售方税号, c.address AS 销售方地址及电话, " +
"c.bank AS 销售方开户行及帐号, a.comment, a.payee, a.\"check\", a.drawer, a.invoicestate, a.returnmoney FROM invoiceinfo AS a " +
"LEFT OUTER JOIN commpanyinfo AS b ON a.buyersid = b.id LEFT OUTER JOIN commpanyinfo AS c ON a.sellersid = c.id WHERE a.flag = '0' " +
"GROUP BY invoicenumber";
DataTable dataTable = SqliteConn.ExecuteTable(sql);
//List<InvoiceInfo> list = new List<InvoiceInfo>();
//if (dataTable.Rows.Count > 0)
//{
// foreach (DataRow dr in dataTable.Rows)
// {
// list.Add(RowToinvoiceInfo(dr)); ;
// }
//}
return(dataTable);
}
public int AddInvoiceInfo(List <InvoiceInfo> list)
{
String sql = "INSERT INTO invoiceinfo VALUES(@id, @invoicecode, @invoicenumber, @date, @buyersid, @productname,@productnumber, @unitprice, @money, " +
"@taxrate, @taxamount, @totalamount, @totaltaxamount, @moneyupper, @moneylow, @sellersid, @comment, @payee, @check, @drawer, " +
"@invoicestate, @returnmoney, @flag) ";
List <List <SQLiteParameter> > list1 = new List <List <SQLiteParameter> >();
for (int i = 0; i < list.Count; i++)
{
list1.Add(AddAndUpdateParameter(1, list[i]));
}
return(SqliteConn.ExecuteNonQueryMulit(sql, list1));
}
public DataTable GetAllInvoiceDetail(DataTable dt)
{
string sql =
"SELECT a.*, b.commpanyname, c.commpanyname FROM invoiceinfo AS a " +
"LEFT OUTER JOIN commpanyinfo AS b ON a.buyersid = b.id" +
"LEFT OUTER JOIN commpanyinfo AS c ON a.sellersid = c.id" +
"WHERE " +
"a.invoicecode LIKE '%@invoiceCode%' AND a.invoicenumbe LIKE '%@invoiceNumber%' AND a.date BETWEEN '%@startTime%' AND '@endTime' AND" +
"a.productname LIKE '%@productName%' AND a.taxrate LIKE '%@taxRate%' AND a.comment LIKE '%@comment%' AND a.payee LIKE '%@payee%' AND a.\"check\" LIKE '%@check%' AND" +
"a.drawer LIKE '%@drawer%' AND a.invoicestate LIKE '%@invoiceState%' AND a.flag = '0' AND b.commpanyname LIKE '%@buyersCommpany%' AND c.commpanyname LIKE '%@sellersCommpany%'";
return(SqliteConn.ExecuteTable(sql, GetAllInvoiceDetailParameterSet(dt)));
}
/// <summary>
/// 双击发票信息某行 查看详细开票信息
/// </summary>
/// <param name="invoiceNumber">发票号码</param>
/// <returns>返回一个Datatable</returns>
public DataTable GetInvoiceNumberDetail(string invoiceNumber)
{
string sql =
"SELECT a.id, a.invoicecode, a.invoicenumber, a.date, a.buyersid, b.commpanyname, b.taxnumber, b.address, b.bank, " +
"a.productname, a.productnumber, a.unitprice, a.money, a.taxrate, a.taxamount, a.totalamount, a.totaltaxamount, " +
"a.moneyupper, a.moneylow, a.sellersid, c.commpanyname AS 销售方名称, c.taxnumber AS 销售方税号, c.address AS 销售方地址及电话, " +
"c.bank AS 销售方开户行及帐号, a.comment, a.payee, a.\"check\", a.drawer, a.invoicestate, a.returnmoney FROM invoiceinfo AS a " +
"LEFT OUTER JOIN commpanyinfo AS b ON a.buyersid = b.id LEFT OUTER JOIN commpanyinfo AS c ON a.sellersid = c.id WHERE flag = '0' " +
"AND invoicenumber = '" + invoiceNumber + "'";
DataTable dt = SqliteConn.ExecuteTable(sql);
return(dt);
}
/// <summary>
/// 获取发票货物明细
/// </summary>
/// <param name="str">发票号码</param>
/// <returns>返回DataTable</returns>
public DataTable GetProductDetail(string str)
{
//string sql =
//"SELECT id, productname, productnumber, unitprice, money, taxrate, taxamount FROM invoiceinfo " +
//"WHERE invoicenumber = '" + str + "'";
string sql =
"SELECT a.id, a.invoicecode, a.invoicenumber, a.date, a.buyersid, b.commpanyname AS buyersname , b.taxnumber AS buyerstaxnumber, b.address AS buyersaddress, b.bank AS buyersbank, " +
"a.productname, a.productnumber, a.unitprice, a.money, a.taxrate, a.taxamount, a.totalamount, a.totaltaxamount, " +
"a.moneyupper, a.moneylow, a.sellersid, c.commpanyname AS sellersname, c.taxnumber AS sellerstaxnumber, c.address AS sellersaddress, " +
"c.bank AS sellersbank, a.comment, a.payee, a.\"check\", a.drawer, a.invoicestate, a.returnmoney, a.flag FROM invoiceinfo AS a " +
"LEFT OUTER JOIN commpanyinfo AS b ON a.buyersid = b.id LEFT OUTER JOIN commpanyinfo AS c ON a.sellersid = c.id " +
"WHERE a.flag = '0' AND invoicenumber = '" + str + "'";
DataTable dt = SqliteConn.ExecuteTable(sql);
return(dt);
}
/// <summary>
/// 新增或修改一条语句 参数配置
/// </summary>
/// <param name="tmp">1--新增 2--修改</param>
/// <param name="sql">sql语句</param>
/// <param name="invoiceInfo"></param>
/// <returns></returns>
private int AddAndUpdateInvoiceInfo(int tmp, string sql, InvoiceInfo invoiceInfo)
{
SQLiteParameter[] sqLiteParameter =
{
new SQLiteParameter("@invoicecode", invoiceInfo.Invoicecode),
new SQLiteParameter("@invoicenumber", invoiceInfo.Invoicenumber),
new SQLiteParameter("@date", invoiceInfo.Date),
new SQLiteParameter("@buyersid", invoiceInfo.Buyersid),
new SQLiteParameter("@productname", invoiceInfo.Productname),
new SQLiteParameter("@productnumber", invoiceInfo.Productnumber),
new SQLiteParameter("@unitprice", invoiceInfo.Unitprice),
new SQLiteParameter("@money", invoiceInfo.Money),
new SQLiteParameter("@taxrate", invoiceInfo.Taxrate),
new SQLiteParameter("@taxamount", invoiceInfo.Taxamount),
new SQLiteParameter("@totalamount", invoiceInfo.Totalamount),
new SQLiteParameter("@totaltaxamount", invoiceInfo.Totaltaxamount),
new SQLiteParameter("@moneyupper", invoiceInfo.Moneyupper),
new SQLiteParameter("@moneylow", invoiceInfo.Moneylow),
new SQLiteParameter("@sellersid", invoiceInfo.Sellersid),
new SQLiteParameter("@comment", invoiceInfo.Comment),
new SQLiteParameter("@payee", invoiceInfo.Payee),
new SQLiteParameter("@check", invoiceInfo.Check),
new SQLiteParameter("@drawer", invoiceInfo.Drawer),
new SQLiteParameter("@invoicestate", invoiceInfo.Invoicestate),
new SQLiteParameter("@returnmoney", invoiceInfo.Returnmoney),
};
List <SQLiteParameter> list = new List <SQLiteParameter>();
list.AddRange(sqLiteParameter);
if (tmp == 1)
{
list.Add(new SQLiteParameter("@id", "NULL"));
}
else if (tmp == 2)
{
list.Add(new SQLiteParameter("@id", invoiceInfo.Id));
}
return(SqliteConn.ExecuteNonQuery(sql, list.ToArray()));
}
public List <CommpanyInfo> GetAllCommpanyInfos()
{
//string sql = "SELECT id,commpanyname,taxnumber,address,bank,contact,phone FROM commpanyinfo";
string sql = "SELECT * FROM commpanyinfo";
SqliteConn sqliteConn = new SqliteConn();
DataTable dataTable = SqliteConn.ExecuteTable(sql);
List <CommpanyInfo> list = new List <CommpanyInfo>();
if (dataTable.Rows.Count > 0)
{
foreach (DataRow dr in dataTable.Rows)
{
CommpanyInfo mem = RowToCommpanyInfo(dr);
list.Add(mem);
}
}
return(list);
}
/// <summary>
/// 发票修改
/// </summary>
/// <param name="tableName">数据库表名</param>
/// <param name="dt">DataTable 存放发票明细信息的表</param>
/// <param name="dt1">DataTable 存放发票明细基本信息的表</param>
/// <returns>返回影响行数</returns>
public int ModifyData(string tableName, DataTable dt, DataTable dt1)
{
List <string> sqlList = new List <string>();
List <List <SQLiteParameter> > paramList = new List <List <SQLiteParameter> >();
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i].RowState == DataRowState.Added)
{
string sql = "INSERT INTO " + tableName + " VALUES(NULL, @invoicecode, @invoicenumber, @date, " +
"@buyersid, @productname, @productnumber, @unitprice, " +
"@money, @taxrate, @taxamount, @totalamount, " +
"@totaltaxamount, @moneyupper, @moneylow, @sellersid, " +
"@comment, @payee, @check, @drawer, @invoicestate, " +
"@returnmoney, '0')";
sqlList.Add(sql);
paramList.Add(parametersesSet(i, dt, dt1));
}
else if (dt.Rows[i].RowState == DataRowState.Deleted)
{
string sql = "UPDATE " + tableName + " SET flag = '1' WHERE id = " + dt.Rows[0]["id", DataRowVersion.Original].ToString();
sqlList.Add(sql);
paramList.Add(null);
}
else
{
string sql = "UPDATE " + tableName + " SET invoicecode = @invoicecode, invoicenumber = @invoicenumber, date = @date, " +
"buyersid = @buyersid, productname = @productname, productnumber = @productnumber, unitprice = @unitprice, " +
"money = @money, taxrate = @taxrate, taxamount = @taxamount, totalamount = @totalamount, " +
"totaltaxamount = @totaltaxamount, moneyupper = @moneyupper, moneylow = @moneylow, sellersid = @sellersid, " +
"comment = @comment, payee = @payee, \"check\" = @check, drawer = @drawer, invoicestate = @invoicestate, " +
"returnmoney = @returnmoney WHERE id = " + dt.Rows[i]["id"];
sqlList.Add(sql);
paramList.Add(parametersesSet(i, dt, dt1));
}
}
return(SqliteConn.ExeCuteNonQueryMulit(sqlList, paramList));
}
/// <summary>
/// 删除一条发票信息
/// </summary>
/// <param name="tableName">数据库表名</param>
/// <param name="dgvRow">DataGridViewRow</param>
/// <returns>返回影响行数 </returns>
public int DeleteSingleInfo(string tableName, DataGridViewRow dgvRow)
{
string sql = "UPDATE " + tableName + " SET flag = '1' WHERE invoicenumber = '" + dgvRow.Cells["invoicenumber"].Value.ToString() + "'";
return(SqliteConn.ExecuteNonQuery(sql));
}