/// <summary>
/// 增加一条数据
/// </summary>
public int Insert(SqlTransaction sqltran, Entities.TTable model)
{
SqlParameter[] parameters =
{
new SqlParameter("@RecID", SqlDbType.Int, 4),
new SqlParameter("@TTCode", SqlDbType.NVarChar, 20),
new SqlParameter("@TTDesName", SqlDbType.NVarChar, 40),
new SqlParameter("@TTName", SqlDbType.NVarChar, 40),
new SqlParameter("@Status", SqlDbType.Int, 4),
new SqlParameter("@CreateTime", SqlDbType.DateTime),
new SqlParameter("@CreateUserID", SqlDbType.Int, 4),
new SqlParameter("@TTIsData", SqlDbType.Int, 4)
};
parameters[0].Direction = ParameterDirection.Output;
parameters[1].Value = model.TTCode;
parameters[2].Value = model.TTDesName;
parameters[3].Value = model.TTName;
parameters[4].Value = model.Status;
parameters[5].Value = model.CreateTime;
parameters[6].Value = model.CreateUserID;
parameters[7].Value = model.TTIsData;
SqlHelper.ExecuteNonQuery(sqltran, CommandType.StoredProcedure, P_TTABLE_INSERT, parameters);
return((int)parameters[0].Value);
}
private void Generate(out string msg)
{
msg = "";
int RecID = int.Parse(recid);
Entities.TTable ttableModel = new Entities.TTable();
List <Entities.TField> fieldList = new List <Entities.TField>();
Entities.TPage tpageModel = BLL.TPage.Instance.GetTPage(RecID);
if (tpageModel != null)
{
ttableModel = BLL.TTable.Instance.GetTTableByTTCode(tpageModel.TTCode);
fieldList = BLL.TField.Instance.GetTFieldListByTTCode(tpageModel.TTCode);
if (fieldList.Count > 0)
{
GenerateFields(tpageModel, ttableModel, fieldList, out msg);
}
else
{
msg += "模板没有字段，不能生成";
}
}
else
{
msg += "没有找到对应的模板信息";
}
}
private Entities.TTable LoadSingleTTable(DataRow row)
{
Entities.TTable model = new Entities.TTable();
if (row["RecID"].ToString() != "")
{
model.RecID = int.Parse(row["RecID"].ToString());
}
model.TTCode = row["TTCode"].ToString();
model.TTDesName = row["TTDesName"].ToString();
model.TTName = row["TTName"].ToString();
if (row["Status"].ToString() != "")
{
model.Status = int.Parse(row["Status"].ToString());
}
if (row["CreateTime"].ToString() != "")
{
model.CreateTime = DateTime.Parse(row["CreateTime"].ToString());
}
if (row["CreateUserID"].ToString() != "")
{
model.CreateUserID = int.Parse(row["CreateUserID"].ToString());
}
if (row["TTIsData"].ToString() != "")
{
model.TTIsData = int.Parse(row["TTIsData"].ToString());
}
return(model);
}
public Entities.TTable GetTTableByTTCode(string ttcode)
{
Entities.TTable ttableModel = new Entities.TTable();
Entities.QueryTTable query = new QueryTTable();
query.TTCode = ttcode;
int totalCount = 0;
DataTable dt = BLL.TTable.Instance.GetTTable(query, "", 1, 999, out totalCount);
if (dt != null && dt.Rows.Count > 0)
{
ttableModel = Dal.TTable.Instance.GetTTable(int.Parse(dt.Rows[0]["RecID"].ToString()));
}
return(ttableModel);
}
//获取状态 add lxw 13.3.22
//未完成-保存但未生成模板-status=0；已完成-已保存且生成模板但新建的新表没数据-status=1；已使用：已生成模板并新建的新表存在数据-status=1
//在为已完成时，需要根据TTable表查询该TTcode下的TTIsData是否为1，如果为1则为已使用，status赋值为2
public int getStatusByTTCode(string status, string ttCode)
{
int _status;
if (int.TryParse(status, out _status) && ttCode != string.Empty)
{
if (_status == 1)
{
//判断该生成的模板表是否存在数据
Entities.TTable model = BLL.TTable.Instance.GetTTableByTTCode(ttCode);
if (model != null && model.TTIsData == 1)//等于1，表示模板表有数据，状态修改为已使用
{
_status = 2;
}
}
}
return(_status);
}
public void GetTaskOrderInfoValues(string PTID, out string ProvinceID, out string CityID, out string YXBrandID, out string YXSerialID)
{
ProvinceID = "";
CityID = "";
YXBrandID = "";
YXSerialID = "";
//BLL.Loger.Log4Net.Info(string.Format("BitAuto.ISDC.CC2012.BLL.OtherTaskInfo，根据任务id:{0}取意向车型，意向品牌，省份，城市，开始...", PTID));
Entities.OtherTaskInfo taskInfo = BLL.OtherTaskInfo.Instance.GetOtherTaskInfo(PTID);
if (taskInfo != null)
{
Entities.TTable ttable = BLL.TTable.Instance.GetTTableByTTCode(taskInfo.RelationTableID);
if (ttable != null)
{
DataTable TempDataTable = Dal.OtherTaskInfo.Instance.GetCustomTable(taskInfo.RelationID, ttable.TTName);
if (TempDataTable.Rows.Count == 1)
{
for (int colIndex = TempDataTable.Columns.Count - 1; colIndex >= 0; colIndex--)
{
if (TempDataTable.Columns[colIndex].ColumnName.IndexOf("_Province") != -1)
{
//存在省份字段
ProvinceID = TempDataTable.Rows[0][colIndex].ToString();
}
if (TempDataTable.Columns[colIndex].ColumnName.IndexOf("_City") != -1)
{
//存在城市字段
CityID = TempDataTable.Rows[0][colIndex].ToString();
}
if (TempDataTable.Columns[colIndex].ColumnName.IndexOf("_YXBrand") != -1)
{
//存在意向品牌字段
YXBrandID = TempDataTable.Rows[0][colIndex].ToString();
}
if (TempDataTable.Columns[colIndex].ColumnName.IndexOf("_YXSerial") != -1)
{
//存在意向车型字段
YXSerialID = TempDataTable.Rows[0][colIndex].ToString();
}
}
}
}
}
//BLL.Loger.Log4Net.Info(string.Format("BitAuto.ISDC.CC2012.BLL.OtherTaskInfo，根据任务id:{0}取意向车型，意向品牌，省份，城市，结束", PTID));
}
/// <summary>
/// 获取项目、自定义数据表、字段信息 基本信息
/// </summary>
/// <param name="ProjectID"></param>
/// <param name="model"></param>
/// <param name="TTCode"></param>
/// <param name="ttable"></param>
/// <param name="TTName"></param>
/// <param name="fieldList"></param>
/// <param name="msg"></param>
private void GetBaseInfo(int ProjectID, out Entities.ProjectInfo model, out string TTCode, out Entities.TTable ttable, out string TTName, out List <Entities.TField> fieldList, out string msg)
{
msg = string.Empty;
model = BLL.ProjectInfo.Instance.GetProjectInfo(ProjectID);
TTCode = string.Empty;
ttable = new Entities.TTable();
TTName = string.Empty;
fieldList = new List <Entities.TField>();
if (model == null)
{
msg += "没找到对应的项目";
return;
}
if (model.Source != 4)
{
msg += "此类型项目不允许导出";
return;
}
TTCode = model.TTCode;
ttable = BLL.TTable.Instance.GetTTableByTTCode(TTCode); //
if (ttable.TTName == "" || ttable.TTName == null)
{
msg += "没有找到定义的自定义数据表名称";
return;
}
TTName = ttable.TTName;
fieldList = BLL.TField.Instance.GetTFieldListByTTCode(TTCode);
}
/// <summary>
/// 更新一条数据
/// </summary>
public int Update(Entities.TTable model)
{
SqlParameter[] parameters =
{
new SqlParameter("@RecID", SqlDbType.Int, 4),
new SqlParameter("@TTCode", SqlDbType.NVarChar, 20),
new SqlParameter("@TTDesName", SqlDbType.NVarChar, 40),
new SqlParameter("@TTName", SqlDbType.NVarChar, 40),
new SqlParameter("@Status", SqlDbType.Int, 4),
new SqlParameter("@CreateTime", SqlDbType.DateTime),
new SqlParameter("@CreateUserID", SqlDbType.Int, 4),
new SqlParameter("@TTIsData", SqlDbType.Int, 4)
};
parameters[0].Value = model.RecID;
parameters[1].Value = model.TTCode;
parameters[2].Value = model.TTDesName;
parameters[3].Value = model.TTName;
parameters[4].Value = model.Status;
parameters[5].Value = model.CreateTime;
parameters[6].Value = model.CreateUserID;
parameters[7].Value = model.TTIsData;
return(SqlHelper.ExecuteNonQuery(CONNECTIONSTRINGS, CommandType.StoredProcedure, P_TTABLE_UPDATE, parameters));
}
private void Submit(out string msg, int userID, out string returnTTCode)
{
msg = "";
returnTTCode = "";
string datainfoStr = DataStr;
TemplateInfo tempInfoData = null;
int templateStatus = -1;//模板状态
Entities.TPage tpageModel = new Entities.TPage();
Entities.TTable ttable = new Entities.TTable();
List <Entities.TField> editfieldList = new List <Entities.TField>();
List <Entities.TField> addfieldList = new List <Entities.TField>();
Entities.TField fieldModel = new Entities.TField();
List <Entities.TField> delFields = new List <Entities.TField>();
tempInfoData = (TemplateInfo)Newtonsoft.Json.JavaScriptConvert.DeserializeObject(datainfoStr, typeof(TemplateInfo));
#region 验证数据
CheckMsg(tempInfoData, out msg);
if (msg != "")
{
return;
}
#endregion
#region 准备数据
if (tempInfoData.ttcode != "")
{
//编辑
#region 模板信息
tpageModel = BLL.TPage.Instance.GetTPageByTTCode(tempInfoData.ttcode);
if (tpageModel != null)
{
//状态
templateStatus = BLL.TPage.Instance.getStatus(tpageModel.RecID.ToString(), tpageModel.Status.ToString());
if (templateStatus != 0)
{
msg += "模板当前状态下不允许编辑";
return;
}
tpageModel.TPName = tempInfoData.templateName;
tpageModel.BGID = int.Parse(tempInfoData.BGID);
tpageModel.SCID = int.Parse(tempInfoData.CID);
tpageModel.TPContent = tempInfoData.templateDesc;
tpageModel.IsShowBtn = int.Parse(tempInfoData.IsShowBtn);
tpageModel.IsShowWorkOrderBtn = int.Parse(tempInfoData.IsShowWorkOrderBtn);
tpageModel.IsShowSendMsgBtn = int.Parse(tempInfoData.IsShowSendMsgBtn);
tpageModel.IsShowQiCheTong = int.Parse(tempInfoData.IsShowQiCheTong);
tpageModel.IsShowSubmitOrder = int.Parse(tempInfoData.IsShowSubmitOrder);
}
else
{
msg += "没有找到相关模板信息";
return;
}
#endregion
#region 自定义数据表
ttable = BLL.TTable.Instance.GetTTableByTTCode(tempInfoData.ttcode);
if (ttable != null)
{
ttable.TTDesName = tempInfoData.templateName;
}
else
{
msg += "没有找到相关的自定义表信息";
return;
}
#endregion
#region 编辑字段
editfieldList = BLL.TField.Instance.GetTFieldListByTTCode(tempInfoData.ttcode);
List <FieldInfoData> updateFieldInfo = new List <FieldInfoData>();
#region 新增的
for (int i = tempInfoData.fieldListInfo.Length - 1; i >= 0; i--)
{
if (tempInfoData.fieldListInfo[i].RecID == "")
{
//新增
fieldModel = GetOneFieldMode(userID, tempInfoData.fieldListInfo[i], tpageModel.TTCode, "");
addfieldList.Add(fieldModel);
}
else
{
//编辑的
updateFieldInfo.Add(tempInfoData.fieldListInfo[i]);
}
}
#endregion
int existflog = 0;
for (int i = editfieldList.Count - 1; i >= 0; i--)
{
existflog = 0;
if (tempInfoData.fieldListInfo != null)
{
foreach (FieldInfoData field in updateFieldInfo)
{
if (field.RecID == editfieldList[i].RecID.ToString())
{
//编辑
editfieldList[i].TFDesName = field.TFDesName;
editfieldList[i].TFDes = field.TFDes;
editfieldList[i].TFInportIsNull = int.Parse(field.TFInportIsNull);
editfieldList[i].TFIsNull = int.Parse(field.TFIsNull);
editfieldList[i].TFSortIndex = int.Parse(field.TFSortIndex);
editfieldList[i].TFIsExportShow = int.Parse(field.TFIsExportShow);
editfieldList[i].TFIsListShow = int.Parse(field.TFIsListShow);
editfieldList[i].TFValue = field.TFValue;
editfieldList[i].TFCssName = field.TFCssName;
existflog = 1;
}
}
// //在页面传来的对象中没有，是删除
if (existflog == 0)
{
//删除的
delFields.Add(editfieldList[i]);
editfieldList.RemoveAt(i);
}
}
}
#endregion
}
else
{
//新增
#region 自定义数据表
int maxID = BLL.TTable.Instance.GetMaxID();
ttable.TTCode = "t" + maxID;
ttable.TTDesName = tempInfoData.templateName;
ttable.TTName = "Tempt" + maxID;
ttable.Status = 0;
ttable.CreateTime = DateTime.Now;
ttable.CreateUserID = userID;
#endregion
#region 模板表
tpageModel.TPName = tempInfoData.templateName;
tpageModel.BGID = int.Parse(tempInfoData.BGID);
tpageModel.SCID = int.Parse(tempInfoData.CID);
tpageModel.TPRef = "";
tpageModel.TTCode = ttable.TTCode;
tpageModel.TPContent = tempInfoData.templateDesc;
tpageModel.GenTempletPath = "";
tpageModel.Status = 0;
tpageModel.Remark = "";
tpageModel.CreateTime = DateTime.Now;
tpageModel.CreateUserID = userID;
tpageModel.TTName = ttable.TTName;
tpageModel.IsShowBtn = int.Parse(tempInfoData.IsShowBtn);
tpageModel.IsShowWorkOrderBtn = int.Parse(tempInfoData.IsShowWorkOrderBtn);
tpageModel.IsShowSendMsgBtn = int.Parse(tempInfoData.IsShowSendMsgBtn);
tpageModel.IsShowQiCheTong = int.Parse(tempInfoData.IsShowQiCheTong);
tpageModel.IsShowSubmitOrder = int.Parse(tempInfoData.IsShowSubmitOrder);
tpageModel.IsUsed = -1;
#endregion
#region 字段
if (tempInfoData.fieldListInfo != null)
{
foreach (FieldInfoData item in tempInfoData.fieldListInfo)
{
string ttCode = ttable.TTCode;
fieldModel = GetOneFieldMode(userID, item, ttCode, "");
addfieldList.Add(fieldModel);
}
}
#endregion
}
#endregion
#region 提交事务
string connectionstrings = ConfigurationUtil.GetAppSettingValue("ConnectionStrings_CC");
SqlConnection connection = new SqlConnection(connectionstrings);
connection.Open();
SqlTransaction tran = connection.BeginTransaction("SampleTransaction");
try
{
if (tempInfoData.ttcode == "")
{
//新增
BLL.TPage.Instance.Insert(tran, tpageModel);
BLL.TTable.Instance.Insert(tran, ttable);
//新增的字段
foreach (Entities.TField item in addfieldList)
{
BLL.TField.Instance.Insert(tran, item);
}
returnTTCode = tpageModel.TTCode;
}
else
{
//编辑
BLL.TPage.Instance.Update(tran, tpageModel);
BLL.TTable.Instance.Update(tran, ttable);
//编辑的字段
foreach (Entities.TField item in editfieldList)
{
BLL.TField.Instance.Update(tran, item);
}
//新增的字段
foreach (Entities.TField item in addfieldList)
{
BLL.TField.Instance.Insert(tran, item);
#region 如果已经生成模板，添加物理表的字段（修改为：模板生成了，还可以修改，⊙﹏⊙b汗）
if (templateStatus == 1)
{
try
{
List <string> addNames = GetRealFieldName(item);
foreach (string name in addNames)
{
BLL.TField.Instance.AddColumn(ttable.TTName, name, item.TTypeID.ToString());
}
}
catch (Exception ex)
{
msg += "添加数据表字段失败！<br>";
}
}
#endregion
}
//删除的字段
foreach (Entities.TField item in delFields)
{
BLL.TField.Instance.Delete(item.RecID);
#region 如果已经生成模板，删除物理表的字段
if (templateStatus == 1)
{
try
{
List <string> delNames = GetRealFieldName(item);
foreach (string name in delNames)
{
BLL.TField.Instance.DelColumn(ttable.TTName, name);
}
}
catch (Exception ex)
{
msg += "删除数据表字段失败！<br>";
}
}
#endregion
}
returnTTCode = tpageModel.TTCode;
}
tran.Commit();
}
catch (Exception ex)
{
if (tran.Connection != null)
{
tran.Rollback();
}
msg = ex.Message.ToString();
}
finally
{
connection.Close();
}
#endregion
}
/// <summary>
/// 创建表的语句
/// </summary>
/// <param name="ttableModel"></param>
/// <param name="fieldList"></param>
/// <returns></returns>
public string CreateSql(Entities.TTable ttableModel, List <Entities.TField> fieldList)
{
string sqlStr = "";
sqlStr += "create table " + ttableModel.TTName + " (";
sqlStr += " [RecID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY, ";
sqlStr += " [Status] [int] , ";
sqlStr += " [CreateTime] [datetime] NULL,";
sqlStr += " [CreateUserID] [int] NULL,";
//是否有个人用户字段
List <Entities.TField> listFind = fieldList.FindAll(delegate(Entities.TField field)
{
return(field.TFShowCode == "100015");
});
if (listFind.Count > 0)
{
fieldList.RemoveAll(delegate(Entities.TField field)
{
return(field.TFShowCode == "100015");
});
foreach (Entities.TField item in listFind)
{
switch (item.TFDesName)
{
case "姓名":
sqlStr += item.TFName + " " + BLL.Util.GetEnumOptText(typeof(EnumTFieldType), (int)EnumTFieldType.Tnvarchar) + "(" + item.TFLen + "),";
break;
case "性别":
sqlStr += item.TFName + "_radioid " + BLL.Util.GetEnumOptText(typeof(EnumTFieldType), (int)EnumTFieldType.Tint) + ",";
sqlStr += item.TFName + "_radioid_name " + BLL.Util.GetEnumOptText(typeof(EnumTFieldType), (int)EnumTFieldType.Tnvarchar) + "(" + item.TFLen + "),";
break;
case "电话":
sqlStr += item.TFName + " " + BLL.Util.GetEnumOptText(typeof(EnumTFieldType), (int)EnumTFieldType.Tnvarchar) + "(" + item.TFLen + "),";
break;
default:
break;
}
}
}
foreach (Entities.TField item in fieldList)
{
if (item.TFShowCode == "100009")
{
//日期段
sqlStr += item.TFName + "_startdata " + BLL.Util.GetEnumOptText(typeof(EnumTFieldType), (int)EnumTFieldType.Tdatetime) + ",";
sqlStr += item.TFName + "_enddata " + BLL.Util.GetEnumOptText(typeof(EnumTFieldType), (int)EnumTFieldType.Tdatetime) + ",";
}
if (item.TFShowCode == "100011")
{
//时间段
sqlStr += item.TFName + "_starttime " + BLL.Util.GetEnumOptText(typeof(EnumTFieldType), (int)EnumTFieldType.Tdatetime) + ",";
sqlStr += item.TFName + "_endtime " + BLL.Util.GetEnumOptText(typeof(EnumTFieldType), (int)EnumTFieldType.Tdatetime) + ",";
}
else if (item.TFShowCode == "100012")
{
//二级省市
sqlStr += item.TFName + "_Province " + BLL.Util.GetEnumOptText(typeof(EnumTFieldType), (int)EnumTFieldType.Tint) + ",";
sqlStr += item.TFName + "_Province_name " + BLL.Util.GetEnumOptText(typeof(EnumTFieldType), (int)EnumTFieldType.Tnvarchar) + "(" + item.TFLen + "),";
sqlStr += item.TFName + "_City " + BLL.Util.GetEnumOptText(typeof(EnumTFieldType), (int)EnumTFieldType.Tint) + ",";
sqlStr += item.TFName + "_City_name " + BLL.Util.GetEnumOptText(typeof(EnumTFieldType), (int)EnumTFieldType.Tnvarchar) + "(" + item.TFLen + "),";
}
else if (item.TFShowCode == "100013")
{
//三级省市县
sqlStr += item.TFName + "_Province " + BLL.Util.GetEnumOptText(typeof(EnumTFieldType), (int)EnumTFieldType.Tint) + ",";
sqlStr += item.TFName + "_Province_name " + BLL.Util.GetEnumOptText(typeof(EnumTFieldType), (int)EnumTFieldType.Tnvarchar) + "(" + item.TFLen + "),";
sqlStr += item.TFName + "_City " + BLL.Util.GetEnumOptText(typeof(EnumTFieldType), (int)EnumTFieldType.Tint) + ",";
sqlStr += item.TFName + "_City_name " + BLL.Util.GetEnumOptText(typeof(EnumTFieldType), (int)EnumTFieldType.Tnvarchar) + "(" + item.TFLen + "),";
sqlStr += item.TFName + "_Country " + BLL.Util.GetEnumOptText(typeof(EnumTFieldType), (int)EnumTFieldType.Tint) + ",";
sqlStr += item.TFName + "_Country_name " + BLL.Util.GetEnumOptText(typeof(EnumTFieldType), (int)EnumTFieldType.Tnvarchar) + "(" + item.TFLen + "),";
}
else if (item.TFShowCode == "100004")//复选
{
sqlStr += item.TFName + "_checkid " + BLL.Util.GetEnumOptText(typeof(EnumTFieldType), (int)EnumTFieldType.Tnvarchar) + "(" + item.TFLen + "),";
sqlStr += item.TFName + "_checkid_name " + BLL.Util.GetEnumOptText(typeof(EnumTFieldType), (int)EnumTFieldType.Tnvarchar) + "(" + item.TFLen + "),";
}
else if (item.TFShowCode == "100003")//单选
{
sqlStr += item.TFName + "_radioid " + BLL.Util.GetEnumOptText(typeof(EnumTFieldType), (int)EnumTFieldType.Tint) + ",";
sqlStr += item.TFName + "_radioid_name " + BLL.Util.GetEnumOptText(typeof(EnumTFieldType), (int)EnumTFieldType.Tnvarchar) + "(" + item.TFLen + "),";
}
else if (item.TFShowCode == "100005")//下拉
{
sqlStr += item.TFName + "_selectid " + BLL.Util.GetEnumOptText(typeof(EnumTFieldType), (int)EnumTFieldType.Tint) + ",";
sqlStr += item.TFName + "_selectid_name " + BLL.Util.GetEnumOptText(typeof(EnumTFieldType), (int)EnumTFieldType.Tnvarchar) + "(" + item.TFLen + "),";
}
else if (item.TFShowCode == "100014")//CRM CustID
{
sqlStr += item.TFName + "_crmcustid_name " + BLL.Util.GetEnumOptText(typeof(EnumTFieldType), (int)EnumTFieldType.Tnvarchar) + "(" + item.TFLen + "),";
}
else if (item.TFShowCode == "100015")//个人用户
{
sqlStr += item.TFName + "_crmcustid_name " + BLL.Util.GetEnumOptText(typeof(EnumTFieldType), (int)EnumTFieldType.Tnvarchar) + "(" + item.TFLen + "),";
}
else if (item.TFShowCode == "100016")
{
//下单车型
sqlStr += item.TFName + "_XDBrand " + BLL.Util.GetEnumOptText(typeof(EnumTFieldType), (int)EnumTFieldType.Tint) + ",";
sqlStr += item.TFName + "_XDBrand_Name " + BLL.Util.GetEnumOptText(typeof(EnumTFieldType), (int)EnumTFieldType.Tnvarchar) + "(" + item.TFLen + "),";
sqlStr += item.TFName + "_XDSerial " + BLL.Util.GetEnumOptText(typeof(EnumTFieldType), (int)EnumTFieldType.Tint) + ",";
sqlStr += item.TFName + "_XDSerial_Name " + BLL.Util.GetEnumOptText(typeof(EnumTFieldType), (int)EnumTFieldType.Tnvarchar) + "(" + item.TFLen + "),";
}
else if (item.TFShowCode == "100017")
{
//意向车型
sqlStr += item.TFName + "_YXBrand " + BLL.Util.GetEnumOptText(typeof(EnumTFieldType), (int)EnumTFieldType.Tint) + ",";
sqlStr += item.TFName + "_YXBrand_Name " + BLL.Util.GetEnumOptText(typeof(EnumTFieldType), (int)EnumTFieldType.Tnvarchar) + "(" + item.TFLen + "),";
sqlStr += item.TFName + "_YXSerial " + BLL.Util.GetEnumOptText(typeof(EnumTFieldType), (int)EnumTFieldType.Tint) + ",";
sqlStr += item.TFName + "_YXSerial_Name " + BLL.Util.GetEnumOptText(typeof(EnumTFieldType), (int)EnumTFieldType.Tnvarchar) + "(" + item.TFLen + "),";
}
else if (item.TFShowCode == "100018")
{
//出售车型
sqlStr += item.TFName + "_CSBrand " + BLL.Util.GetEnumOptText(typeof(EnumTFieldType), (int)EnumTFieldType.Tint) + ",";
sqlStr += item.TFName + "_CSBrand_Name " + BLL.Util.GetEnumOptText(typeof(EnumTFieldType), (int)EnumTFieldType.Tnvarchar) + "(" + item.TFLen + "),";
sqlStr += item.TFName + "_CSSerial " + BLL.Util.GetEnumOptText(typeof(EnumTFieldType), (int)EnumTFieldType.Tint) + ",";
sqlStr += item.TFName + "_CSSerial_Name " + BLL.Util.GetEnumOptText(typeof(EnumTFieldType), (int)EnumTFieldType.Tnvarchar) + "(" + item.TFLen + "),";
}
else if (item.TFShowCode == "100019")//推荐活动
{
sqlStr += item.TFName + "_Activity " + BLL.Util.GetEnumOptText(typeof(EnumTFieldType), (int)EnumTFieldType.Tnvarchar) + "(" + item.TFLen + "),";
sqlStr += item.TFName + "_Activity_Name " + BLL.Util.GetEnumOptText(typeof(EnumTFieldType), (int)EnumTFieldType.Tnvarchar) + "(" + item.TFLen + "),";
}
else if (item.TFShowCode == "100020")
{
sqlStr += item.TFName + " " + BLL.Util.GetEnumOptText(typeof(EnumTFieldType), (int)EnumTFieldType.Tint) + ",";
}
else
{
switch (item.TFShowCode)
{
case "100001":
case "100002":
case "100006":
case "100007":
sqlStr += item.TFName + " " + BLL.Util.GetEnumOptText(typeof(EnumTFieldType), (int)EnumTFieldType.Tnvarchar) + "(" + item.TFLen + "),";
break;
case "100008":
case "100010":
sqlStr += item.TFName + " " + BLL.Util.GetEnumOptText(typeof(EnumTFieldType), (int)EnumTFieldType.Tdatetime) + ",";
break;
}
}
}
sqlStr = sqlStr.Substring(0, sqlStr.Length - 1);
sqlStr += " )";
return(sqlStr);
}
/// <summary>
/// 生成字段
/// </summary>
/// <param name="tpageModel"></param>
/// <param name="ttableModel"></param>
/// <param name="fieldList"></param>
/// <param name="msg"></param>
private void GenerateFields(Entities.TPage tpageModel, Entities.TTable ttableModel, List <Entities.TField> fieldList, out string msg)
{
msg = "";
#region 除模板文件
try
{
string root = BLL.Util.GetUploadWebRoot() + BLL.Util.GetUploadProject(BLL.Util.ProjectTypePath.Template, "\\");
string fullname = root + tpageModel.TPName + "_" + tpageModel.RecID + ".xls";
if (File.Exists(fullname))
{
File.Delete(fullname);
}
}
catch (Exception ex)
{
msg = "删除模板文件出现异常：" + ex.Message;
}
#endregion
#region 创建表
string sqlStr = CreateSql(ttableModel, fieldList);
BLL.TTable.Instance.CreateTable(sqlStr, out msg);
#endregion
#region 生成Excel文件
if (msg != string.Empty)
{
return;
}
SaveExcelTemplate(tpageModel, out msg);
#endregion
#region 修改模板的状态和路径
BLL.Util.InsertUserLog("【生成模板结束】createFileIsOk为" + createFileIsOk + "，msg为" + msg);
if (!createFileIsOk)
{
BLL.Util.InsertUserLog("【生成模板失败】createFileIsOk为False，准备删除【" + ttableModel.TTName + "】模板表");
//生成模板失败，则删除刚才创建的表
string dropSql = " DROP Table " + ttableModel.TTName;
string dropMsg = string.Empty;
try
{
BLL.TTable.Instance.CreateTable(dropSql, out dropMsg);
//记录日志
BLL.Util.InsertUserLog("【生成模板失败】【删除】刚创建的表【" + ttableModel.TTName + "】【删除模板表成功】");
}
catch (Exception ex)
{
//记录日志
BLL.Util.InsertUserLog("【生成模板失败】【删除】刚创建的表【" + ttableModel.TTName + "】【删除模板表失败】，异常信息：" + ex.Message);
}
return;
}
//修改TPage表的IsUsed“是否可用”字段：修改成1：启用
//add lxw 13.12.16
tpageModel.IsUsed = 1;
tpageModel.Status = 1;
//更新路径到模板表
tpageModel.GenTempletPath = tpageModel.TPName + "_" + tpageModel.RecID + ".xls";
BLL.TPage.Instance.Update(tpageModel);
#endregion
}
/// <summary>
///
/// </summary>
/// <param name="ProjectModel">项目</param>
/// <param name="userId"></param>
/// <param name="dt">项目的DataSource数据</param>
/// <param name="msg"></param>
public void GenByOtherTaskDataTable(Entities.ProjectInfo ProjectModel, int userId, DataTable dt, out string msg, out string data)
{
msg = "";
data = "";
#region panduan
bool IsExistCrmCustID = false;
string ttName = "";
string tfName = "";
List <Entities.TField> fieldlist = BLL.TField.Instance.GetTFieldListByTTCode(ProjectModel.TTCode);
Entities.TField fielditem = fieldlist.Find(delegate(Entities.TField o) { return(o.TFShowCode == "100014"); });
if (fielditem != null)
{
IsExistCrmCustID = true;
tfName = fielditem.TFName;
Entities.TTable tableModel = BLL.TTable.Instance.GetTTableByTTCode(ProjectModel.TTCode);
if (tableModel != null)
{
ttName = tableModel.TTName;
}
}
#endregion
#region 根据数据关联生成任务
List <Entities.OtherTaskInfo> list = new List <Entities.OtherTaskInfo>();
int maxcount = BLL.ProjectTaskInfo.Instance.GetMaxRecID();//从OtherTaskInfo表中查找最大值，不是ProjectTaskInfo表中查找最大值
DataTable SourceOtherTaskDt = new DataTable();
SourceOtherTaskDt.Columns.Add("PTID");
SourceOtherTaskDt.Columns.Add("ProjectID", typeof(int));
SourceOtherTaskDt.Columns.Add("RelationTableID");
SourceOtherTaskDt.Columns.Add("RelationID");
SourceOtherTaskDt.Columns.Add("CreateTime", typeof(DateTime));
SourceOtherTaskDt.Columns.Add("CreateUserID", typeof(int));
SourceOtherTaskDt.Columns.Add("LastOptTime", typeof(DateTime));
SourceOtherTaskDt.Columns.Add("LastOptUserID", typeof(int));
SourceOtherTaskDt.Columns.Add("TaskStatus", typeof(int));
SourceOtherTaskDt.Columns.Add("Status", typeof(int));
SourceOtherTaskDt.Columns.Add("CustID");
int i = 0;
foreach (DataRow dr in dt.Rows)
{
DataRow newDr = SourceOtherTaskDt.NewRow();
string PixStr = "";
if (ProjectModel.Source == 4)
{
PixStr = "OTH";
}
newDr["PTID"] = PixStr + (++maxcount).ToString().PadLeft(7, '0');
//记录最小id和最大id
if (i == 0)
{
data += "{open:'@open',MinOtherTaskID:'" + newDr["PTID"] + "',";
}
if (i + 1 == dt.Rows.Count)
{
data += "MaxOtherTaskID:'" + newDr["PTID"] + "'}";
}
newDr["ProjectID"] = int.Parse(ProjectID);
newDr["RelationTableID"] = ProjectModel.TTCode;
newDr["RelationID"] = dr["RelationID"].ToString();
newDr["CreateTime"] = DateTime.Now;
newDr["CreateUserID"] = userId;
newDr["LastOptTime"] = DateTime.Now;
newDr["LastOptUserID"] = userId;
newDr["TaskStatus"] = (int)(OtheTaskStatus.Unallocated);
newDr["Status"] = 0;
if (IsExistCrmCustID)
{
newDr["CustID"] = GetCustID(ProjectModel, ttName, tfName, dr["RelationID"].ToString());
}
else
{
newDr["CustID"] = "";
}
SourceOtherTaskDt.Rows.Add(newDr);
i++;
}
string connectionstrings = ConfigurationUtil.GetAppSettingValue("ConnectionStrings_CC");
SqlConnection connection = new SqlConnection(connectionstrings);
connection.Open();
SqlTransaction tran = connection.BeginTransaction("SampleTransaction");
try
{
BLL.Util.BulkCopyToDB(SourceOtherTaskDt, connectionstrings, "OtherTaskInfo", tran, out msg);
BLL.ProjectLog.Instance.InsertProjectLog(ProjectModel.ProjectID, ProjectLogOper.L6_生成任务, "生成任务" + SourceOtherTaskDt.Rows.Count + "条", tran);
if (msg == "")
{
#region 修改项目状态
ProjectModel.Status = 1;
BLL.ProjectInfo.Instance.Update(tran, ProjectModel);
#endregion
#region 修改关联数据状态
BLL.ProjectDataSoure.Instance.UpdateStatusByProjectId(tran, "1", dt, (int)ProjectModel.ProjectID);
#endregion
tran.Commit();
}
}
catch (Exception ex)
{
if (tran.Connection != null)
{
tran.Rollback();
}
msg = ex.Message.ToString();
}
finally
{
connection.Close();
}
#endregion
}
public DataTable ExportTask(int ProjectID, out string msg, out string exportName, string taskcreatestart, string taskcreateend, string tasksubstart, string tasksubend)
{
msg = "";
exportName = "";
#region 定义变量
int totalCount = 0;
string TTName = "";
string TTCode = "";
string selectDataIDs = "";
Entities.TTable ttable = null;
Entities.ProjectInfo model = null;
List <Entities.TField> fieldList = null;
DataTable DbdataDt = null;//自定义数据表中的数据
#endregion
#region 获取项目、自定义数据表、字段信息
GetBaseInfo(ProjectID, out model, out TTCode, out ttable, out TTName, out fieldList, out msg);
if (msg != "")
{
return(null);
}
exportName = model.Name;
#endregion
#region 获取自定义数据表中的关联数据
GetTemptRelationData(ProjectID, TTName, TTCode, model, out selectDataIDs, out DbdataDt, taskcreatestart, taskcreateend, tasksubstart, tasksubend, out msg);
if (msg != "")
{
return(null);
}
#endregion
#region 是否成功,是否接通,接通后失败原因,未接通原因列的转换
if (DbdataDt.Columns.Contains("IsSuccess") && DbdataDt.Columns.Contains("IsEstablish") && DbdataDt.Columns.Contains("NotSuccessReason") && DbdataDt.Columns.Contains("NotEstablishReason"))
{
DataColumn colIsSuccess = new DataColumn("IsSucName", Type.GetType("System.String"));
DbdataDt.Columns.Add(colIsSuccess);
DataColumn colIsJT = new DataColumn("IsJTName", Type.GetType("System.String"));
DbdataDt.Columns.Add(colIsJT);
DataColumn colNotSuccessReason = new DataColumn("NotSuccessReasonName", Type.GetType("System.String"));
DbdataDt.Columns.Add(colNotSuccessReason);
DataColumn colNotExport = new DataColumn("NotExportName", Type.GetType("System.String"));
DbdataDt.Columns.Add(colNotExport);
for (int i = 0; i < DbdataDt.Rows.Count; i++)
{
DbdataDt.Rows[i]["IsSucName"] = BLL.Util.GetIsNotStatus(DbdataDt.Rows[i]["IsSuccess"].ToString());
DbdataDt.Rows[i]["IsJTName"] = BLL.Util.GetIsNotStatus(DbdataDt.Rows[i]["IsEstablish"].ToString());
string notSuc = DbdataDt.Rows[i]["NotSuccessReason"].ToString();
string notExport = DbdataDt.Rows[i]["NotEstablishReason"].ToString();
string notExportName = String.Empty;
if (!(String.IsNullOrEmpty(notExport) || notExport.Trim() == "-1" || notExport.Trim() == "-2" || DbdataDt.Rows[i]["IsEstablish"].ToString() == "1"))
{
//notExport=空，-1，-2 或者 接通了 是没有值的
notExportName = BLL.Util.GetEnumOptText(typeof(Entities.NotEstablishReason), Int32.Parse(notExport));
}
string notSuccessReasonName = String.Empty;
if (!(String.IsNullOrEmpty(notSuc) || notSuc.Trim() == "-1" || notSuc.Trim() == "-2"))
{
//notSuc=空，-1，-2 是没有值的
notSuccessReasonName = BLL.Util.GetEnumOptText(typeof(Entities.NotSuccessReason), Int32.Parse(notSuc));
}
DbdataDt.Rows[i]["NotSuccessReasonName"] = notSuccessReasonName;
DbdataDt.Rows[i]["NotExportName"] = notExportName;
}
}
#endregion
#region 处理列
DbdataDt = ProcessExportDataColoumns(TTName, TTCode, model, fieldList, DbdataDt);
#endregion
#region 处理数据
DbdataDt = ProcessData(DbdataDt);
#endregion
#region 关联crm客户信息
if (crmCustIDFieldName != string.Empty)
{
GetTogetherCustInfo(crmCustIDFieldName, ProjectID, DbdataDt);
}
#endregion
#region 除用户ID列
DbdataDt.Columns.Remove("LastOptUserID");
DbdataDt.Columns.Remove("TaskStatus");
#endregion
return(DbdataDt);
}
/// <summary>
/// 更新一条数据
/// </summary>
public int Update(SqlTransaction sqltran, Entities.TTable model)
{
return(Dal.TTable.Instance.Update(sqltran, model));
}
/// <summary>
/// 更新一条数据
/// </summary>
public int Update(Entities.TTable model)
{
return(Dal.TTable.Instance.Update(model));
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Insert(Entities.TTable model)
{
return(Dal.TTable.Instance.Insert(model));
}
