public void ULogin(string uname, string upwd) { try { string sql = "select vchrUid as UserID,vchrName as UserName,vchrPwd,cDepCode from _UserInfo where vchrUid='" + uname + "'"; DataTable dt = clsSQLCommond.ExecQueryWithoutSession(sql); if (dt.Rows.Count > 0) { if (dt.Rows[0]["vchrPwd"].ToString().Trim() == upwd) { Session["uID"] = dt.Rows[0]["UserID"].ToString(); Session["uName"] = dt.Rows[0]["UserName"].ToString(); // sSQL = @"select cDept_num,c.cPsn_Num,a.cUser_Id as cPersonCode,a.cUser_Name as cPersonName from @u8.UA_User a, @u8.UserHRPersonContro b,@u8.hr_hi_person c where a.cUser_Id = b.cUser_Id and b.cPsn_Num = c.cPsn_Num // and a.cUser_Id='" + dt.Rows[0]["UserID"].ToString() + "'"; // DataTable dts = clsSQLCommond.ExecQueryWithoutSession(sSQL); // if (dts.Rows.Count > 0) // { // Session["uDeptID"] = dts.Rows[0]["cDept_num"].ToString(); // } //sSQL = "update _UserInfo set vlanguage='" + DropDownList1.SelectedValue + "' where vchrUid='" + uname + "'"; //clsSQLCommond.Update(sSQL); sSQL = "select vchrUid as UserID,vchrName as UserName,vchrPwd,cDepCode from _UserInfo where isnull(dtmCreate,getdate())<=getdate() and isnull(dtmClose,getdate())>=getdate() and vchrUid='" + uname + "'"; DataTable dtclose = clsSQLCommond.ExecQueryWithoutSession(sSQL); if (dtclose.Rows.Count == 0) { throw new Exception("账号异常"); } else { //Session["NowDate"] = NowDate; //if (((CheckBox)Login1.FindControl("CheckBox1")).Checked == true) //if(CheckBox1.Checked==true) //{ // Response.Redirect("../System/UserInfo_Update2.aspx"); //} //else //{ Response.Redirect("../List.aspx"); //} } } else { throw new Exception("密码错误"); } } else { throw new Exception("登陆失败"); } } catch (Exception ee) { System.Web.HttpContext.Current.Response.Write("<script>alert('" + ee.Message + "!');</script>"); } }
public DataTable Get() { sSQL = @"SELECT a.cInvCCode,cInvCName,Per FROM (select cInvCCode,cInvCName from viewSalesBack group by cInvCCode,cInvCName) a left join " + tablename + " b on a.cInvCCode=b.cInvCCode "; sSQL = sSQL + " order by cInvCCode"; DataTable dt = clsSQLCommond.ExecQueryWithoutSession(sSQL); return(dt); }
public DataTable Report1(string sDate, string eDate) { sSQL = @" --select 1 as iOrder,'进入铝棒' as Flag,datepart(yyyy,BusinessDate) as iYear,datepart(MM,BusinessDate) as iMonth,convert(decimal(18, 6),iQuantity) as iQuantity into #a FROM viewRec0101 where 1=1 --union all --select 2,'外购铝材',datepart(yyyy,BusinessDate) as iYear,datepart(MM,BusinessDate) as iMonth,convert(decimal(18, 6),iQuantity) as iQuantity FROM viewRec0302 where 1=1 --union all --select 3,'发出废料',datepart(yyyy,BusinessDate) as iYear,datepart(MM,BusinessDate) as iMonth,convert(decimal(18, 6),iQuantity) as iQuantity FROM viewRecBack where 1=1 --union all --select 4,'发出成品(实际重量)',datepart(yyyy,BusinessDate) as iYear,datepart(MM,BusinessDate) as iMonth,convert(decimal(18, 6),iQuantity) as iQuantity FROM viewSalesOut where 1=1 --union all --select 5,'退货(实际重量)',datepart(yyyy,BusinessDate) as iYear,datepart(MM,BusinessDate) as iMonth,convert(decimal(18, 6),iQuantity) as iQuantity FROM viewSalesBack where 1=1 --union all --select 6,'进入-发出',datepart(yyyy,BusinessDate) as iYear,datepart(MM,BusinessDate) as iMonth,convert(decimal(18, 6),iQuantity) as iQuantity FROM viewRec0101 where 1=1 --union all --select 6,'进入-发出',datepart(yyyy,BusinessDate) as iYear,datepart(MM,BusinessDate) as iMonth,convert(decimal(18, 6),iQuantity) as iQuantity FROM viewSalesBack where 1=1 --union all --select 6,'进入-发出',datepart(yyyy,BusinessDate) as iYear,datepart(MM,BusinessDate) as iMonth,convert(decimal(18, 6),iQuantity) as iQuantity FROM viewRec0302 where 1=1 --union all --select 6,'进入-发出',datepart(yyyy,BusinessDate) as iYear,datepart(MM,BusinessDate) as iMonth,-convert(decimal(18, 6),iQuantity) as iQuantity FROM viewRecBack where 1=1 --union all --select 6,'进入-发出',datepart(yyyy,BusinessDate) as iYear,datepart(MM,BusinessDate) as iMonth,-convert(decimal(18, 6),iQuantity) as iQuantity FROM viewSalesOut where 1=1 --union all --select 7,'净发货',datepart(yyyy,BusinessDate) as iYear,datepart(MM,BusinessDate) as iMonth,convert(decimal(18, 6),iQuantity) as iQuantity FROM viewSalesOut where 1=1 --union all --select 7,'净发货',datepart(yyyy,BusinessDate) as iYear,datepart(MM,BusinessDate) as iMonth,-convert(decimal(18, 6),iQuantity) as iQuantity FROM viewSalesBack where 1=1 --union all --select 8,'销售订单',datepart(yyyy,BusinessDate) as iYear,datepart(MM,BusinessDate) as iMonth,convert(decimal(18, 6),iQuantity) as iQuantity FROM viewSales where 1 select 1 as iOrder,convert(nvarchar(50),'进入铝棒') as Flag,datepart(yyyy,BusinessDate) as iYear,datepart(MM,BusinessDate) as iMonth,convert(decimal(18, 6),iQuantity) as iQuantity into #a FROM viewRec0101 where 1=1 insert into #a select 2,'外购铝材',datepart(yyyy,BusinessDate) as iYear,datepart(MM,BusinessDate) as iMonth,convert(decimal(18, 6),iQuantity) as iQuantity FROM viewRec0302 where 1=1 insert into #a select 3,'发出废料',datepart(yyyy,BusinessDate) as iYear,datepart(MM,BusinessDate) as iMonth,convert(decimal(18, 6),iQuantity) as iQuantity FROM viewRecBack where 1=1 insert into #a select 4,'发出成品(实际重量)',datepart(yyyy,BusinessDate) as iYear,datepart(MM,BusinessDate) as iMonth,convert(decimal(18, 6),iQuantity) as iQuantity FROM viewSalesOut where 1=1 insert into #a select 5,'退货(实际重量)',datepart(yyyy,BusinessDate) as iYear,datepart(MM,BusinessDate) as iMonth,convert(decimal(18, 6),iQuantity) as iQuantity FROM viewSalesBack where 1=1 insert into #a select 6,'进入-发出',datepart(yyyy,BusinessDate) as iYear,datepart(MM,BusinessDate) as iMonth,convert(decimal(18, 6),iQuantity) as iQuantity FROM viewRec0101 where 1=1 insert into #a select 6,'进入-发出',datepart(yyyy,BusinessDate) as iYear,datepart(MM,BusinessDate) as iMonth,convert(decimal(18, 6),iQuantity) as iQuantity FROM viewSalesBack where 1=1 insert into #a select 6,'进入-发出',datepart(yyyy,BusinessDate) as iYear,datepart(MM,BusinessDate) as iMonth,convert(decimal(18, 6),iQuantity) as iQuantity FROM viewRec0302 where 1=1 insert into #a select 6,'进入-发出',datepart(yyyy,BusinessDate) as iYear,datepart(MM,BusinessDate) as iMonth,-convert(decimal(18, 6),iQuantity) as iQuantity FROM viewRecBack where 1=1 insert into #a select 6,'进入-发出',datepart(yyyy,BusinessDate) as iYear,datepart(MM,BusinessDate) as iMonth,-convert(decimal(18, 6),iQuantity) as iQuantity FROM viewSalesOut where 1=1 insert into #a select 7,'净发货',datepart(yyyy,BusinessDate) as iYear,datepart(MM,BusinessDate) as iMonth,convert(decimal(18, 6),iQuantity) as iQuantity FROM viewSalesOut where 1=1 insert into #a select 7,'净发货',datepart(yyyy,BusinessDate) as iYear,datepart(MM,BusinessDate) as iMonth,-convert(decimal(18, 6),iQuantity) as iQuantity FROM viewSalesBack where 1=1 insert into #a select 8,'销售订单',datepart(yyyy,BusinessDate) as iYear,datepart(MM,BusinessDate) as iMonth,convert(decimal(18, 6),iQuantity) as iQuantity FROM viewSales where 1=1 insert into #a(iYear,iOrder,Flag) values(111111,1,'进入铝棒') insert into #a(iYear,iOrder,Flag) values(111111,2,'外购铝材') insert into #a(iYear,iOrder,Flag) values(111111,3,'发出废料') insert into #a(iYear,iOrder,Flag) values(111111,4,'发出成品(实际重量)') insert into #a(iYear,iOrder,Flag) values(111111,5,'退货(实际重量)') insert into #a(iYear,iOrder,Flag) values(111111,6,'进入-发出') insert into #a(iYear,iOrder,Flag) values(111111,7,'净发货') insert into #a(iYear,iOrder,Flag) values(111111,8,'销售订单') select iOrder,Flag ,convert(decimal(18, 1),SUM(case when iMonth=1 then iQuantity end)) as S1 ,convert(decimal(18, 1),SUM(case when iMonth=2 then iQuantity end)) as S2 ,convert(decimal(18, 1),SUM(case when iMonth=3 then iQuantity end)) as S3 ,convert(decimal(18, 1),SUM(case when iMonth=4 then iQuantity end)) as S4 ,convert(decimal(18, 1),SUM(case when iMonth=5 then iQuantity end)) as S5 ,convert(decimal(18, 1),SUM(case when iMonth=6 then iQuantity end)) as S6 ,convert(decimal(18, 1),SUM(case when iMonth=7 then iQuantity end)) as S7 ,convert(decimal(18, 1),SUM(case when iMonth=8 then iQuantity end)) as S8 ,convert(decimal(18, 1),SUM(case when iMonth=9 then iQuantity end)) as S9 ,convert(decimal(18, 1),SUM(case when iMonth=10 then iQuantity end)) as S10 ,convert(decimal(18, 1),SUM(case when iMonth=11 then iQuantity end)) as S11 ,convert(decimal(18, 1),SUM(case when iMonth=12 then iQuantity end)) as S12 ,convert(decimal(18, 1),SUM(iQuantity)) as 合计 from #a a GROUP BY iOrder,Flag order by iOrder,Flag "; if (sDate != "") { sSQL = sSQL.Replace("1=1", "1=1 and BusinessDate>='" + sDate + "'"); sSQL = sSQL.Replace("111111", "'" + DateTime.Parse(sDate).Year + "'"); } if (eDate != "") { sSQL = sSQL.Replace("1=1", "1=1 and BusinessDate<='" + eDate + "'"); } DataTable dt = clsSQLCommond.ExecQueryWithoutSession(sSQL); return(dt); }