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