/// <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);
}
/// <summary>
/// 获取所有的发票明细
/// </summary>
/// <returns>返回一个DataTable表</returns>
public DataTable GetAllInvoiceDetail()
{
string sql = "SELECT * FROM invoiceinfo WHERE flag = '0'";
DataTable dt = SqliteConn.ExecuteTable(sql);
return(dt);
}
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);
}
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);
}