private void WriteSheet(Worksheet worksheet, string conditon, IList<CM_Client> clientlists, AC_AccountMonth Month, int cloumn)
{
IList<PDT_Product> productlists = PDT_ProductBLL.GetModelList(conditon);
int bgcolor1 = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightYellow);
int bgcolor2 = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGreen);
#region 创建表头
if (clientlists[0].ClientType == 2)
{
worksheet.Cells[1, 1] = "分销商ID";
worksheet.Cells[1, 2] = "分销商编号";
worksheet.Cells[1, 3] = "分销商名称";
worksheet.Cells[1, 4] = "分销商分类";
worksheet.Cells[1, 5] = "归属月份";
}
else
{
worksheet.Cells[1, 1] = "零售商ID";
worksheet.Cells[1, 2] = "零售商编号";
worksheet.Cells[1, 3] = "零售商名称";
worksheet.Cells[1, 4] = "零售商分类";
worksheet.Cells[1, 5] = "归属月份";
}
if (cloumn == 8)
{
worksheet.Cells[1, 6] = "导购ID";
worksheet.Cells[1, 7] = "导购姓名";
}
worksheet.get_Range("B2", "B2").ColumnWidth = 15;
worksheet.get_Range("C2", "C2").ColumnWidth = 20;
worksheet.get_Range("D2", "E2").ColumnWidth = 10;
worksheet.get_Range("A1", "A1").RowHeight = 50;
worksheet.get_Range("A1", "CC1").WrapText = true;
worksheet.get_Range("A1", "CC1").Font.Bold = true;
worksheet.get_Range("A1", "CC1000").Font.Size = 9;
worksheet.get_Range("A1", "CC1000").HorizontalAlignment = XlHAlign.xlHAlignCenter;
#region 将零售商信息写入表格内
int sellrow = 2;
foreach (CM_Client client in clientlists)
{
worksheet.Cells[sellrow, 1] = client.ID;
worksheet.Cells[sellrow, 2] = client.Code;
worksheet.Cells[sellrow, 3] = client.FullName;
try
{
worksheet.Cells[sellrow, 4] = client.ClientType == 2 ? "分销商" : DictionaryBLL.GetDicCollections("CM_RT_Classify")[client["RTClassify"]].Name;
}
catch
{ ;}
worksheet.Cells[sellrow, 5] = "'" + Month.Name;
if (cloumn == 8)
{
StringBuilder condition = new StringBuilder(" PM_Promotor.BeginWorkDate<'" + Month.EndDate.AddDays(1).ToString("yyyy-MM-dd") + "' AND ISNULL(PM_Promotor.EndWorkDate,GETDATE())>='" + Month.BeginDate.ToString("yyyy-MM-dd") + "' AND PM_Promotor.ApproveFlag=1 ");
condition.Append("AND ID in (SELECT Promotor FROM PM_PromotorInRetailer WHERE Client = " + client.ID.ToString() + ")");
IList<PM_Promotor> promotorlists = PM_PromotorBLL.GetModelList(condition.ToString());
for (int j = 0; j < promotorlists.Count; j++)
{
if (j > 0)
{
worksheet.Cells[sellrow, 1] = client.ID;
worksheet.Cells[sellrow, 2] = client.Code;
worksheet.Cells[sellrow, 3] = client.FullName;
try
{
worksheet.Cells[sellrow, 4] = DictionaryBLL.GetDicCollections("CM_RT_Classify")[client["RTClassify"]].Name;
}
catch
{ ;}
worksheet.Cells[sellrow, 5] = "'" + Month.Name;
}
worksheet.Cells[sellrow, 6] = promotorlists[j].ID;
worksheet.Cells[sellrow, 7] = promotorlists[j].Name;
if (j != promotorlists.Count - 1) sellrow++;
}
}
sellrow++;
}
#endregion
#endregion
//将产品写入表头
for (int i = 0; i < productlists.Count; i++)
{
worksheet.Cells[1, cloumn + i] = productlists[i].ShortName;
#region 按品牌设置产品列颜色
if (i > 0)
{
if (productlists[i].Brand == productlists[i - 1].Brand)
{
worksheet.get_Range(worksheet.Cells[1, cloumn + i], worksheet.Cells[1000, cloumn + i]).Interior.Color =
worksheet.get_Range(worksheet.Cells[1, cloumn + i - 1], worksheet.Cells[1000, cloumn + i - 1]).Interior.Color;
}
else
{
if (int.Parse(worksheet.get_Range(worksheet.Cells[1, cloumn + i - 1], worksheet.Cells[1000, cloumn + i - 1]).Interior.Color.ToString()) == bgcolor1)
worksheet.get_Range(worksheet.Cells[1, cloumn + i], worksheet.Cells[1000, cloumn + i]).Interior.Color = bgcolor2;
else
worksheet.get_Range(worksheet.Cells[1, cloumn + i], worksheet.Cells[1000, cloumn + i]).Interior.Color = bgcolor1;
}
}
else
{
worksheet.get_Range(worksheet.Cells[1, cloumn + i], worksheet.Cells[1000, cloumn + i]).Interior.Color = bgcolor1;
}
#endregion
}
#region 设置表格格式
//设置行高
worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[sellrow - 1, 1]).RowHeight = 16;
//设置表格单元格格线
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[sellrow - 1, cloumn + productlists.Count - 1]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
#endregion
}
private string ImportGifts(Worksheet worksheet, string remark, AC_AccountMonth month, int insertstaff, IList<PDT_Product> productlists, out int State)
{
string ImportInfo = "";
int cloumn = 6;
int sellinrow = 1;
DateTime maxday = DateTime.Today < month.EndDate ? DateTime.Today : month.EndDate;
ImportInfo += "<br/>";
ImportInfo += "<span style='color: Red'>-------------------------------------------------------------------------</span><br/>";
ImportInfo += "<span style='color: Red'>----" + remark + "----</span><br/>";
State = 0;
PDT_Product product;
#region 读取Excel表格
while (true)
{
cloumn = 6;
sellinrow++;
if (((Range)worksheet.Cells[sellinrow, 1]).Value2 == null)
{
break;
}
int clientid = 0;
if (!int.TryParse(((Range)worksheet.Cells[sellinrow, 1]).Value2.ToString(), out clientid))
{
continue;
}
#region 验证数据
CM_Client client = new CM_ClientBLL(clientid).Model;
if (client == null || client.FullName != ((Range)worksheet.Cells[sellinrow, 3]).Text.ToString())
{
ImportInfo += "<span style='color: Red'>ID号：" + ((Range)worksheet.Cells[sellinrow, 1]).Text.ToString() + (client.ClientType == 3 ? "，零售商ID与零售商名称" : "，分销商ID与分销商名称") + "不匹配！</span><br/>";
State = 4;
continue;
}
int uplimit = client.ClientType == 3 ? 5000 : 9000;
if (((Range)worksheet.Cells[sellinrow, 5]).Text.ToString() != month.Name)
{
ImportInfo += "<span style='color: Red'>ID号：" + ((Range)worksheet.Cells[sellinrow, 1]).Text.ToString() + "，归属月份必须为【" + month.Name + "】</span><br/>";
State = 4;
continue;
}
#endregion
#region 组织销量头
SVM_SalesVolumeBLL bll = null;
IList<SVM_SalesVolume> svmlists = SVM_SalesVolumeBLL.GetModelList("Client=" + clientid.ToString() + " AND InsertStaff!=1"
+ " AND Type IN(1,2) AND AccountMonth=" + month.ID.ToString() //+ " AND SalesDate='" + salesdate.ToString("yyyy-MM-dd")
+ " AND Flag=11 AND (Remark='" + remark + "' OR Remark NOT IN ('Excel批量导入本品赠品','Excel批量导入试用装赠品','Excel批量导入高值促销品'))");
if (svmlists.Count > 0)
{
if (svmlists.FirstOrDefault(p => p.ApproveFlag == 1) != null)
{
ImportInfo += "<span style='color: Red'>ID号：" + ((Range)worksheet.Cells[sellinrow, 1]).Text.ToString() + (client.ClientType == 3 ? "，该零售商" : "，该分销商") + "当月的本品赠品进货已审核，不可再次导入！</span><br/>";
continue;
}
//仅存在一条符合条件的赠品,进行覆盖
if (svmlists.Count == 1 && svmlists[0].Remark == remark)
{
bll = new SVM_SalesVolumeBLL(svmlists[0].ID);
bll.Items.Clear();
}
}
if (bll == null)
{
bll = new SVM_SalesVolumeBLL();
bll.Model.Client = clientid;
bll.Model.OrganizeCity = client.OrganizeCity;
bll.Model.Supplier = client.Supplier;
bll.Model.AccountMonth = month.ID;
bll.Model.SalesDate = maxday;
bll.Model.Type = 2;
bll.Model.ApproveFlag = 2;
bll.Model.Flag = 11; //赠品进货
bll.Model["IsCXP"] = "Y";
bll.Model.InsertStaff = insertstaff;
bll.Model.Remark = remark;
}
#endregion
bll.Model["SubmitFlag"] = "1";
IList<SVM_SalesVolume_Detail> details = new List<SVM_SalesVolume_Detail>();
int quantity = 0;
bool wrongflag = false;//判断导入数量是否正常（除空导致的异常）
bool isnumber = false;
while (true)
{
if (((Range)worksheet.Cells[1, cloumn]).Text.ToString() == string.Empty)
{
break;
}
IList<PDT_Product> products = PDT_ProductBLL.GetModelList("ShortName='" + ((Range)worksheet.Cells[1, cloumn]).Text + "' AND State=1");
if (products.Count > 0)
{
product = products[0];
}
else
{
ImportInfo += "<span style='color: Red'>赠品产品名称：" + ((Range)worksheet.Cells[1, cloumn]).Text + "在赠品列表中不存在！</span><br/>";
State = 4;
cloumn++;
continue;
}
if (product != null && ((Range)worksheet.Cells[sellinrow, cloumn]).Value2 != null)
{
#region 读取各产品销量
if (!isnumber && int.TryParse(((Range)worksheet.Cells[sellinrow, cloumn]).Value2.ToString(), out quantity))
{
isnumber = true;
}
if (quantity != 0 && quantity <= uplimit && quantity >= 0)
{
decimal factoryprice = 0, salesprice = 0;
PDT_ProductPriceBLL.GetPriceByClientAndType(client.ID, product.ID, 2, out factoryprice, out salesprice);
if (factoryprice == 0) factoryprice = product.FactoryPrice;
if (salesprice == 0) salesprice = product.NetPrice;
SVM_SalesVolume_Detail detail = new SVM_SalesVolume_Detail();
detail.Product = product.ID;
detail.FactoryPrice = factoryprice;
detail.SalesPrice = salesprice;
detail.Quantity = quantity;
details.Add(detail);
}
else if (trim(((Range)worksheet.Cells[sellinrow, cloumn]).Text.ToString()) != "" && ((Range)worksheet.Cells[sellinrow, cloumn]).Text.ToString() != "0")
{
wrongflag = true;
break;
}
#endregion
}
cloumn++;
}
if (wrongflag)
{
ImportInfo += "<span style='color: Red'>ID号：" + clientid.ToString() + (client.ClientType == 3 ? "，零售商" : "，分销商：") + client.FullName
+ "的赠品进货单未能导入，赠品名称：" + ((Range)worksheet.Cells[1, cloumn]).Text + "数量填写错误";
State = 4;
continue;
}
#region 更新销量至数据库
if (bll.Model.ID > 0)
{
if (details.Count > 0)
{
bll.DeleteDetail(); //先清除原先导入的数据
bll.Model.UpdateStaff = insertstaff;
bll.Items = details;
bll.AddDetail();
bll.Update();
ImportInfo += "<span style='color: Blue'>ID号：" + clientid.ToString() + (client.ClientType == 3 ? "，零售商：" : "，分销商：") + client.FullName
+ " 的原有日期为：" + bll.Model.SalesDate.ToString("yyyy-MM-dd") + " 的赠品进货单被成功更新！产品SKU数："
+ bll.Items.Count.ToString() + "，产品总数量：" + bll.Items.Sum(p => p.Quantity).ToString() + "</span><br/>";
}
if (details.Count == 0 && isnumber)
{
bll.DeleteDetail();
}
}
else
{
if (details.Count > 0 || svmlists.Count == 0) //没有产品也新增一条空销量头
{
bll.Items = details;
if (bll.Add() > 0)
{
foreach (SVM_SalesVolume m in svmlists)
{
bll = new SVM_SalesVolumeBLL(m.ID);
bll.DeleteDetail();
bll.Delete();
}
}
ImportInfo += "<span style='color: Black'>ID号：" + clientid.ToString() + (client.ClientType == 3 ? "，零售商：" : "，分销商：") + client.FullName
+ " 的赠品进货单已成功导入！产品SKU数：" + bll.Items.Count.ToString() + "，产品总数量："
+ bll.Items.Sum(p => p.Quantity).ToString() + "</span><br/>";
}
}
#endregion
}
#endregion
return ImportInfo;
}