public ActionResult Login(mainClass real1)
{
using (DataContext db = new DataContext())
{
mainClass obj = db.mainClasss.FirstOrDefault(user => user.userId == real1.userId && user.password == real1.password);
if (obj == null)
{
return(View("vendorRejected"));
}
if (obj != null)
{
Session["userid"] = obj.userId;
if (obj.categoryId == categ.user)
{
return(RedirectToAction("userProductList"));
}
else if (obj.categoryId == categ.vendor)
{
return(RedirectToAction("vendorLoginCheck"));
}
else
{
return(RedirectToAction("admin"));
}
}
}
return(View(real1));
}
public ActionResult vendorLogin(mainClass obj)
{
if (obj.status == "reject")
{
return(View("reqPending"));
}
else
{
return(RedirectToAction("productList"));
}
}
public ActionResult vendorLogin1(mainClass obj)
{
if (obj.status == "reject")
{
return(View("reqPending"));
}
else
{
string id = Session["userId"].ToString();
var a1 = (from d in db.products where d.userId == id select d).ToList();
return(View(a1));
}
}
public ActionResult Register(mainClass usr)
{
if (ModelState.IsValid)
{
db.mainClasss.Add(usr);
db.SaveChanges();
return(RedirectToAction("submitted"));
}
else
{
ModelState.AddModelError("", "Some Error Occured!");
}
return(View(usr));
}
public ActionResult vendorLoginCheck(mainClass obj)
{
string d = Session["userId"].ToString();
var b = (from c in db.pquotations where c.vendorId == d select c).FirstOrDefault();
if (b == null || b.status == "reject")
{
return(RedirectToAction("vendorLogin", obj));
}
else
{
return(RedirectToAction("vendorLogin1", obj));
}
}
public ActionResult Login(mainClass real)
{
using (DataContext db = new DataContext())
{
mainClass obj = db.mainClasss.FirstOrDefault(user => user.userId == real.userId && user.password == real.password);
if (obj == null)
{
return(View("userRejected"));
}
if (obj != null)
{
Session["userid"] = obj.userId;
return(RedirectToAction("userExpenseList"));;
}
}
return(View(real));
}
protected void btnExport_Click(object sender, EventArgs e)
{
if (ddlDatabase.SelectedIndex == 0) return;
mainClass cls = new mainClass();
string sql = string.Format("SELECT name, [object_id] FROM {0}.sys.tables", ddlDatabase.SelectedItem);
DataSet ds = cls.GetDataSet(sql, "tables");
using (ExcelPackage package = new ExcelPackage())
{
foreach (DataRow dr in ds.Tables["tables"].Rows)
{
// add a new worksheet to the empty workbook
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(dr["name"].ToString());
worksheet.Cells["A1"].Value = "TableId";
worksheet.Cells["B1"].Value = dr["object_id"].ToString();
worksheet.Cells["A2"].Value = "TableName";
worksheet.Cells["B2"].Value = dr["name"].ToString();
worksheet.Cells["A3"].Value = "DefaultDisplay";
worksheet.Cells["G1"].Value = "FormType";
worksheet.Cells["H1"].Value = "Data";
worksheet.Cells["A1"].Style.Font.Bold = true;
worksheet.Cells["A2"].Style.Font.Bold = true;
worksheet.Cells["A3"].Style.Font.Bold = true;
worksheet.Cells["G1"].Style.Font.Bold = true;
////Add the headers
worksheet.Cells["A5"].Value = "SystemId";
worksheet.Cells["B5"].Value = "Field";
worksheet.Cells["C5"].Value = "PK/FK";
worksheet.Cells["D5"].Value = "Type";
worksheet.Cells["E5"].Value = "DefaultValue";
worksheet.Cells["F5"].Value = "ValueList";
worksheet.Cells["G5"].Value = "Null";
worksheet.Cells["H5"].Value = "RequireField";
worksheet.Cells["I5"].Value = "RequireField (Error Msg)";
worksheet.Cells["J5"].Value = "RegularExpression";
worksheet.Cells["K5"].Value = "RegularExpression (Error Msg)";
worksheet.Cells["L5"].Value = "UniqueValue";
worksheet.Cells["M5"].Value = "UniqueValue (Error Msg)";
worksheet.Cells["N5"].Value = "MinLength";
worksheet.Cells["O5"].Value = "MinLength (Error Msg)";
worksheet.Cells["P5"].Value = "MaxLength";
worksheet.Cells["Q5"].Value = "MaxLength (Error Msg)";
worksheet.Cells["R5"].Value = "RuleCriteria";
worksheet.Cells["S5"].Value = "RuleCriteria (Error Msg)";
worksheet.Cells["T5"].Value = "FileAttachment";
worksheet.Cells["U5"].Value = "AppearanceRules";
worksheet.Cells["V5"].Value = "CalculationRules";
worksheet.Cells["A5:V5"].Style.Font.Bold = true;
System.Drawing.Color colFromHex = System.Drawing.ColorTranslator.FromHtml("#B7DEE8");
worksheet.Cells["A5:V5"].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
worksheet.Cells["A5:V5"].Style.Fill.BackgroundColor.SetColor(colFromHex);
sql = string.Format(@"SELECT column_id, C.name,
--PF.CONSTRAINT_TYPE,
CASE PF.CONSTRAINT_TYPE WHEN 'PRIMARY KEY' THEN 'PK' WHEN 'FOREIGN KEY' THEN 'FK' ELSE '' END CONSTRAINT_TYPE,
C.user_type_id,
--T.name [type_name],
CASE
WHEN T.name IN ('nvarchar', 'varchar', 'char') AND C.max_length <> -1 THEN T.name + ' (' + CONVERT(varchar, C.max_length) + ')'
WHEN T.name IN ('nvarchar', 'varchar', 'char') AND C.max_length = -1 THEN T.name + ' (max)'
ELSE T.name
END [type_name],
C.max_length, C.is_nullable, is_identity
FROM {0}.sys.columns C
LEFT JOIN (SELECT KCU.TABLE_NAME, COLUMN_NAME, CONSTRAINT_TYPE
FROM {0}.INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
INNER JOIN {0}.INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU ON TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
WHERE KCU.TABLE_NAME = '{1}') AS PF ON C.name = PF.COLUMN_NAME
INNER JOIN {0}.sys.types T ON C.user_type_id = T.user_type_id
WHERE [object_id] = {2}",
ddlDatabase.SelectedItem, dr["name"].ToString(), dr["object_id"].ToString());
DataSet ds_columns = cls.GetDataSet(sql, "columns");
int row = 6;
foreach (DataRow dr_columns in ds_columns.Tables["columns"].Rows)
{
worksheet.Cells["A" + row.ToString()].Value = dr_columns["column_id"].ToString();
worksheet.Cells["B" + row.ToString()].Value = dr_columns["name"].ToString();
worksheet.Cells["C" + row.ToString()].Value = dr_columns["CONSTRAINT_TYPE"].ToString();
worksheet.Cells["D" + row.ToString()].Value = dr_columns["type_name"].ToString();
worksheet.Cells["G" + row.ToString()].Value = dr_columns["is_nullable"].ToString();
row++;
}
}
string fileName = string.Format("{0}_{1}.xlsx", ddlDatabase.SelectedItem, DateTime.Now.ToString("dd-MM-yyyy_HH-mm-ss"));
//Write it back to the client
Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", fileName));
Response.BinaryWrite(package.GetAsByteArray());
Response.End();
}
}
protected void btnExportEntity_Click(object sender, EventArgs e)
{
if (ddlDatabase.SelectedIndex == 0) return;
mainClass cls = new mainClass();
string sql = string.Format("SELECT name, [object_id] FROM {0}.sys.tables WHERE name NOT LIKE '%_AUDIT'", ddlDatabase.SelectedItem);
DataSet ds = cls.GetDataSet(sql, "tables");
//string folder = Server.MapPath(string.Format("assets/entity/{0}_{1}", ddlDatabase.SelectedItem, DateTime.Now.ToString("ddMMyyyy_HHmmss")));
string folder = Server.MapPath(string.Format("assets/entity/{0}", ddlDatabase.SelectedItem));
if (!Directory.Exists(folder))
{
Directory.CreateDirectory(folder);
}
foreach (DataRow dr in ds.Tables["tables"].Rows)
{
using (ExcelPackage package = new ExcelPackage())
{
// add a new worksheet to the empty workbook
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(dr["name"].ToString());
worksheet.Cells["A1"].Value = "TableId";
worksheet.Cells["B1"].Value = dr["object_id"].ToString();
worksheet.Cells["A2"].Value = "TableName";
worksheet.Cells["B2"].Value = dr["name"].ToString();
worksheet.Cells["A3"].Value = "DefaultDisplay";
worksheet.Cells["A4"].Value = "Context";
////Add the headers
worksheet.Cells["A5"].Value = "Field";
worksheet.Cells["B5"].Value = "PK/FK";
worksheet.Cells["C5"].Value = "Type";
worksheet.Cells["D5"].Value = "Length";
worksheet.Cells["E5"].Value = "DefaultValue";
worksheet.Cells["F5"].Value = "IsRequire";
worksheet.Cells["G5"].Value = "IsUnique";
worksheet.Cells["H5"].Value = "IsAI";
worksheet.Cells["I5"].Value = "OnDelete";
worksheet.Cells["A5:I5"].Style.Font.Bold = true;
System.Drawing.Color colFromHex = System.Drawing.ColorTranslator.FromHtml("#B7DEE8");
worksheet.Cells["A5:I5"].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
worksheet.Cells["A5:I5"].Style.Fill.BackgroundColor.SetColor(colFromHex);
sql = string.Format(@"SELECT column_id, C.name,
--PF.CONSTRAINT_TYPE,
CASE PF.CONSTRAINT_TYPE WHEN 'PRIMARY KEY' THEN 'PK' WHEN 'FOREIGN KEY' THEN 'FK' ELSE '' END CONSTRAINT_TYPE,
CONSTRAINT_NAME,
C.user_type_id, T.name [type_name], C.max_length, C.is_nullable, is_identity
FROM {0}.sys.columns C
LEFT JOIN (SELECT KCU.TABLE_NAME, COLUMN_NAME, CONSTRAINT_TYPE, KCU.CONSTRAINT_NAME
FROM {0}.INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
INNER JOIN {0}.INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU ON TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
WHERE KCU.TABLE_NAME = '{1}') AS PF ON C.name = PF.COLUMN_NAME
INNER JOIN {0}.sys.types T ON C.user_type_id = T.user_type_id
WHERE [object_id] = {2} AND C.name NOT LIKE '%_AUDIT'",
ddlDatabase.SelectedItem, dr["name"].ToString(), dr["object_id"].ToString());
DataSet ds_columns = cls.GetDataSet(sql, "columns");
int row = 6;
foreach (DataRow dr_columns in ds_columns.Tables["columns"].Rows)
{
worksheet.Cells["A" + row.ToString()].Value = dr_columns["name"].ToString();
//worksheet.Cells["B" + row.ToString()].Value = dr_columns["CONSTRAINT_TYPE"].ToString();
if (dr_columns["CONSTRAINT_TYPE"].ToString() == "FK")
{
worksheet.Cells["B" + row.ToString()].Value = string.Format("{0}[{1}.{2}]",
dr_columns["CONSTRAINT_TYPE"],
dr_columns["CONSTRAINT_NAME"].ToString().Replace("FK_", "").Replace(dr["name"].ToString() + "_", ""),
dr_columns["name"].ToString());
}
else
{
worksheet.Cells["B" + row.ToString()].Value = dr_columns["CONSTRAINT_TYPE"].ToString();
}
switch (dr_columns["type_name"].ToString())
{
case "varchar":
case "nvarchar":
case "char":
case "nchar":
worksheet.Cells["C" + row.ToString()].Value = "string";
break;
case "numeric":
case "bigint":
worksheet.Cells["C" + row.ToString()].Value = "int";
break;
case "datetime":
worksheet.Cells["C" + row.ToString()].Value = "DateTime";
break;
case "bit":
worksheet.Cells["C" + row.ToString()].Value = "bool";
break;
default:
worksheet.Cells["C" + row.ToString()].Value = dr_columns["type_name"].ToString();
break;
}
if (dr_columns["max_length"].ToString() != "-1")
{
worksheet.Cells["D" + row.ToString()].Value = dr_columns["max_length"].ToString();
}
if (dr_columns["is_nullable"].ToString() == "True")
{
worksheet.Cells["F" + row.ToString()].Value = "false";
}
else
{
worksheet.Cells["F" + row.ToString()].Value = "true";
}
if (dr_columns["is_identity"].ToString() == "True")
{
worksheet.Cells["H" + row.ToString()].Value = "true";
}
else
{
worksheet.Cells["H" + row.ToString()].Value = "false";
}
row++;
}
row--;
worksheet.Cells["A1:I" + row.ToString()].Style.Border.Top.Style = ExcelBorderStyle.Thin;
worksheet.Cells["A1:I" + row.ToString()].Style.Border.Right.Style = ExcelBorderStyle.Thin;
worksheet.Cells["A1:I" + row.ToString()].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
worksheet.Cells["A1:I" + row.ToString()].Style.Border.Left.Style = ExcelBorderStyle.Thin;
worksheet.Cells["A1:I" + row.ToString()].Style.Font.Name = "Tahoma";
worksheet.Cells["A1:I" + row.ToString()].Style.Font.Size = 10;
string fileName = string.Format("{0}.xlsx", dr["name"].ToString());
FileInfo fi = new FileInfo(folder + "/" + fileName);
package.SaveAs(fi);
//Write it back to the client
//Response.Clear();
//Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
//Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", fileName));
//Response.BinaryWrite(package.GetAsByteArray());
//Response.End();
}
}
#region zip
string path = folder + "_ENTITY.zip";
ZipFolder(folder, path);
Directory.Delete(folder, true);
using (FileStream fs = File.OpenRead(path))
{
int length = (int)fs.Length;
byte[] buffer;
using (BinaryReader br = new BinaryReader(fs))
{
buffer = br.ReadBytes(length);
}
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", String.Format("attachment;filename={0}", Path.GetFileName(path)));
Response.ContentType = "application/" + Path.GetExtension(path).Substring(1);
Response.BinaryWrite(buffer);
Response.End();
}
#endregion
}
protected void btnExportUI_Click(object sender, EventArgs e)
{
if (ddlDatabase.SelectedIndex == 0) return;
mainClass cls = new mainClass();
string sql = string.Format("SELECT name, [object_id] FROM {0}.sys.tables", ddlDatabase.SelectedItem);
DataSet ds = cls.GetDataSet(sql, "tables");
//string folder = Server.MapPath(string.Format("assets/schema2excel/{0}_{1}", ddlDatabase.SelectedItem, DateTime.Now.ToString("ddMMyyyy_HHmmss")));
string folder = Server.MapPath(string.Format("assets/schema2excel/{0}", ddlDatabase.SelectedItem));
if (!Directory.Exists(folder))
{
Directory.CreateDirectory(folder);
}
foreach (DataRow dr in ds.Tables["tables"].Rows)
{
sql = string.Format(@"SELECT column_id, C.name,
--PF.CONSTRAINT_TYPE,
CASE PF.CONSTRAINT_TYPE WHEN 'PRIMARY KEY' THEN 'PK' WHEN 'FOREIGN KEY' THEN 'FK' ELSE '' END CONSTRAINT_TYPE,
C.user_type_id, CONSTRAINT_NAME,
--T.name [type_name],
CASE
WHEN T.name IN ('nvarchar', 'varchar', 'char') AND C.max_length <> -1 THEN T.name + ' (' + CONVERT(varchar, C.max_length) + ')'
WHEN T.name IN ('nvarchar', 'varchar', 'char') AND C.max_length = -1 THEN T.name + ' (max)'
ELSE T.name
END [type_name],
C.max_length, C.is_nullable, is_identity
FROM {0}.sys.columns C
LEFT JOIN (SELECT KCU.TABLE_NAME, COLUMN_NAME, CONSTRAINT_TYPE,KCU.CONSTRAINT_NAME
FROM {0}.INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
INNER JOIN {0}.INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU ON TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
WHERE KCU.TABLE_NAME = '{1}') AS PF ON C.name = PF.COLUMN_NAME
INNER JOIN {0}.sys.types T ON C.user_type_id = T.user_type_id
WHERE [object_id] = {2}",
ddlDatabase.SelectedItem, dr["name"].ToString(), dr["object_id"].ToString());
DataSet ds_columns = cls.GetDataSet(sql, "columns");
int row = 0;
string fieldformat = "";
//string TableName = "";
bool found = false;
string viewName = dr["name"].ToString() + "_DetailView";
using (ExcelPackage package = new ExcelPackage())
{
#region MasterLayout
// add a new worksheet to the empty workbook
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("MasterLayout");
worksheet.Cells["A1"].Value = "SystemName";
worksheet.Cells["A2"].Value = "ThemeName";
worksheet.Cells["A3"].Value = "DefaultLayout";
worksheet.Cells["A4"].Value = "DefaultView";
worksheet.Cells["A5"].Value = "Banner";
worksheet.Cells["A6"].Value = "Authentication";
worksheet.Cells["A7"].Value = "Header";
worksheet.Cells["A8"].Value = "Footer";
worksheet.Cells["A9"].Value = "Root Service URL";
////Add the headers
worksheet.Cells["A10"].Value = "ElementType";
worksheet.Cells["B10"].Value = "ElementId";
worksheet.Cells["C10"].Value = "LV1";
worksheet.Cells["D10"].Value = "LV2";
worksheet.Cells["E10"].Value = "LV3";
worksheet.Cells["F10"].Value = "LV4";
worksheet.Cells["G10"].Value = "LV5";
worksheet.Cells["H10"].Value = "Caption";
worksheet.Cells["I10"].Value = "Url";
worksheet.Cells["J10"].Value = "AppearanceRules";
System.Drawing.Color colFromHex = System.Drawing.ColorTranslator.FromHtml("#B7DEE8");
worksheet.Cells["A10:J10"].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
worksheet.Cells["A10:J10"].Style.Fill.BackgroundColor.SetColor(colFromHex);
worksheet.Cells["A1:J11"].Style.Border.Top.Style = ExcelBorderStyle.Thin;
worksheet.Cells["A1:J11"].Style.Border.Right.Style = ExcelBorderStyle.Thin;
worksheet.Cells["A1:J11"].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
worksheet.Cells["A1:J11"].Style.Border.Left.Style = ExcelBorderStyle.Thin;
worksheet.Cells["A1:J11"].Style.Font.Name = "Tahoma";
worksheet.Cells["A1:J11"].Style.Font.Size = 10;
#endregion
#region ExcelConfiguration
string folderXML = ConfigurationManager.AppSettings["HomePath"] + @"\Generator\XML";
ExcelConfiguration conf = ExcelConfiguration.GetExcelConfigurationXml(folderXML);
#endregion
#region DetailViewLayout
worksheet = package.Workbook.Worksheets.Add("DetailViewLayout");
for (int i = 0; i < conf.ViewDetails.Count; i++)
{
// TableName = conf.ViewDetails[i].ViewName;
if (viewName.ToLower().Equals(conf.ViewDetails[i].ViewName.ToLower()))
{
found = true;
worksheet = CreateDetailView_XML(worksheet, row, fieldformat, colFromHex, i,conf);
i = conf.ViewDetails.Count;
}
//worksheet = CreateDetailView(worksheet, dr, row, fieldformat, colFromHex, ds_columns);
}
if (!found)
{
worksheet = CreateDetailView_Table(worksheet, dr, row, fieldformat, colFromHex, ds_columns);
}
#endregion
#region ListViewLayout
worksheet = package.Workbook.Worksheets.Add("ListViewLayout");
for (int i = 0; i < conf.ViewList.Count; i++)
{
// TableName = conf.ViewDetails[i].ViewName;
if (viewName.ToLower().Equals(conf.ViewDetails[i].ViewName.ToLower()))
{
found = true;
worksheet = CreateListView_XML(worksheet, row, fieldformat, colFromHex, i, conf);
i = conf.ViewDetails.Count;
}
//worksheet = CreateDetailView(worksheet, dr, row, fieldformat, colFromHex, ds_columns);
}
if (!found)
{
worksheet = CreateListView_Table(worksheet, dr, row, fieldformat, colFromHex, ds_columns);
}
#endregion
#region LookupListViewLayout
worksheet = package.Workbook.Worksheets.Add("LookupListViewLayout");
worksheet.Cells["A1"].Value = "TableName";
worksheet.Cells["B1"].Value = dr["name"].ToString();
worksheet.Cells["A2"].Value = "ViewType";
worksheet.Cells["B2"].Value = "LookupListView";
worksheet.Cells["A3"].Value = "ViewName";
worksheet.Cells["B3"].Value = dr["name"].ToString()+"_LookupListView";
worksheet.Cells["A4"].Value = "Title";
//worksheet.Cells["A5"].Value = "Root Service URL";
worksheet.Cells["B5"].Value = "output/service";
worksheet.Cells["A5"].Value = "Order By";
worksheet.Cells["A6"].Value = "Filter";
worksheet.Cells["B6"].Value = "TRUE";
worksheet.Cells["A7"].Value = "PageSize";
worksheet.Cells["B7"].Value = "20";
//
worksheet.Cells["A8"].Value = "ReturnField";
worksheet.Cells["A9"].Value = "FilterView";
worksheet.Cells["A10"].Value = "InputParameters";
worksheet.Cells["A11"].Value = "FilterParameters";
worksheet.Cells["A12"].Value = "MasterPage";
worksheet.Cells["A13"].Value = "SqlSelect";
worksheet.Cells["E13"].Value = "SqlSelectParameter";
//
////Add the headers
worksheet.Cells["A14"].Value = "Caption";
worksheet.Cells["B14"].Value = "Column";
worksheet.Cells["C14"].Value = "Format";
worksheet.Cells["D14"].Value = "TextAlignment";
worksheet.Cells["E14"].Value = "IsHideResize";
worksheet.Cells["F14"].Value = "CustomStyle";
colFromHex = System.Drawing.ColorTranslator.FromHtml("#FFFF00");
worksheet.Cells["A14:F14"].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
worksheet.Cells["A14:F14"].Style.Fill.BackgroundColor.SetColor(colFromHex);
row = 15;
foreach (DataRow dr_columns in ds_columns.Tables["columns"].Rows)
{
fieldformat = "";
if (row == 15)
{
if (dr_columns["CONSTRAINT_TYPE"].ToString().ToLower() == "pk")
worksheet.Cells["B10"].Value = dr_columns["name"].ToString();
}
string[] typename = dr_columns["type_name"].ToString().Split(' ');
if (typename[0].ToString().ToString() == "date" || typename[0].ToString().ToString() == "datetime")
{
fieldformat = "d MMM yyyy";
}
else if (typename[0].ToString().ToString() == "int" || typename[0].ToString().ToString() == "decimal"
|| typename[0].ToString().ToString() == "numeric" || typename[0].ToString().ToString() == "money")
{
fieldformat = "n2";
}
worksheet.Cells["A" + row.ToString()].Value = dr_columns["name"].ToString();
worksheet.Cells["B" + row.ToString()].Value = dr_columns["name"].ToString();
worksheet.Cells["C" + row.ToString()].Value = fieldformat;
row++;
}
row--;
worksheet.Cells["A1:F" + row.ToString()].Style.Border.Top.Style = ExcelBorderStyle.Thin;
worksheet.Cells["A1:F" + row.ToString()].Style.Border.Right.Style = ExcelBorderStyle.Thin;
worksheet.Cells["A1:F" + row.ToString()].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
worksheet.Cells["A1:F" + row.ToString()].Style.Border.Left.Style = ExcelBorderStyle.Thin;
worksheet.Cells["A1:F" + row.ToString()].Style.Font.Name = "Tahoma";
worksheet.Cells["A1:F" + row.ToString()].Style.Font.Size = 10;
#endregion
#region DataSpect
worksheet = package.Workbook.Worksheets.Add("DATA_SPEC");
worksheet.Cells["A1"].Value = "TableId";
worksheet.Cells["B1"].Value = dr["object_id"].ToString();
worksheet.Cells["A2"].Value = "TableName";
worksheet.Cells["B2"].Value = dr["name"].ToString();
worksheet.Cells["A3"].Value = "DefaultDisplay";
worksheet.Cells["H1"].Value = "FormType";
worksheet.Cells["I1"].Value = "Data";
//worksheet.Cells["A1"].Style.Font.Bold = true;
//worksheet.Cells["A2"].Style.Font.Bold = true;
//worksheet.Cells["A3"].Style.Font.Bold = true;
//worksheet.Cells["G1"].Style.Font.Bold = true;
////Add the headers
worksheet.Cells["A5"].Value = "SystemId";
worksheet.Cells["B5"].Value = "Field";
worksheet.Cells["C5"].Value = "PK/FK";
worksheet.Cells["D5"].Value = "FieldForeignKeyTableName";
worksheet.Cells["E5"].Value = "Type";
worksheet.Cells["F5"].Value = "DefaultValue";
worksheet.Cells["G5"].Value = "ValueList";
worksheet.Cells["H5"].Value = "Null";
worksheet.Cells["I5"].Value = "RequireField";
worksheet.Cells["J5"].Value = "RequireField (Error Msg)";
worksheet.Cells["K5"].Value = "RegularExpression";
worksheet.Cells["L5"].Value = "RegularExpression (Error Msg)";
worksheet.Cells["M5"].Value = "UniqueValue";
worksheet.Cells["N5"].Value = "UniqueValue (Error Msg)";
worksheet.Cells["O5"].Value = "MinLength";
worksheet.Cells["P5"].Value = "MinLength (Error Msg)";
worksheet.Cells["Q5"].Value = "MaxLength";
worksheet.Cells["R5"].Value = "MaxLength (Error Msg)";
worksheet.Cells["S5"].Value = "RuleCriteria";
worksheet.Cells["T5"].Value = "RuleCriteria (Error Msg)";
worksheet.Cells["U5"].Value = "FileAttachment";
worksheet.Cells["V5"].Value = "AppearanceRules";
worksheet.Cells["W5"].Value = "AppearanceDisplay";
worksheet.Cells["X5"].Value = "CalculationRules";
colFromHex = System.Drawing.ColorTranslator.FromHtml("#B7DEE8");
worksheet.Cells["A5:X5"].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
worksheet.Cells["A5:X5"].Style.Fill.BackgroundColor.SetColor(colFromHex);
row = 6;
foreach (DataRow dr_columns in ds_columns.Tables["columns"].Rows)
{
worksheet.Cells["A" + row.ToString()].Value = dr_columns["column_id"].ToString();
worksheet.Cells["B" + row.ToString()].Value = dr_columns["name"].ToString();
worksheet.Cells["C" + row.ToString()].Value = dr_columns["CONSTRAINT_TYPE"].ToString();
worksheet.Cells["E" + row.ToString()].Value = dr_columns["type_name"].ToString();
worksheet.Cells["G" + row.ToString()].Value = dr_columns["is_nullable"].ToString();
if (dr_columns["CONSTRAINT_TYPE"].ToString() == "FK")
{
worksheet.Cells["D" + row.ToString()].Value = dr_columns["CONSTRAINT_NAME"].ToString();
}
row++;
}
row--;
worksheet.Cells["A1:X" + row.ToString()].Style.Border.Top.Style = ExcelBorderStyle.Thin;
worksheet.Cells["A1:X" + row.ToString()].Style.Border.Right.Style = ExcelBorderStyle.Thin;
worksheet.Cells["A1:X" + row.ToString()].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
worksheet.Cells["A1:X" + row.ToString()].Style.Border.Left.Style = ExcelBorderStyle.Thin;
worksheet.Cells["A1:X" + row.ToString()].Style.Font.Name = "Tahoma";
worksheet.Cells["A1:X" + row.ToString()].Style.Font.Size = 10;
#endregion
//string fileName = string.Format("UITEMP_{0}_{1}.xlsx", dr["name"].ToString(), DateTime.Now.ToString("ddMMyyyy_HHmmss"));
string fileName = string.Format("{0}.xlsx", dr["name"].ToString(), DateTime.Now.ToString("ddMMyyyy_HHmmss"));
FileInfo fi = new FileInfo(folder + "/" + fileName);
package.SaveAs(fi);
}
}
#region zip
string path = folder + "_UI.zip";
ZipFolder(folder, path);
Directory.Delete(folder, true);
using (FileStream fs = File.OpenRead(path))
{
int length = (int)fs.Length;
byte[] buffer;
using (BinaryReader br = new BinaryReader(fs))
{
buffer = br.ReadBytes(length);
}
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", String.Format("attachment;filename={0}", Path.GetFileName(path)));
Response.ContentType = "application/" + Path.GetExtension(path).Substring(1);
Response.BinaryWrite(buffer);
Response.End();
}
#endregion
}
public void MainTest()
{
mainClass obj = new mainClass();
}
public void clikTest()
{
mainClass obj = new mainClass();
}
//System.Globalization.CultureInfo ct = new System.Globalization.CultureInfo("th-TH");
protected void Page_Load(object sender, EventArgs e)
{
id = Request.QueryString["id"];
sDate = Request.QueryString["date"];
if (string.IsNullOrEmpty(id)) id = "0";
if (!IsPostBack)
{
mainClass cls = new mainClass();
DateTime dateShow = DateTime.Now;
if (id != "0" && sDate == null)
{
System.Data.DataTable dtMeeting = cls.GetDataTable(@"SELECT MEETING_ID, MEETING_TITLE, ROOM_ID, ROOM_NAME, PERIOD_DATE_EN, PERIOD_START, PERIOD_STOP
FROM [dbo].[getMeetingAll]()
WHERE MEETING_ID = " + id + " ");
if (dtMeeting.Rows[0]["PERIOD_DATE_EN"].ToString() != "")
dateShow = (DateTime)dtMeeting.Rows[0]["PERIOD_DATE_EN"];
sDate = dateShow.ToString("yyyy-MM-dd", ct);
Response.Redirect("dialogRoomSelection.aspx?id=" + id + "&date=" + sDate);
}
else if (!string.IsNullOrEmpty(sDate))
{
try
{
dateShow = DateTime.Parse(sDate, ct);
if (dateShow.Year > 2500)
{
dateShow.AddYears(-543);
}
}
catch
{
dateShow = DateTime.Now;
}
//dateShow = DateTime.Parse(sDate, ct);
}
sDate = dateShow.ToString("yyyy-MM-dd", ct);
sNewDate = dateShow.ToString("yyyy", ct) + ", " + (dateShow.Month - 1) + ", " + dateShow.Day;
//sDateShow = dateShow.ToString("d MMMM yyyy", ct);
//sDateShow = dateShow.DayOfWeek.ToString() + " " + dateShow.ToString("d MMMM yyyy", ct);
sDateShow = DayEN2TH(dateShow.DayOfWeek.ToString()) + " " + DateEN2TH(dateShow.Day, dateShow.Month, dateShow.Year);
string sRoomTemplate = "{ key: %ID%, label: \"%LOCATION%\" },";
System.Data.DataTable dt = cls.GetDataTable(@"SELECT [ROOM_ID]
,[ROOM_CODE]
,[ROOM_NAME]
,[ROOM_CAPACITY]
FROM [OPM_RMS].[dbo].[RMSM_MDM_ROOM_INFO] where RECORD_STATUS='A'");
for (int i = 0; i < dt.Rows.Count; ++i)
{
sRoom += sRoomTemplate.Replace("%ID%", dt.Rows[i]["ROOM_ID"].ToString()).Replace("%LOCATION%", dt.Rows[i]["ROOM_NAME"].ToString());
sRoomScript += "if($(this).html()=='" + dt.Rows[i]["ROOM_NAME"].ToString() +
"'){$(this).html(\"<span class='room room" + (i+1).ToString() +
"' style='float: left; margin-left: 5px'>" + dt.Rows[i]["ROOM_NAME"].ToString() +
"</span><span style='display: block; float: left; margin-left: 5px; '>ความจุ " +
dt.Rows[i]["ROOM_CAPACITY"].ToString() + " คน</span>\");}";
}
if (sRoom != "") sRoom = sRoom.Substring(0, sRoom.Length - 1);
}
}
