{
public static String CONN = System.Configuration.ConfigurationManager.AppSettings.Get("ConnectionString");
protected void Page_Load(object sender, EventArgs e)
{
PageBegin(true, false);
if (IsPostBack)
return;
FromDate.Text = DateTime.Now.Year.ToString() + "-1-1";
ToDate.Text = (DateTime.Now.Year + 1).ToString() + "-1-1";
PageFill();
}
protected void QueryButton_Click(object sender, EventArgs e)
{
if (DateUtil.IsDateTime(FromDate.Text) == false || DateUtil.IsDateTime(FromDate.Text) == false)
{
Session["CM_report_FromDate"] = "";
Session["CM_report_ToDate"] = "";
}
else
{
Session["CM_report_FromDate"] = FromDate.Text;
Session["CM_report_ToDate"] = ToDate.Text;
}
BuildTable();
}
protected void PageFill()
{
if (Session["CM_Year"] != null)
{
FromDate.Text = Session["CM_Year"] + "-1-1";
ToDate.Text = int.Parse(Session["CM_Year"].ToString()) + 1 + "-1-1";
}
if (Session["CM_report_FromDate"] != null)
FromDate.Text = Session["CM_report_FromDate"].ToString();
if (Session["CM_report_ToDate"] != null)
ToDate.Text = Session["CM_report_ToDate"].ToString();
BuildTable();
}
protected void BuildTable()
{
SqlConnection conn = new SqlConnection();
conn.ConnectionString = CONN;
SqlCommand command = new SqlCommand();
command.CommandText = "CM_Test";
command.CommandType = CommandType.StoredProcedure;
command.Connection = conn;
SqlParameter param = new SqlParameter("@FromDate", SqlDbType.DateTime);
if (DateUtil.IsDateTime(FromDate.Text) == false)
{
param.Value = DateTime.Parse(DateTime.Now.Year.ToString() + "-1-1");
}
else
{
param.Value = DateTime.Parse(FromDate.Text);
}
command.Parameters.Add(param);
param = new SqlParameter("@ToDate", SqlDbType.DateTime);
if (DateUtil.IsDateTime(ToDate.Text) == false)
{
param.Value = DateTime.Parse((DateTime.Now.Year + 1).ToString() + "-1-1");
}
else
{
param.Value = DateTime.Parse(ToDate.Text);
}
command.Parameters.Add(param);
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = command;
da.Fill(ds);
DG.DataSource = ds;
DG.DataBind();
}
}
@FromDate datetime,
@ToDate datetime
AS
declare @title varchar(50)
declare @ic float
declare @asb float
declare @sales float
declare @tmpSales table(CID varchar(50),Company varchar(50),Money float,TPSOMoney float,TPSIMoney float,
TPHOMoney float,TPHIMoney float,SPMoney float,SPIMoney float,CDMoney float,IMoney float,
OMoney float,GSMoney float,Dep varchar(50),InvoiceMoney float,NotInvoiceMoney float,
ReceiveMoney float,NotReceiveMoney float,InvoiceNotReMoney float,RealReceiveMoney float)
declare @tmpIC table(CID varchar(50),Company varchar(50),Money float,TPSOMoney float,TPSIMoney float,
TPHOMoney float,TPHIMoney float,SPMoney float,SPIMoney float,CDMoney float,IMoney float,
OMoney float,GSMoney float,Dep varchar(50),InvoiceMoney float,NotInvoiceMoney float,
ReceiveMoney float,NotReceiveMoney float,InvoiceNotReMoney float,RealReceiveMoney float)
declare @tmpASB table(CID varchar(50),Company varchar(50),Money float,TPSOMoney float,TPSIMoney float,
TPHOMoney float,TPHIMoney float,SPMoney float,SPIMoney float,CDMoney float,IMoney float,
OMoney float,GSMoney float,Dep varchar(50),InvoiceMoney float,NotInvoiceMoney float,
ReceiveMoney float,NotReceiveMoney float,InvoiceNotReMoney float,RealReceiveMoney float)
declare @tmp table(item varchar(50),ic float,asb float,sales float)
insert @tmpSales select CID,Company,Money,TPSOMoney,TPSIMoney,
TPHOMoney,TPHIMoney,SPMoney,SPIMoney,CDMoney,IMoney,
OMoney,GSMoney,Dep,InvoiceMoney,NotInvoiceMoney,
ReceiveMoney,NotReceiveMoney,InvoiceNotReMoney,RealReceiveMoney
from CM_Sales where DelFlag=0 and SignDate >= @FromDate and SignDate < @ToDate
insert @tmpIC select * from @tmpSales where Company not like '%公司名字%'
insert @tmpASB select * from @tmpSales where Company like '%公司名字%'
select @ic=Count(CID) from @tmpIC select @asb=Count(CID) from @tmpASB select @sales=@ic+@asb
insert @tmp values('合同数:',@ic,@asb,@sales)
select @ic=Sum(Money) from @tmpIC select @asb=Sum(Money) from @tmpASB select @sales=@ic+@asb
insert @tmp values('合同总额:',@ic,@asb,@sales)
/*
select @ic=Sum(TPSIMoney)+Sum(TPHIMoney)+Sum(SPIMoney) from @tmpIC select @asb=Sum(TPSIMoney)+Sum(TPHIMoney)+Sum(SPIMoney) from @tmpASB select @sales=@ic+@asb
insert @tmp values('采购总额:',@ic,@asb,@sales)
*/
select @ic=sum(Money) from CM_SalesTProduct where CID in (select ID from CM_Sales where DelFlag=0 and SignDate >= @FromDate and SignDate < @ToDate and Company not like '%公司名字%')
select @asb=sum(Money) from CM_SalesTProduct where CID in (select ID from CM_Sales where DelFlag=0 and SignDate >= @FromDate and SignDate < @ToDate and Company like '%公司名字%')
select @sales=@ic+@asb
insert @tmp values('采购总额:',@ic,@asb,@sales)
insert @tmp values('————————————————',null,null,null)
select @ic=Sum(SPMoney)+Sum(GSMoney) from @tmpIC select @asb=Sum(SPMoney)+Sum(GSMoney) from @tmpASB select @sales=@ic+@asb
insert @tmp values('(自有+服务)合同额:',@ic,@asb,@sales)
select @ic=Sum(TPSOMoney)+Sum(TPHOMoney) from @tmpIC select @asb=Sum(TPSOMoney)+Sum(TPHOMoney) from @tmpASB select @sales=@ic+@asb
insert @tmp values('第三方软硬件合同额:',@ic,@asb,@sales)
insert @tmp values('————————————————',null,null,null)
select @ic=Sum(TPHOMoney) from @tmpIC select @asb=Sum(TPHOMoney) from @tmpASB select @sales=@ic+@asb
insert @tmp values('第三方硬件合同额:',@ic,@asb,@sales)
select @ic=Sum(TPHIMoney) from @tmpIC select @asb=Sum(TPHIMoney) from @tmpASB select @sales=@ic+@asb
insert @tmp values('第三方硬件采购额:',@ic,@asb,@sales)
select @ic=Sum(TPSOMoney) from @tmpIC select @asb=Sum(TPSOMoney) from @tmpASB select @sales=@ic+@asb
insert @tmp values('第三方软件合同额:',@ic,@asb,@sales)
select @ic=Sum(TPSIMoney) from @tmpIC select @asb=Sum(TPSIMoney) from @tmpASB select @sales=@ic+@asb
insert @tmp values('第三方软件采购额:',@ic,@asb,@sales)
select @ic=Sum(SPMoney) from @tmpIC select @asb=Sum(SPMoney) from @tmpASB select @sales=@ic+@asb
insert @tmp values('自有产品合同额:',@ic,@asb,@sales)
select @ic=Sum(SPIMoney) from @tmpIC select @asb=Sum(SPIMoney) from @tmpASB select @sales=@ic+@asb
insert @tmp values('自有产品采购额:',@ic,@asb,@sales)
select @ic=Sum(CDMoney)+Sum(IMoney)+Sum(GSMoney)+Sum(OMoney) from @tmpIC select @asb=Sum(CDMoney)+Sum(IMoney)+Sum(GSMoney)+Sum(OMoney) from @tmpASB select @sales=@ic+@asb
insert @tmp values('服务及其他合同额:',@ic,@asb,@sales)
insert @tmp values('————————————————',null,null,null)
select @ic=Sum(InvoiceMoney) from @tmpIC select @asb=Sum(InvoiceMoney) from @tmpASB select @sales=@ic+@asb
insert @tmp values('已开票总额:',@ic,@asb,@sales)
select @ic=Sum(ReceiveMoney) from @tmpIC select @asb=Sum(ReceiveMoney) from @tmpASB select @sales=@ic+@asb
insert @tmp values('已回款总额:',@ic,@asb,@sales)
select @ic=Sum(NotReceiveMoney) from @tmpIC select @asb=Sum(NotReceiveMoney) from @tmpASB select @sales=@ic+@asb
insert @tmp values('未回款总额:',@ic,@asb,@sales)
select @ic=Sum(InvoiceNotReMoney) from @tmpIC select @asb=Sum(InvoiceNotReMoney) from @tmpASB select @sales=@ic+@asb
insert @tmp values('已开票未回款总额:',@ic,@asb,@sales)
select @ic=Sum(RealReceiveMoney) from @tmpIC select @asb=Sum(RealReceiveMoney) from @tmpASB select @sales=@ic+@asb
insert @tmp values('实际应收款总额 :',@ic,@asb,@sales)
insert @tmp values('————————————————',null,null,null)
select @ic=Sum(SPMoney)+Sum(GSMoney)+Sum(IMoney) from @tmpIC where dep=57 select @asb=Sum(SPMoney)+Sum(GSMoney)+Sum(IMoney) from @tmpASB where dep=57 select @sales=Sum(SPMoney)+Sum(GSMoney)+Sum(IMoney) from @tmpSales where dep=57
insert @tmp values('BU1(自有+服务)合同额 :',@ic,@asb,@sales)
select @ic=Sum(SPMoney)+Sum(GSMoney)+Sum(IMoney) from @tmpIC where dep=43 select @asb=Sum(SPMoney)+Sum(GSMoney)+Sum(IMoney) from @tmpASB where dep=43 select @sales=Sum(SPMoney)+Sum(GSMoney)+Sum(IMoney) from @tmpSales where dep=43
insert @tmp values('BU2(自有+服务)合同额 :',@ic,@asb,@sales)
select @ic=Sum(SPMoney)+Sum(GSMoney)+Sum(IMoney) from @tmpIC where dep=60 select @asb=Sum(SPMoney)+Sum(GSMoney)+Sum(IMoney) from @tmpASB where dep=60 select @sales=Sum(SPMoney)+Sum(GSMoney)+Sum(IMoney) from @tmpSales where dep=60
insert @tmp values('BU3(自有+服务)合同额 :',@ic,@asb,@sales)
select @ic=Sum(SPMoney)+Sum(GSMoney)+Sum(IMoney) from @tmpIC where dep=88 select @asb=Sum(SPMoney)+Sum(GSMoney)+Sum(IMoney) from @tmpASB where dep=88 select @sales=Sum(SPMoney)+Sum(GSMoney)+Sum(IMoney) from @tmpSales where dep=88
insert @tmp values('BU4(自有+服务)合同额 :',@ic,@asb,@sales)
select @ic=Sum(SPMoney)+Sum(GSMoney)+Sum(IMoney) from @tmpIC where dep=50 select @asb=Sum(SPMoney)+Sum(GSMoney)+Sum(IMoney) from @tmpASB where dep=50 select @sales=Sum(SPMoney)+Sum(GSMoney)+Sum(IMoney) from @tmpSales where dep=50
insert @tmp values('BU5(自有+服务)合同额 :',@ic,@asb,@sales)
select @ic=Sum(SPMoney)+Sum(GSMoney)+Sum(IMoney) from @tmpIC where dep=58 select @asb=Sum(SPMoney)+Sum(GSMoney)+Sum(IMoney) from @tmpASB where dep=58 select @sales=Sum(SPMoney)+Sum(GSMoney)+Sum(IMoney) from @tmpSales where dep=58
insert @tmp values('BU6(自有+服务)合同额 :',@ic,@asb,@sales)
select @ic=Sum(SPMoney)+Sum(GSMoney)+Sum(IMoney) from @tmpIC where dep=78 select @asb=Sum(SPMoney)+Sum(GSMoney)+Sum(IMoney) from @tmpASB where dep=78 select @sales=Sum(SPMoney)+Sum(GSMoney)+Sum(IMoney) from @tmpSales where dep=78
insert @tmp values('BU7(自有+服务)合同额 :',@ic,@asb,@sales)
select @ic=Sum(SPMoney)+Sum(GSMoney)+Sum(IMoney) from @tmpIC where dep=83 select @asb=Sum(SPMoney)+Sum(GSMoney)+Sum(IMoney) from @tmpASB where dep=83 select @sales=Sum(SPMoney)+Sum(GSMoney)+Sum(IMoney) from @tmpSales where dep=83
insert @tmp values('BU8(自有+服务)合同额 :',@ic,@asb,@sales)
select @ic=Sum(SPMoney)+Sum(GSMoney)+Sum(IMoney) from @tmpIC where dep=70 select @asb=Sum(SPMoney)+Sum(GSMoney)+Sum(IMoney) from @tmpASB where dep=70 select @sales=Sum(SPMoney)+Sum(GSMoney)+Sum(IMoney) from @tmpSales where dep=70
insert @tmp values('SRS服务合同额 :',@ic,@asb,@sales)
select @ic=Sum(SPMoney)+Sum(GSMoney)+Sum(IMoney) from @tmpIC where dep=66 select @asb=Sum(SPMoney)+Sum(GSMoney)+Sum(IMoney) from @tmpASB where dep=66 select @sales=Sum(SPMoney)+Sum(GSMoney)+Sum(IMoney) from @tmpSales where dep=66
insert @tmp values('SIS服务合同额 :',@ic,@asb,@sales)
select * from @tmp
GO