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