private void AddItem(string sheetname, modVSalesShipmentDetail modd, string col, ref IList <modExcelRangeData> list)
{
switch (sheetname)
{
case "蓝图对帐单":
list.Add(new modExcelRangeData(modd.ProductName, "B" + col, "B" + col));
//list.Add(new modExcelRangeData(modd.No, "C" + col, "C" + col));
list.Add(new modExcelRangeData(modd.ShipDate.ToString("yyyy-MM-dd"), "D" + col, "D" + col));
list.Add(new modExcelRangeData(modd.CustOrderNo, "E" + col, "E" + col));
list.Add(new modExcelRangeData(modd.UnitNo, "F" + col, "F" + col));
list.Add(new modExcelRangeData((modd.Qty * modd.AdFlag).ToString(), "G" + col, "G" + col));
list.Add(new modExcelRangeData(string.Format("{0:C2}", modd.Price), "H" + col, "H" + col));
list.Add(new modExcelRangeData(string.Format("{0:C2}", modd.SumMny * modd.AdFlag), "I" + col, "I" + col));
list.Add(new modExcelRangeData(modd.Remark, "J" + col, "J" + col));
break;
case "威廉姆对帐单":
list.Add(new modExcelRangeData(modd.ShipDate.ToString("yyyy-MM-dd"), "B" + col, "B" + col));
list.Add(new modExcelRangeData(modd.No, "C" + col, "C" + col));
list.Add(new modExcelRangeData(modd.CustOrderNo, "E" + col, "E" + col));
list.Add(new modExcelRangeData(modd.ProductName, "F" + col, "F" + col));
list.Add(new modExcelRangeData((modd.Qty * modd.AdFlag).ToString(), "I" + col, "I" + col));
list.Add(new modExcelRangeData(modd.UnitNo, "J" + col, "J" + col));
list.Add(new modExcelRangeData(string.Format("{0:C2}", modd.Price), "K" + col, "K" + col));
list.Add(new modExcelRangeData(string.Format("{0:C2}", modd.SumMny * modd.AdFlag), "L" + col, "L" + col));
break;
case "深科达对帐单":
list.Add(new modExcelRangeData(modd.ShipDate.ToString("yyyy-MM-dd"), "A" + col, "A" + col));
list.Add(new modExcelRangeData(modd.No, "B" + col, "B" + col));
list.Add(new modExcelRangeData(modd.ProductName, "C" + col, "C" + col));
list.Add(new modExcelRangeData(modd.ProductName, "D" + col, "D" + col));
list.Add(new modExcelRangeData(modd.Specify, "F" + col, "F" + col));
list.Add(new modExcelRangeData(modd.CustOrderNo, "G" + col, "G" + col));
list.Add(new modExcelRangeData(modd.UnitNo, "I" + col, "I" + col));
list.Add(new modExcelRangeData((modd.Qty * modd.AdFlag).ToString(), "J" + col, "J" + col));
list.Add(new modExcelRangeData(string.Format("{0:C2}", modd.Price), "K" + col, "K" + col));
list.Add(new modExcelRangeData(string.Format("{0:C2}", modd.SumMny * modd.AdFlag), "L" + col, "L" + col));
list.Add(new modExcelRangeData(modd.Remark, "M" + col, "M" + col));
break;
default:
list.Add(new modExcelRangeData(modd.ShipDate.ToString("yyyy-MM-dd"), "A" + col, "A" + col));
list.Add(new modExcelRangeData(modd.CustOrderNo, "B" + col, "B" + col));
list.Add(new modExcelRangeData(modd.No, "C" + col, "C" + col));
list.Add(new modExcelRangeData(modd.ProductName, "D" + col, "E" + col));
list.Add(new modExcelRangeData(modd.Specify, "F" + col, "F" + col));
list.Add(new modExcelRangeData((modd.Qty * modd.AdFlag).ToString(), "G" + col, "G" + col));
list.Add(new modExcelRangeData(string.Format("{0:C2}", modd.Price), "H" + col, "H" + col));
list.Add(new modExcelRangeData(string.Format("{0:C2}", modd.SumMny * modd.AdFlag), "I" + col, "I" + col));
list.Add(new modExcelRangeData(modd.Remark, "J" + col, "J" + col));
break;
}
}
private void mnuSalesStatement_Click(object sender, EventArgs e)
{
try
{
this.Cursor = Cursors.WaitCursor;
if (DBGrid.RowCount == 0)
{
return;
}
IList <modExcelRangeData> list = new List <modExcelRangeData>();
string custid = string.Empty;
string custname = string.Empty;
string sheetname = string.Empty;
int copies = 1;
int pagesize = 1;
int pagerows = 1;
int pagecols = 1;
int titleindex = 1;
int datarows = 0;
if (DBGrid.SelectedRows.Count == 0)
{
for (int i = 0; i < DBGrid.RowCount; i++)
{
if (DBGrid.Rows[i].Visible)
{
datarows++;
}
}
}
else
{
for (int i = 0; i < DBGrid.SelectedRows.Count; i++)
{
if (DBGrid.SelectedRows[i].Visible)
{
datarows++;
}
}
}
string company = clsLxms.GetParameterValue("COMPANY_NAME");
switch (company)
{
case "深圳市蓝图净化用品有限公司":
sheetname = "蓝图对帐单";
pagesize = 17;
pagerows = 30;
pagecols = 10;
titleindex = 11;
copies = datarows / pagesize;
if (datarows % pagesize > 0)
{
copies++;
}
break;
default:
switch (custname)
{
case "威廉姆":
sheetname = "威廉姆对帐单";
pagesize = 19;
pagerows = 36;
pagecols = 12;
titleindex = 6;
copies = datarows / pagesize;
if (datarows % pagesize > 0)
{
copies++;
}
break;
case "深科达":
case "深圳市深科达气动设备有限":
sheetname = "深科达对帐单";
pagesize = 17;
pagerows = 31;
pagecols = 13;
titleindex = 8;
copies = datarows / pagesize;
if (datarows % pagesize > 0)
{
copies++;
}
break;
default:
sheetname = "普通对帐单";
pagesize = 22;
pagerows = 36;
pagecols = 10;
titleindex = 10;
copies = datarows / pagesize;
if (datarows % pagesize > 0)
{
copies++;
}
break;
}
break;
}
int currrow = 0;
if (DBGrid.SelectedRows.Count == 0)
{
for (int iCopy = 0; iCopy < copies; iCopy++)
{
int rowindex = 0;
decimal pagesum = 0;
while (rowindex < pagesize && currrow < DBGrid.RowCount)
{
if (currrow < DBGrid.RowCount && DBGrid.Rows[currrow].Visible)
{
if (string.IsNullOrEmpty(custid))
{
custid = DBGrid.Rows[currrow].Cells["CustId"].Value.ToString();
custname = DBGrid.Rows[currrow].Cells["CustName"].Value.ToString();
AddHeader(sheetname, pagerows, iCopy, custid, ref list);
}
else if (custid.CompareTo(DBGrid.Rows[currrow].Cells["CustId"].Value.ToString()) != 0)
{
MessageBox.Show("您所选择的单据必须属于同一个客户!", clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
modVSalesShipmentDetail modd = (modVSalesShipmentDetail)DBGrid.Rows[currrow].DataBoundItem;
string col = (iCopy * pagerows + titleindex + rowindex).ToString().Trim();
AddItem(sheetname, modd, col, ref list);
rowindex++;
pagesum += modd.SumMny;
}
currrow++;
}
AddFoot(sheetname, pagerows, iCopy, copies, pagesum, ref list);
}
}
else
{
for (int iCopy = 0; iCopy < copies; iCopy++)
{
int rowindex = 0;
decimal pagesum = 0;
while (rowindex < pagesize && currrow < DBGrid.SelectedRows.Count)
{
if (DBGrid.SelectedRows[currrow].Visible)
{
if (string.IsNullOrEmpty(custid))
{
custid = DBGrid.SelectedRows[currrow].Cells["CustId"].Value.ToString();
custname = DBGrid.SelectedRows[currrow].Cells["CustName"].Value.ToString();
AddHeader(sheetname, pagerows, iCopy, custid, ref list);
}
else if (custid.CompareTo(DBGrid.SelectedRows[currrow].Cells["CustId"].Value.ToString()) != 0)
{
MessageBox.Show("您所选择的单据必须属于同一个客户!", clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
modVSalesShipmentDetail modd = (modVSalesShipmentDetail)DBGrid.SelectedRows[currrow].DataBoundItem;
string col = (iCopy * pagerows + titleindex + rowindex).ToString().Trim();
AddItem(sheetname, modd, col, ref list);
rowindex++;
pagesum += modd.SumMny;
}
currrow++;
}
AddFoot(sheetname, pagerows, iCopy, copies, pagesum, ref list);
}
}
clsExport.ExportByTemplate(list, sheetname, 1, pagerows, pagecols, copies);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
this.Cursor = Cursors.Default;
}
}
/// <summary>
/// get all salesshipmentdetail
/// <summary>
/// <param name=shipid>shipid</param>
/// <param name=out emsg>return error message</param>
///<returns>details of all salesshipmentdetail</returns>
public BindingCollection <modVSalesShipmentDetail> GetShipDetail(string custid, string custorderno, string productid, out string emsg)
{
try
{
BindingCollection <modVSalesShipmentDetail> modellist = new BindingCollection <modVSalesShipmentDetail>();
//Execute a query to read the categories
string sql = string.Format("select b.ship_id,b.ship_type,b.ship_date,b.status,b.no,b.cust_order_no,b.ad_flag,b.cust_id,b.cust_name,a.product_id,a.product_name,a.specify,a.size,a.unit_no,a.qty,a.price,a.sales_mny,a.currency,a.exchange_rate,a.warehouse_id,a.remark,a.seq "
+ "from sales_shipment_detail a inner join sales_shipment b on a.ship_id=b.ship_id "
+ "where b.cust_id='{0}' and b.cust_order_no='{1}' and a.product_id='{2}' order by a.ship_id,a.seq", custid, custorderno, productid);
using (SqlDataReader rdr = SqlHelper.ExecuteReader(sql))
{
while (rdr.Read())
{
modVSalesShipmentDetail model = new modVSalesShipmentDetail();
model.ShipId = dalUtility.ConvertToString(rdr["ship_id"]);
model.Status = dalUtility.ConvertToInt(rdr["status"]);
model.ShipType = dalUtility.ConvertToString(rdr["ship_type"]);
model.ShipDate = dalUtility.ConvertToDateTime(rdr["ship_date"]);
model.No = dalUtility.ConvertToString(rdr["no"]);
model.CustOrderNo = dalUtility.ConvertToString(rdr["cust_order_no"]);
model.AdFlag = dalUtility.ConvertToInt(rdr["ad_flag"]);
model.CustId = dalUtility.ConvertToString(rdr["cust_id"]);
model.CustName = dalUtility.ConvertToString(rdr["cust_name"]);
//model.ProductId = dalUtility.ConvertToString(rdr["product_id"]);
model.ProductName = dalUtility.ConvertToString(rdr["product_name"]);
model.Specify = dalUtility.ConvertToString(rdr["specify"]);
model.Size = dalUtility.ConvertToDecimal(rdr["size"]);
model.UnitNo = dalUtility.ConvertToString(rdr["unit_no"]);
model.Qty = dalUtility.ConvertToDecimal(rdr["qty"]);
model.Price = dalUtility.ConvertToDecimal(rdr["price"]);
model.SumMny = model.Qty * model.Price;
model.SalesManMny = dalUtility.ConvertToDecimal(rdr["sales_mny"]);
model.Currency = dalUtility.ConvertToString(rdr["currency"]);
model.ExchangeRate = dalUtility.ConvertToDecimal(rdr["exchange_rate"]);
//model.WarehouseId = dalUtility.ConvertToString(rdr["warehouse_id"]);
model.Remark = dalUtility.ConvertToString(rdr["remark"]);
model.Seq = dalUtility.ConvertToInt(rdr["seq"]);
modellist.Add(model);
}
}
sql = string.Format("select id,status,form_date,form_type,ad_flag,no,cust_id,cust_name,pay_method,sales_man,cust_order_no,product_name,unit_no,qty,mny,sales_mny,currency,exchange_rate,remark,receive_status,receive_date,account_no,invoice_status,invoice_no,invoice_mny,update_user,update_time,audit_man,audit_time,acc_name,acc_seq "
+ "from sales_design_form a where b.cust_id='{0}' and b.cust_order_no='{1}'", custid, custorderno, productid);
using (SqlDataReader rdr = SqlHelper.ExecuteReader(sql))
{
while (rdr.Read())
{
modVSalesShipmentDetail model = new modVSalesShipmentDetail();
model.ShipId = dalUtility.ConvertToString(rdr["id"]);
model.Status = dalUtility.ConvertToInt(rdr["status"]);
model.ShipType = dalUtility.ConvertToString(rdr["form_type"]);
model.ShipDate = dalUtility.ConvertToDateTime(rdr["form_date"]);
model.No = dalUtility.ConvertToString(rdr["no"]);
model.CustOrderNo = dalUtility.ConvertToString(rdr["cust_order_no"]);
model.AdFlag = dalUtility.ConvertToInt(rdr["ad_flag"]);
model.CustId = dalUtility.ConvertToString(rdr["cust_id"]);
model.CustName = dalUtility.ConvertToString(rdr["cust_name"]);
model.ProductId = dalUtility.ConvertToString(rdr["product_id"]);
model.ProductName = dalUtility.ConvertToString(rdr["product_name"]);
model.Specify = dalUtility.ConvertToString(rdr["specify"]);
model.Size = dalUtility.ConvertToDecimal(rdr["size"]);
model.UnitNo = dalUtility.ConvertToString(rdr["unit_no"]);
model.Qty = dalUtility.ConvertToDecimal(rdr["qty"]);
model.Price = dalUtility.ConvertToDecimal(rdr["price"]);
model.SumMny = model.Qty * model.Price;
model.SalesManMny = dalUtility.ConvertToDecimal(rdr["sales_mny"]);
model.Currency = dalUtility.ConvertToString(rdr["currency"]);
model.ExchangeRate = dalUtility.ConvertToDecimal(rdr["exchange_rate"]);
model.WarehouseId = dalUtility.ConvertToString(rdr["warehouse_id"]);
model.Remark = dalUtility.ConvertToString(rdr["remark"]);
model.Seq = dalUtility.ConvertToInt(rdr["seq"]);
modellist.Add(model);
}
}
emsg = null;
return(modellist);
}
catch (Exception ex)
{
emsg = dalUtility.ErrorMessage(ex.Message);
return(null);
}
}