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