public string AccountingForQueryGYD(String StartFph, int Number) { List <AccountingInfo> accList = new List <AccountingInfo>(); string comm = @"SELECT top {1} kprq,fph,( SELECT SUM(kpje) FROM yx_t_ddfpgxb WHERE fpid = a.id ) AS je FROM yx_t_fpxxb AS a WHERE FPH>='{0}' ORDER BY fph"; comm = String.Format(comm, StartFph, Number); nrWebClass.LiLanzDAL dal = new nrWebClass.LiLanzDAL("14204"); using (IDataReader reader = dal.ExecuteReader(comm)) { while (reader.Read()) { AccountingInfo acc = new AccountingInfo(); acc.Rq = DateTime.Parse(reader[0].ToString()); acc.Fph = reader[1].ToString(); acc.Je = Decimal.Parse(reader[2].ToString()); accList.Add(acc); } } XmlSerializer serializer = new XmlSerializer(typeof(List <AccountingInfo>)); StringWriter textWriter = new StringWriter(); serializer.Serialize(textWriter, accList); return(textWriter.ToString()); }
public string AccountingForQuery(String StartFph, int Number, string filter) { List <AccountingInfo> accList = new List <AccountingInfo>(); string comm = @"select top {0} a.kprq,a.pjh,a.hsje,a.sje,a.hsje-a.sje as bhsje,a.khmc, case when a.djbs=1 and djzt=0 then '未开票' when a.djbs=1 and djzt=1 then '正常填开' when a.djbs=0 then '作废' end as fpzt, case a.pjlx when 11 then '普通发票' when 12 then '增值税专用发票' end as pjlx,djbs,djzt from zw_t_pjxxb a inner join zw_t_pjkcd b on a.rkid=b.id {1} where b.tzid=1 order by pjh"; string SqlFilter = ""; if (StartFph != "") { SqlFilter = String.Format(@" and a.pjh>={0} ", StartFph); } if (filter != "") { SqlFilter += filter; } comm = String.Format(comm, Number, SqlFilter); nrWebClass.LiLanzDAL dal = new nrWebClass.LiLanzDAL(); using (IDataReader reader = dal.ExecuteReader(comm)) { while (reader.Read()) { AccountingInfo acc = new AccountingInfo(); acc.Rq = DateTime.Parse(reader[0].ToString()); acc.Fph = reader[1].ToString(); acc.Je = Decimal.Parse(reader[2].ToString()); acc.Amount = Decimal.Parse(reader[4].ToString()); acc.DutyFee = Decimal.Parse(reader[3].ToString()); if (reader["djbs"].ToString() == "1") { if (reader["djzt"].ToString() == "1") { acc.Status = 1; } else { acc.Status = 0; } } else { acc.Status = 2; } accList.Add(acc); } } XmlSerializer serializer = new XmlSerializer(typeof(List <AccountingInfo>)); StringWriter textWriter = new StringWriter(); serializer.Serialize(textWriter, accList); return(textWriter.ToString()); }
public string InvioceList(DateTime dayStart, DateTime dayEnd, int companyId, string cat) { List <Invioce> list = new List <Invioce>(); string comm = String.Format(@"select a.id,shbs=case when a.shbs=1 then '已审' else '未审' end ,a.id,a.rq,a.djh,mx.je as je,isnull(a.shbs,0) as shbs, a.zdr,a.kprq,a.fph,a.fpzdr,d.mc as sskh,f.zhmc as khmc, h.dm+'.'+h.mc as fplx,ly.rq as lyrq,ly.djh as lydjh,a.jsrq,a.qsrq ,a.fpdm,k3.khdm+'.'+k3.khmc k3khmc , xt.mc as khfl,f.swdjh,f.yhzh,lxdz + ' ' + f.lxdh addr,d.Invoicer,a.fph, a.fpdm, a.kprq, f.khyh,mx.se as se,mx.bhsje,mx.sl as sl ,CASE WHEN mx.ls>8 THEN '是' ELSE '否' END AS isqd,isnull(a.fhr,'') as fhr,isnull(a.skr,'') as skr from yx_t_fpxxb a inner join yx_t_khmx d on a.sskh=d.id inner join (select id,sum(round(je,2)-ROUND(je/1.17,2)) as se,sum(je) as je,sum(ROUND(je/1.17,2)) as bhsje,sum(sl) as sl ,COUNT(*) AS ls from yx_T_fpxxmxb group by id) as mx on a.id=mx.id inner join zw_t_yhzlb f on a.khid=f.id left outer join zw_t_k3khdy k3 on a.k3khid=k3.id left outer join t_xtdm h on a.fplx=h.dm and h.ssid=7735 left join t_xtdm xt on k3.khfl=xt.dm and xt.ssid=8813 left outer join yx_t_hxdjb ly on a.lydjid=ly.id and a.lydjlx=ly.djlx and ly.tzid=1 where a.tzid=1 and a.djbs=1 and a.djlx=137 and a.rq>='{0:yyyy-MM-dd}' and a.rq<'{1:yyyy-MM-dd}' and a.sskh = {2} and a.fplx = '{3}'", dayStart, dayEnd.AddDays(1), companyId, cat); nrWebClass.LiLanzDAL dal = new nrWebClass.LiLanzDAL(); using (IDataReader reader = dal.ExecuteReader(comm)) { while (reader.Read()) { Invioce invoice = new Invioce(); invoice.Id = int.Parse(reader[0].ToString()); invoice.CName = reader["khmc"].ToString(); invoice.InvoiceDate = DateTime.Parse(reader["kprq"].ToString()); invoice.Cdate = DateTime.Parse(reader["rq"].ToString()); invoice.Sn = reader["djh"].ToString() + "|" + reader["isqd"].ToString(); invoice.AmountTotal = Decimal.Parse(reader["je"].ToString()); invoice.CtaxCode = reader["swdjh"].ToString(); invoice.Type = reader["fplx"].ToString(); invoice.CAddrPhone = reader["addr"].ToString(); invoice.CbankAccount = reader["khyh"].ToString() + " " + reader["yhzh"].ToString(); invoice.Invoicer = reader["Invoicer"].ToString(); invoice.TaxCode = reader["fpdm"].ToString(); invoice.TaxDate = DateTime.Parse(reader["kprq"].ToString()); invoice.TaxNumber = reader["fph"].ToString(); invoice.NumberTotal = Decimal.Parse(reader["sl"].ToString()); invoice.AmountAddTax = Decimal.Parse(reader["bhsje"].ToString()); //不含税金额 invoice.Checker = reader["fhr"].ToString(); //复核人 invoice.Cashier = reader["skr"].ToString(); //收款人 list.Add(invoice); } } return(nrWebClass.xmlHelper.ToString <List <Invioce> >(list)); }
public string cat() { List <InvioceCat> list = new List <InvioceCat>(); string comm = "SELECT dm,mc from t_xtdm where ssid=7735"; nrWebClass.LiLanzDAL dal = new nrWebClass.LiLanzDAL(); using (IDataReader reader = dal.ExecuteReader(comm)) { while (reader.Read()) { InvioceCat icat = new InvioceCat(); icat.Cat = reader[0].ToString(); icat.Cname = reader[1].ToString(); list.Add(icat); } } return(nrWebClass.xmlHelper.ToString <List <InvioceCat> >(list)); }
protected void Page_Load(object sender, EventArgs e) { string ip = ""; if (Request.ServerVariables["HTTP_VIA"] != null) // using proxy { ip = Request.ServerVariables["HTTP_X_FORWARDED_FOR"].ToString(); // Return real client IP. } else// not using proxy or can't get the Client IP { ip = Request.ServerVariables["REMOTE_ADDR"].ToString(); //While it can't get the Client IP, it will return proxy IP. } string guid = HttpContext.Current.Request.Cookies["guid"].Value; #region 验证guid ip guidtime合法性 nrWebClass.LiLanzDAL sqlhelper = new nrWebClass.LiLanzDAL(); if (ip == "127.0.0.1" || ip == "192.168.36.131") {//内部测试IP sqlhelper.ConnectionString = "Data Source=192.168.35.23;Persist Security Info=False;User ID=lllogin;Password=rw1894tla;Initial Catalog=tlsoft;Connect Timeout=10"; } string checkSql = ("select * from user_cooperate where guidip=@ip and guid=@guid and DATEDIFF (MINUTE,GUIDTIME,GETDATE()) <=30 "); SqlParameter[] checkparameters = { new SqlParameter("@ip", SqlDbType.VarChar, 15), new SqlParameter("@guid", SqlDbType.VarChar, 36) }; checkparameters[0].Value = ip; checkparameters[1].Value = guid; using (IDataReader dataReader = sqlhelper.ExecuteReader(checkSql.ToString(), CommandType.Text, checkparameters)) { if (dataReader.Read()) {//请求合法 username.Value = dataReader["user_name"].ToString(); realname.Value = dataReader["real_name"].ToString(); product.Value = dataReader["product"].ToString(); address.Value = dataReader["address"].ToString(); phone.Value = dataReader["phone"].ToString(); email.Value = dataReader["email"].ToString(); } else { username.Value = "请求超时,请重新登陆"; } } #endregion }
public string InvioceCompanyList() { List <InvioceCompany> list = new List <InvioceCompany>(); string comm = @"select id,dm,mc,txdz,lxdh,qydd,qymc from yx_t_khmx where khid=1"; nrWebClass.LiLanzDAL dal = new nrWebClass.LiLanzDAL(); using (IDataReader reader = dal.ExecuteReader(comm)) { while (reader.Read()) { InvioceCompany comp = new InvioceCompany(); comp.Id = int.Parse(reader[0].ToString()); comp.Dm = reader[1].ToString(); comp.Mc = reader[2].ToString(); list.Add(comp); } } return(nrWebClass.xmlHelper.ToString <List <InvioceCompany> >(list)); }
public string InvioceDetail(int id) { try { List <InvioceDetail> list = new List <InvioceDetail>(); string comm = String.Format(@"select a.id,b.mxid,b.sphh as cwhh,c.xz,c.splbdm,c.cwspmc as spmc,b.sl,b.dj,b.je, b.je-cast(b.je/1.17 as numeric(12,2)) se,cast(b.je/1.17 as numeric(12,2)) bhsje, b.bm,b.cmmc,C.dw from yx_t_fpxxb a inner join yx_t_fpxxmxb b on a.id=b.id inner join yx_v_cwspdmb c on b.sphh=c.cwhh where a.id = {0}", id); nrWebClass.LiLanzDAL dal = new nrWebClass.LiLanzDAL(); using (IDataReader reader = dal.ExecuteReader(comm)) { while (reader.Read()) { InvioceDetail detail = new InvioceDetail(); if (reader["bm"].ToString() != "") { detail.GoodsName = reader["bm"].ToString(); } else { detail.GoodsName = reader["spmc"].ToString(); } detail.Standard = reader["cmmc"].ToString(); detail.Number = Decimal.Parse(reader["sl"].ToString()); detail.Amount = Decimal.Parse(reader["je"].ToString()); detail.Unit = reader["dw"].ToString(); detail.PriceKind = 1; list.Add(detail); } } return(nrWebClass.xmlHelper.ToString <List <InvioceDetail> >(list)); } catch (Exception ex) { return(ex.ToString()); } }
private SortedDictionary <string, string> get_row(string sql) { SortedDictionary <string, string> row = new SortedDictionary <string, string>(); nrWebClass.LiLanzDAL dal = new nrWebClass.LiLanzDAL(); IDataReader reader = dal.ExecuteReader(sql); if (reader.Read()) { for (int i = 0; i < reader.FieldCount; i++) { row.Add(reader.GetName(i), reader[i].ToString()); } } else { return(null); } reader.Dispose(); return(row); }