private void dgvLoadInfo(string sql)
{
OLEDBHelper.iLoginEx = iLoginEx;
string selectSQL = "declare @CurrentYr varchar(10) \r\n";//业务员
selectSQL += " declare @lastyear varchar(10) \r\n";
selectSQL += " set @CurrentYr=datepart(year,getdate())--今年 \r\n";
selectSQL += " set @lastyear=@CurrentYr-1 \r\n";
selectSQL += " select cy.aCsocode as '订单号',cy.ztcinvcode as '存货编码',(case when isnull(cy.cinvccode ,'')='' then '配件' when cy.cinvccode ='H' then '硬盘' else cls.cInvCName end) as '分类名称',cy.ztcinvName as '存货名称',cy.ztCinvstd as '规格型号',c.cPersonName as '业务员',b.ccusname as '客户简称',cy.ccuscode as '客户编码',cy.addate as '日期',cy.sales as '销售额',cy.Number as '数量', \r\n";
// selectSQL += " case when zid=0 and Yr = @lastyear then Sales else 0 end as 'Sales2015', \r\n";
selectSQL += " case when zid=0 and Yr = @CurrentYr+'' and left(convert(varchar,adDate,111),7)=@CurrentYr+'/01' then Sales else 0 end as '1月销售额', \r\n";
selectSQL += " case when zid=0 and Yr = @CurrentYr+'' and left(convert(varchar,adDate,111),7)=@CurrentYr+'/02' then Sales else 0 end as '2月销售额', \r\n";
selectSQL += " case when zid=0 and Yr = @CurrentYr+'' and left(convert(varchar,adDate,111),7)=@CurrentYr+'/03' then Sales else 0 end as '3月销售额', \r\n";
selectSQL += " case when zid=0 and Yr = @CurrentYr+'' and left(convert(varchar,adDate,111),7)=@CurrentYr+'/04' then Sales else 0 end as '4月销售额', \r\n";
selectSQL += " case when zid=0 and Yr = @CurrentYr+'' and left(convert(varchar,adDate,111),7)=@CurrentYr+'/05' then Sales else 0 end as '5月销售额', \r\n";
selectSQL += " case when zid=0 and Yr = @CurrentYr+'' and left(convert(varchar,adDate,111),7)=@CurrentYr+'/06' then Sales else 0 end as '6月销售额', \r\n";
selectSQL += " case when zid=0 and Yr = @CurrentYr+'' and left(convert(varchar,adDate,111),7)=@CurrentYr+'/07' then Sales else 0 end as '7月销售额', \r\n";
selectSQL += " case when zid=0 and Yr = @CurrentYr+'' and left(convert(varchar,adDate,111),7)=@CurrentYr+'/08' then Sales else 0 end as '8月销售额', \r\n";
selectSQL += " case when zid=0 and Yr = @CurrentYr+'' and left(convert(varchar,adDate,111),7)=@CurrentYr+'/09' then Sales else 0 end as '9月销售额', \r\n";
selectSQL += " case when zid=0 and Yr = @CurrentYr+'' and left(convert(varchar,adDate,111),7)=@CurrentYr+'/10' then Sales else 0 end as '10月销售额', \r\n";
selectSQL += " case when zid=0 and Yr = @CurrentYr+'' and left(convert(varchar,adDate,111),7)=@CurrentYr+'/11' then Sales else 0 end as '11月销售额', \r\n";
selectSQL += " case when zid=0 and Yr = @CurrentYr+'' and left(convert(varchar,adDate,111),7)=@CurrentYr+'/12' then Sales else 0 end as '12月销售额' \r\n";
//selectSQL += " case when zid=0 and Yr = @lastyear then Costs else 0 end as 'Costs2015', \r\n";
//selectSQL += " case when zid=0 and Yr = @CurrentYr+'' and left(convert(varchar,adDate,111),7)=@CurrentYr+'/01' then Costs else 0 end as 'Costs01', \r\n";
//selectSQL += " case when zid=0 and Yr = @CurrentYr+'' and left(convert(varchar,adDate,111),7)=@CurrentYr+'/02' then Costs else 0 end as 'Costs02', \r\n";
//selectSQL += " case when zid=0 and Yr = @CurrentYr+'' and left(convert(varchar,adDate,111),7)=@CurrentYr+'/03' then Costs else 0 end as 'Costs03', \r\n";
//selectSQL += " case when zid=0 and Yr = @CurrentYr+'' and left(convert(varchar,adDate,111),7)=@CurrentYr+'/04' then Costs else 0 end as 'Costs04', \r\n";
//selectSQL += " case when zid=0 and Yr = @CurrentYr+'' and left(convert(varchar,adDate,111),7)=@CurrentYr+'/05' then Costs else 0 end as 'Costs05', \r\n";
//selectSQL += " case when zid=0 and Yr = @CurrentYr+'' and left(convert(varchar,adDate,111),7)=@CurrentYr+'/06' then Costs else 0 end as 'Costs06', \r\n";
//selectSQL += " case when zid=0 and Yr = @CurrentYr+'' and left(convert(varchar,adDate,111),7)=@CurrentYr+'/07' then Costs else 0 end as 'Costs07', \r\n";
//selectSQL += " case when zid=0 and Yr = @CurrentYr+'' and left(convert(varchar,adDate,111),7)=@CurrentYr+'/08' then Costs else 0 end as 'Costs08', \r\n";
//selectSQL += " case when zid=0 and Yr = @CurrentYr+'' and left(convert(varchar,adDate,111),7)=@CurrentYr+'/09' then Costs else 0 end as 'Costs09', \r\n";
//selectSQL += " case when zid=0 and Yr = @CurrentYr+'' and left(convert(varchar,adDate,111),7)=@CurrentYr+'/10' then Costs else 0 end as 'Costs10', \r\n";
//selectSQL += " case when zid=0 and Yr = @CurrentYr+'' and left(convert(varchar,adDate,111),7)=@CurrentYr+'/11' then Costs else 0 end as 'Costs11', \r\n";
//selectSQL += " case when zid=0 and Yr = @CurrentYr+'' and left(convert(varchar,adDate,111),7)=@CurrentYr+'/12' then Costs else 0 end as 'Costs12' \r\n";
selectSQL += " from Copy_sale_t_zhrs_v_CB_SG_zzc cy left join Customer b on cy.ccuscode=b.ccuscode \r\n";
selectSQL += " left join Person c on b.ccuspperson=c.cPersonCode \r\n";
selectSQL += " left join InventoryClass cls on cy.cinvccode=cls.cInvCCode \r\n";
selectSQL += " where zid=0 " + sql + " order by cy.addate \r\n";
DataTable dt = new DataTable();
dt = OLEDBHelper.GetDataTalbe(selectSQL, CommandType.Text);
dgvDetailed.DataSource = dt;
lblsum.Text = "记录数：" + dt.Rows.Count;
for (int i = 9; i < dgvDetailed.Columns.Count; i++)
{
dgvDetailed.Columns[i].DefaultCellStyle.Format = "#,###0.00";
dgvDetailed.Columns[i].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
}
cbDetail_CheckedChanged(null, null);
}
private void dgvLoadInfo()
{
string sql = "";
if (String.IsNullOrEmpty(txtCsoCode.Text.Trim()) && String.IsNullOrEmpty(txtZtcinvcodes.Text.Trim()) && String.IsNullOrEmpty(txtisoid.Text.Trim()))
{
sql = " where zs.cinvCCode not like 'CB%' and Yr='" + iLoginEx.iYear() + "' ";
}
else if (!String.IsNullOrEmpty(txtCsoCode.Text.Trim()) && String.IsNullOrEmpty(txtZtcinvcodes.Text.Trim()) && String.IsNullOrEmpty(txtisoid.Text.Trim()))
{
sql = " where zs.cinvCCode not like 'CB%' and Yr='" + iLoginEx.iYear() + "' and aCsocode='" + txtCsoCode.Text.Trim() + "'";
}
else if (!String.IsNullOrEmpty(txtCsoCode.Text.Trim()) && !String.IsNullOrEmpty(txtZtcinvcodes.Text.Trim()) && String.IsNullOrEmpty(txtisoid.Text.Trim()))
{
sql = " where zs.cinvCCode not like 'CB%' and Yr='" + iLoginEx.iYear() + "' and aCsocode='" + txtCsoCode.Text.Trim() + "' and ZtCinvcodes='" + txtZtcinvcodes.Text.Trim() + "'";
}
else if (!String.IsNullOrEmpty(txtCsoCode.Text.Trim()) && !String.IsNullOrEmpty(txtZtcinvcodes.Text.Trim()) && !String.IsNullOrEmpty(txtisoid.Text.Trim()))
{
sql = " where zs.cinvCCode not like 'CB%' and Yr='" + iLoginEx.iYear() + "' and aCsocode='" + txtCsoCode.Text.Trim() + "' and ZtCinvcodes='" + txtZtcinvcodes.Text.Trim() + "' and isosid=" + txtisoid.Text.Trim() + " ";
}
DataTable dt = new DataTable();
OLEDBHelper.iLoginEx = iLoginEx;
string selectSQL = "select aCsocode as '订单号',ZtCinvcodes as '母件存货编码',addate as '日期',sales as '销售额',Costs as '成本',Number as '数量',isosid as '唯一的标识', ztCinvcode as '子件存货编码',zs.cinvCCode as '分类编码',ztCinvName as '存货名称',ic.cinvCName as '分类名称', \r\n";
selectSQL += " ztCinvstd as '规格型号',zid as '类别',Yr as '年份' \r\n";
selectSQL += " from zhrs_t_SaleaCosts zs left join \r\n";
selectSQL += " Inventoryclass ic on zs.cinvCCode=ic.cinvccode \r\n";
selectSQL += " " + sql + " \r\n";
dt = OLEDBHelper.GetDataTalbe(selectSQL, CommandType.Text);
dgvRemoveTheSuit.DataSource = dt;
tsslSqlCount.Text = "记录数：" + dt.Rows.Count.ToString();
dgvRemoveTheSuit.DefaultCellStyle.WrapMode = DataGridViewTriState.True;
dgvRemoveTheSuit.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells;
for (int i = 3; i < 6; i++)
{
dgvRemoveTheSuit.Columns[i].DefaultCellStyle.Format = "#,###0.0000";
dgvRemoveTheSuit.Columns[i].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
}
}
private void btnIPC_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
OLEDBHelper.iLoginEx = iLoginEx;
string selectSQL = "select zs.Yr as 年份,zs.aCsocode as 订单号,zs.ztCinvcode as 存货编码,zs.cinvCCode as 存货大类编码,ic.cinvCName as 存货分类 ,zs.ztCinvName as 存货名称,zs.ztCinvstd as 规格型号,zs.addate as 日期,zs.Costs as 成本,zs.sales as 销售额,zs.Number as 数量 \r\n";
selectSQL += " from zhrs_t_SaleaCosts zs left join \r\n";
selectSQL += " Inventoryclass ic on zs.cinvCCode=ic.cinvccode \r\n";
selectSQL += " where zs.cinvCCode like 'SGCA%' and zs.Costs<=0 and zs.Yr='" + iLoginEx.iYear() + "' \r\n";
dt = OLEDBHelper.GetDataTalbe(selectSQL, CommandType.Text);
dgvcostError.DataSource = dt;
dgvcostError.DefaultCellStyle.WrapMode = DataGridViewTriState.True;
dgvcostError.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells;
for (int i = 8; i < 10; i++)
{
dgvcostError.Columns[i].DefaultCellStyle.Format = "#,###0.0000";
dgvcostError.Columns[i].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
}
lblsalescost.Text = "总记录数：" + dt.Rows.Count;
s = "IPC成本为零";
}
private void btncsocode_Click(object sender, EventArgs e)
{
OLEDBHelper.iLoginEx = iLoginEx;
string selectSQL = "select 0 as 'zid','2017' as '年份',a.Csocode as '订单号',a.Cinvcode as '存货编码',a.ddate as '发货单日期',a.isosid as '子表存货唯一标识,a.iSum/a.iquantity as '含税单价',a.iSum*a.iexchrate/(a.itaxrate/100+1) as '销售额',a.iPrice as '成本',a.iexchrate as '汇率',a.itaxrate as '税率',a.iquantity as '数量',a.cInvCCode as '存货大类编码', \r\n";
selectSQL += " a.CinvName as '存货名称',a.Cinvstd as '规格型号',a.ccuscode as '客户编码'' \r\n";
selectSQL += " from (select b.Csocode,b.Cinvcode,k.cInvCCode,h.ccuscode,k.iPrice,k.CinvName,k.Cinvstd, \r\n";
selectSQL += " b.iorderrowno,b.iquantity ,b.itaxrate,b.iSum,h.ddate,c.iexchrate,b.isosid from kcsaleoutH h \r\n";
selectSQL += " left join kcsaleoutb k on h.id=k.id \r\n";
selectSQL += " left join Sales_FHD_W b on k.iDLsID=b.iDLsID \r\n";
selectSQL += " left join Sales_FHD_T c on b.DLID=c.DLID \r\n";
selectSQL += " where h.ddate between '" + iLoginEx.iYear() + "-01-01' and '" + iLoginEx.iYear() + "-12-31' and k.cInvCCode like 'CB%')a where Csocode='" + txtcsocode.Text.Trim() + "' \r\n";
DataTable dt = new DataTable();
dt = OLEDBHelper.GetDataTalbe(selectSQL, CommandType.Text);
dgvsuit.DataSource = dt;
for (int i = 2; i < 7; i++)
{
dgvsuit.Columns[i].DefaultCellStyle.Format = "#,###0.0000";
dgvsuit.Columns[i].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
}
lblcso.Text = "记录数：" + dt.Rows.Count.ToString();
}
private void btnCompare_Click(object sender, EventArgs e)
{
if (i_lst_1.Count > 0)
{
i_lst_1.Clear();
}
OLEDBHelper.iLoginEx = iLoginEx;
string selectSQL = "select a.Csocode as 'aCsocode', sum(a.iSum*a.iexchrate/(a.itaxrate/100+1)) as 'sales',sum(a.iPrice) as 'Costs' \r\n";
selectSQL += " from (select b.Csocode,b.Cinvcode,k.cInvCCode,h.ccuscode,k.iPrice,k.CinvName,k.Cinvstd, \r\n";
selectSQL += " b.iorderrowno,b.iquantity ,b.itaxrate,b.iSum,h.ddate,c.iexchrate,b.isosid from kcsaleoutH h \r\n";
selectSQL += " left join kcsaleoutb k on h.id=k.id \r\n";
selectSQL += " left join Sales_FHD_W b on k.iDLsID=b.iDLsID \r\n";
selectSQL += " left join Sales_FHD_T c on b.DLID=c.DLID \r\n";
selectSQL += " where h.ddate between '" + iLoginEx.iYear() + "-01-01' and '" + iLoginEx.iYear() + "-12-31' and k.cInvCCode like 'CB%')a group by Csocode \r\n";
OleDbDataReader dr = OLEDBHelper.ExecuteReader(selectSQL, CommandType.Text);
while (dr.Read())
{
i_lst.Add(new aCsocodeSales()
{
aCsocode = dr["aCsocode"].ToString(),
sales = Convert.ToDecimal(dr["sales"] == DBNull.Value ? 0 : dr["sales"]),
});
}
dr.Close();
OLEDBHelper.CloseCon();
decimal sum_1 = 0;
string aCsocode_1 = "不存在订单";
foreach (aCsocodeSales u in i_lst)
{
selectSQL = "select count(*) from zhrs_t_SaleaCosts where aCsocode='" + u.aCsocode + "'";
int n = Convert.ToInt32(OLEDBHelper.ExecuteScalar(selectSQL, CommandType.Text));
if (n > 0)
{
selectSQL = "select sum(sales) as 'sales_1',aCsocode from zhrs_t_SaleaCosts where aCsocode='" + u.aCsocode + "' and addate between '" + iLoginEx.iYear() + "-01-01' and '" + iLoginEx.iYear() + "-12-31' group by aCsocode";
OleDbDataReader dr_1 = OLEDBHelper.ExecuteReader(selectSQL, CommandType.Text);
if (dr_1.Read())
{
sum_1 = Convert.ToDecimal(dr_1["sales_1"] == DBNull.Value ? 0 : dr_1["sales_1"]);
aCsocode_1 = dr_1["aCsocode"].ToString();
}
dr_1.Close();
OLEDBHelper.CloseCon();
decimal dec = Convert.ToDecimal(u.sales.ToString("####0.00"));
decimal dec_1 = Convert.ToDecimal(sum_1.ToString("####0.00"));
if (!dec.Equals(dec_1))
{
aCsocodeSales ia = new aCsocodeSales();
ia.aCsocode = u.aCsocode;
ia.sales = u.sales;
ia.aCsocode_1 = aCsocode_1;
ia.sales_1 = sum_1;
ia.Difference = u.sales - sum_1;
i_lst_1.Add(ia);
}
}
else
{
aCsocodeSales ia_1 = new aCsocodeSales();
ia_1.aCsocode = u.aCsocode;
ia_1.sales = u.sales;
ia_1.aCsocode_1 = aCsocode_1;
ia_1.sales_1 = sum_1;
i_lst_1.Add(ia_1);
}
}
dgvSc.DataSource = i_lst_1;
lblCount.Text = "记录数：" + i_lst_1.Count;
}