/// <summary>
/// 菜单列表项
/// </summary>
/// <param name="menutb">level=0的一级菜单</param>
/// <param name="subMenutb">level=1的二级菜单</param>
/// <param name="parentMenutb">isparent=1的具有二级菜单的一级菜单</param>
public static void GetMenuItemBLL(out DataTable menutb,out DataTable subMenutb,out DataTable parentMenutb)
{
DataTable dt = Front_DataCollectorDAL.GetMenuItemDAL();
menutb = dt.Clone();//存放level=0的1层菜单
subMenutb = dt.Clone();//存放level=1的子菜单
parentMenutb = dt.Clone();//存放isparent=1的1层菜单
if (dt!=null && dt.Rows.Count>0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
if (dr["menu_level"].ToString()=="0")
{
menutb.ImportRow(dr);
if (dr["menu_isparent"].ToString()=="1")
{
parentMenutb.ImportRow(dr);
}
}
else if(dr["menu_level"].ToString()=="1")
{
subMenutb.ImportRow(dr);
}
}
}
}
public DataSet GetModelHtmlValue(string ChId, string Id)
{
int MyChId = int.Parse(ChId);
ChannelModel = MyChId <= 0 ? null : ChannelBll.GetChannel(MyChId);
MInfoModel = BInfoModel.GetModel(ChannelModel.ModelType);
DataTable dt=new DataTable();
DataRow dr = BInfoOper.GetInfo(MInfoModel.TableName, int.Parse(Id));
dt = dr.Table.Copy();
dt.Clear();
dt.ImportRow(dr);
dt.TableName = "DrInfo";
DataSet ds = new DataSet();
try
{
ds.Tables.Add(BModelField.GetList(ChannelModel.ModelType).Copy());
ds.Tables.Add(dt);
}
catch(Exception ex)
{
Response.Write(ex);
}
return ds;
}
public static void ImportRowToDataTable(ref DataTable dt, DataRow[] rows)
{
foreach (DataRow r in rows)
{
dt.ImportRow(r);
}
}
public FlawForm(DataRow flaw, Dictionary<string, NMap.Model.Unit> currentUnitList)
{
InitializeComponent();
// Initialize datatable struct
_flawData = new DataTable();
_flawData.Columns.Add("FlawID", typeof(string));
_flawData.Columns.Add("FlawType", typeof(int));
_flawData.Columns.Add("FlawClass", typeof(string));
_flawData.Columns.Add("Area", typeof(string));
_flawData.Columns.Add("CD", typeof(decimal));
_flawData.Columns.Add("MD", typeof(decimal));
_flawData.Columns.Add("Width", typeof(decimal));
_flawData.Columns.Add("Length", typeof(decimal));
_flawData.ImportRow(flaw);
_drFlaw = _flawData.Rows[0];
_currentCdConversion = currentUnitList["FlawMapCD"].Conversion;
_currentMdConversion = currentUnitList["FlawMapMD"].Conversion;
_currentUnitList = currentUnitList;
_drFlaw["CD"] = Convert.ToDecimal(_drFlaw["CD"]) / _currentCdConversion;
_drFlaw["MD"] = Convert.ToDecimal(_drFlaw["MD"]) / _currentMdConversion;
DataHelper dh = new DataHelper();
_imageList = dh.GetFlawImageFromDb(_drFlaw);
}
public DataTable ReadUserInfo(string str)
{
if (bll.GetCookie() == null)
{
return null;
}
else
{
int UserId = bll.GetCookie().UserID;
DataTable dt = new DataTable();
try
{
dr = bll.GetUserAllInfo(UserId);
dt = dr.Table.Copy();
dt.Clear();
dt.ImportRow(dr);
}
catch (Exception ex)
{
Response.Write(ex);
}
return dt;
}
}
protected void Page_Load(object sender, EventArgs e)
{
#region Check Login
CheckStateAdminSession();
#endregion Check Login
#region Initialize Values and Form Execution
DataTable table = (DataTable)Session["StudentList"];
studentList = table.Clone();
int startIndex = Convert.ToInt32(CareerCruisingWeb.CCLib.Common.Strings.GetFormString("Start"));
int endIndex = Convert.ToInt32(CareerCruisingWeb.CCLib.Common.Strings.GetFormString("End"));
// select occupations from above list that correspond to cluster code passed
for (int counter = startIndex; counter <= endIndex; counter++)
{
studentList.ImportRow(table.Rows[counter]);
}
if (studentList.Rows.Count > 0)
rptMassPrintPLPSignOff.DataBind();
#endregion Initialize Values and Form Execution
#region Properties For The State Base Class
HasTopHeader = false;
#endregion Properties For The State Base Class
}
private void FenYe(System.Data.DataTable dataTable)
{
// if (oo != -1)
// {
// num = oo;
// }
System.Data.DataTable dt = dataTable.Clone(); //复制原数据的结构
rows = dataTable.Rows.Count; //总行数
sum = rows % ii == 0 ? rows / ii : (rows / ii) + 1; //总页数=总行数/每页的行数(如果除不尽则+1)
if (sum == 0)
{
sum++;
}
int ks = (num * ii) - ii; //开始行数（当前页数*每页的行数）-每页的行数
int js = num * ii; //结束行数
if (num == sum)
{
js = ks + (rows - ((num - 1) * ii)); //当最后一页时，结束的行数 = 开始行数 + (总行数 - （（总页数-1） * 每页的行数）)
}
//实现分页
if (rows != 0) //必须要有一条记录
{
for (int i = ks; i < js; i++)
{
dt.ImportRow(dataTable.Rows[i]); //复制行
}
}
label6.Text = num.ToString() + "/" + sum.ToString();
}
public static void Upload(this System.Data.DataTable dt, string tableName)
{
string query = "SELECT * from " + tableName;
using (SqlConnection conn = new SqlConnection(SqlConnStr))
{
using (SqlDataAdapter oda = new SqlDataAdapter())
{
using (SqlCommandBuilder bu = new SqlCommandBuilder())
{
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn))
{
oda.SelectCommand = new SqlCommand(query, conn);
bulkcopy.DestinationTableName = "dbo." + tableName;
DataTable dtsql = new DataTable();
oda.Fill(dtsql);
List<DataRow> lst_temp = dt.AsEnumerable().ToList();
foreach (DataRow row in lst_temp)
{
dtsql.ImportRow(row);
}
conn.Open();
bulkcopy.WriteToServer(dtsql);
conn.Close();
}
}
}
}
}
public static System.Data.DataTable GetDataTableDynamic(int AtSegment)
{
InitialPage();
if (AtSegment < _totalpages)
{
Segment = AtSegment * _itemsonpage;
Offset = _itemsonpage;
}
else if (AtSegment == _totalpages)
{
Segment = (_parrity)? AtSegment * _itemsonpage : (AtSegment - 1) * _itemsonpage;
Offset = _itemsonlastpage;
}
else
{
Segment = 0;
Offset = _itemsonpage;
}
System.Data.DataTable DtableDynamic = tablestatic.Clone();
for (int i = Segment; i < Segment + Offset; i++)
{
if (i == _totalitems)
{
break;
}
DtableDynamic.ImportRow(tablestatic.Rows[i]);
}
_currentpages = AtSegment;
return(DtableDynamic);
}
private static DataTable ReorderDeptRow(DataTable dtReturn, int deptID, DataTable dtDept, int align)
{
//获取部门的子部门
DataRow[] drSubDept = dtDept.Select("SuperDeptID = " + deptID);
//遍历所有子部门
for (int i = 0; i < drSubDept.Length; i++)
{
//通过对齐位置，来控制该部门前空格数
string alignPosition = string.Empty;
for (int j = 0; j < align; j++)
{
alignPosition += " ";
}
//获取子部门数据
DataRow drSubDeptTemp = (DataRow)drSubDept[i];
//获取子部门ID
int subDeptID = (int)drSubDeptTemp["ID"];
drSubDeptTemp["DeptName"] = alignPosition + drSubDeptTemp["DeptName"].ToString();
//导入子部门
dtReturn.ImportRow(drSubDeptTemp);
//生成子部门的子部门信息
dtReturn = ReorderDeptRow(dtReturn, subDeptID, dtDept, align + 1);
}
return dtReturn;
}
/// <summary>
/// Obtem todos atendimentos a partir do Ultimo Registro.
/// </summary>
/// <param name="m_iQtdeRegistros">Quantidade de Registro</param>
/// <returns></returns>
public void ProcessarAtendimentosHIS(Int32 m_iQtdeRegistros)
{
string m_sUltimoRegistro;
DataSet DsDados = new DataSet();
ServiceHBD.WSLerAtendimentosSoapClient DadosOrigens = new WSLerAtendimentosSoapClient();
//Obtem o Ultimo Registro
m_sUltimoRegistro = DadosOrigens.RetornarUltimoRegistro(mUnidade);
//Obtem as Informações
DsDados = DadosOrigens.RetornarAtendimentos(m_sUltimoRegistro, m_iQtdeRegistros);
//Gravar no DWSATELITE
for (int i = 0; i < DsDados.Tables[0].Rows.Count; i++)
{
DataRow Dr0 = DsDados.Tables[0].Rows[i];
DataSet Ds = new DataSet();
DataTable Dt = new DataTable(DsDados.Tables[0].TableName);
Dt = DsDados.Tables[0].Clone();
Dt.ImportRow(Dr0);
Ds.Tables.Add(Dt);
DadosOrigens.GravarAtendimento(mUnidade, Ds);
//m_oDataSet = Ds;
//Salvar();
}
}
private void 打开ToolStripMenuItem_Click(object sender, EventArgs e)
{
openFileDialog1.Filter = "CSV文件|*.CSV";
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
string fileName = openFileDialog1.FileName;
System.Data.DataTable newDataTable;
newDataTable = csvHelper.CSVToDataTable(fileName);
m_GradeTable.Clear();
foreach (DataRow row in newDataTable.Rows)
{
m_GradeTable.ImportRow(row);
}
toolStripStatusLabel1.Text = (dataGridView1.RowCount - 1).ToString() + "行";
MessageBox.Show("成功显示CSV数据！");
}
}
private static void AppendData(DataTable source, ref DataTable target)
{
if (target == null)
target = source.Clone();
foreach (DataRow row in source.Rows)
target.ImportRow(row);
}
/// <summary>
/// 处理DataTable,把DataTable里面的true 或 false 换成文字
/// </summary>
/// <param name="dt">要处理的DataTable</param>
/// <returns>处理后的DataTable</returns>
private System.Data.DataTable ProcessDataTable(System.Data.DataTable dt)
{
try
{
if (dt != null && dt.Rows.Count > 0)
{
bool flag = false;
System.Data.DataTable dtClone = dt.Clone();
foreach (DataColumn dc in dtClone.Columns)
{
if (dc.DataType.Name.ToLower() == "boolean")
{
dc.DataType = typeof(string);
flag = true;
}
}
if (flag == true)
{
//复制数据到克隆的datatable里
try
{
foreach (DataRow dr in dt.Rows)
{
dtClone.ImportRow(dr);
}
}
catch { }
dt = dtClone;
}
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
if (dt.Rows[i][j].ToString().ToLower() == "true")
{
dt.Rows[i][j] = "是";
}
else if (dt.Rows[i][j].ToString().ToLower() == "false")
{
dt.Rows[i][j] = "";
}
}
}
}
return(dt);
}
catch (Exception e)
{
if (!e.Message.Equals("正在中止线程。"))
{
MessageBox.Show("发生错误,错误信息[" + e.Message + "]", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
return(null);
}
}
protected void Page_Load(object sender, EventArgs e)
{
#region Properties For The Base Class and Check Login
LoginID = Request.QueryString["LoginID"];
LoginInfo = CareerCruisingWeb.CCLib.Login.GetLoginInfo(LoginID);
Redirect();
PageTitle = TextCode(5672);
Section = "connect";
CSS = "connect";
LeftBar = "<TABLE WIDTH='100%' BORDER='0' CELLSPACING='0' CELLPADDING='0'><TR VALIGN='TOP' BGCOLOR='#000000'><TD BACKGROUND='/media/connect/i_c_indigo_bar_bg.gif'><IMG SRC='/media/connect/i_c_connect_icon.gif' WIDTH='24' HEIGHT='23' BORDER='0' alt=''><IMG SRC='/media1/Connect/ClientBranding/Shared/h_network_" + NWBrandingNamePic + SuffixCode() + ".gif' WIDTH='141' HEIGHT='23' ALIGN='TOP' alt='" + NWBrandingShortName + "'>";
ucHeader.strTitle = TextCode(5677);
#endregion Properties For The Base Class and Check Login
#region Get related careers
string jobInfoTableName = "Jobinfo" + SuffixCountryLanguageCode(AbbreviatedCountryCode());
string tblClusterCareer = "Clusters" + ConSysInfo["ConSysCountry"].ToString();
strClusterID = Request.QueryString["cID"];
if ((LoginID != "")&&(strClusterID!="")&&(ConSysID!=""))
{
dtClusterInfo = CareerCruisingWeb.CCLib.Common.DataAccess.GetDataTable("select ClusterType,ClusterCode,ClusteName" + NonEngSuffixCode() + " from ClusterCodes where ClusterID=" + strClusterID);
if (dtClusterInfo.Rows.Count > 0)
{
strSelect = dtClusterInfo.Rows[0]["ClusteName" + NonEngSuffixCode()].ToString();
strClusterCode = dtClusterInfo.Rows[0]["ClusterCode"].ToString();
}
strSQL = "SELECT DISTINCT ji.OccNumber,ji.OccName,ji.OneDesc, ic." + ConSysInfo["ClusterType"].ToString() + " FROM " + jobInfoTableName + " as ji inner join " + tblClusterCareer + " as ic on ji.OccNumber=ic.OccNumber inner join Con_CareerCoaches as cc on ji.OccNumber=cc.OccNumber ";
strSQL += " inner join Con_PartnerUsers cp on cp.PartnerUserID=cc.PartnerUserID ";
strSQL += " where (cc.IsApproved=1) and cp.ConSysID="+ConSysID+" and cp.PartnerStatusID=4 ORDER by ji.OccName ";
dtCareerClusters = CareerCruisingWeb.CCLib.Common.DataAccess.GetDataTable(strSQL);
dtCareerSearchResults = new DataTable();
dtCareerSearchResults = dtCareerClusters.Clone();
// select occupations from above list that correspond to cluster code passed
foreach (DataRow row in dtCareerClusters.Rows)
{
allClusterCodes = row[ConSysInfo["ClusterType"].ToString()].ToString().Split(',');
foreach (string clusteCode in allClusterCodes)
{
if (clusteCode == strClusterCode)
{
dtCareerSearchResults.ImportRow(row);
break;
}
}
}
uc1.dtCoachClusterResults = dtCareerSearchResults;
uc1.LoginID = LoginID;
uc1.UserLanguage = UserLanguage;
uc1.PageT = "Cluster";
uc1.ConSysID = ConSysID;
}
#endregion
}
private void Form7_Load(object sender, EventArgs e)
{
xmlFile = XmlReader.Create("Veiculo.xml", new XmlReaderSettings());
DataSet ds = new DataSet();
ds.ReadXml(xmlFile);
dt = ds.Tables[0].Clone(); //Tabela recebe dados XML
//Passando dados do DataSet para Tabela
foreach (DataRow row in ds.Tables[0].Rows)
{
dt.ImportRow(row);
}
view = dt.DefaultView; //View recebe dados da tabela
VeiculoBindingSource.DataSource = view;
this.reportViewer1.RefreshReport();
#region Populando Filtro de Tipo de Veículo
List<String> veiculos = new List<String>();
//Carregando o xml dos Trechos
XElement xmlVeiculos = XElement.Load("Veiculo.xml");
//Populando a lista de cidade com o as cidadesA
foreach (XElement x in xmlVeiculos.Elements())
{
veiculos.Add(x.Element("TipoVeiculo").Value);
}
foreach (string vei in veiculos)
{
this.checkBoxcomboBox_RelVeiculos.Items.Add(vei);
}
#endregion
#region Populando Filtro de Cargas
List<String> cargas = new List<String>();
//Carregando o xml dos Trechos
XElement xmlCargas = XElement.Load("Veiculo.xml");
//Populando a lista de cidade com o as cidadesA
foreach (XElement x in xmlCargas.Elements())
{
cargas.Add(x.Element("CargaMaxima").Value);
}
foreach (string car in cargas)
{
this.checkBoxComboBox2.Items.Add(car);
}
#endregion
}
private DataTable GetNewDataTable(DataTable dt, string condition)
{
DataTable newdt = new DataTable();
newdt = dt.Clone();
DataRow[] dr = dt.Select(condition);
for (int i = 0; i < dr.Length; i++)
newdt.ImportRow((DataRow)dr[i]);
return newdt;
}
public SourceTableProvider(TableLink sourceTableLink, RowLink[] sourceRows)
{
this.sourceTableLink = sourceTableLink;
//this.table = sourceTableLink.Table.Copy();
this.table = sourceTableLink.Table.Clone();
foreach (RowLink sourceRow in sourceRows)
{
table.ImportRow(sourceRow.DataRow);
}
}
/// 執行 DataTable 中的查詢返回新的 DataTable
/// </summary>
/// <param name="dt">來源資料 DataTable</param>
/// <param name="condition">查詢條件</param>
/// <returns></returns>
public DataTable QueryDataTable(DataTable dt, string condition, string sortstr)
{
DataTable newdt = new DataTable();
newdt = dt.Clone();
DataRow[] dr = dt.Select(condition, sortstr);
for (int i = 0; i < dr.Length; i++)
{
newdt.ImportRow((DataRow)dr[i]);
}
return newdt;
}
private DataTable DtFilter(DataTable dtData, string strFilter)
{
DataTable dtTemp = new DataTable();
dtTemp = dtData.Clone();
DataRow[] dr = dtData.Select(strFilter);
for (int i = 0; i < dr.Length; i++)
{
dtTemp.ImportRow((DataRow)dr[i]);
}
return dtTemp;//返回的查询结果
}
private static DataTable CloneTable(DataTable dt)
{
System.Data.DataTable temporaryHoldingTable = new System.Data.DataTable();
temporaryHoldingTable = dt.Clone();
temporaryHoldingTable.Columns[dt.Columns.Count - 1].DataType = typeof(double);
foreach (DataRow row in dt.Rows)
{
temporaryHoldingTable.ImportRow(row);
}
return temporaryHoldingTable;
}
private DataTable specialDataSort(DataTable dt)
{
DataTable sortedDt = dt.Clone();
string Fac = null;
dt.AcceptChanges();
foreach (DataRow row in dt.Rows)
{
if (row.RowState != DataRowState.Deleted)
{
string newFac = row[dalMac.MacLocation].ToString();
if (Fac != newFac)
{
foreach (DataRow row2 in dt.Rows)
{
if (row2.RowState != DataRowState.Deleted)
{
string FacSearch = row2[dalMac.MacLocation].ToString();
if (Fac == FacSearch)
{
DataRow newRow2 = sortedDt.NewRow();
newRow2 = row2;
sortedDt.ImportRow(newRow2);
row2.Delete();
}
}
}
}
DataRow newRow = sortedDt.NewRow();
newRow = row;
sortedDt.ImportRow(newRow);
row.Delete();
Fac = newFac;
}
}
return(sortedDt);
}
// String Variabllera göre listeleyen fonksiyon// coloumn index gerekli
public DataTable auctionListByVariable(string Variable, int ColoumnIndex)
{
DataTable tabl2 = new DataTable();
tabl2 = auctionTable.Clone();
for (int i = 0; i < auctionTable.Rows.Count; i++)
{
if (auctionTable.Rows[i][ColoumnIndex].ToString().StartsWith(Variable))
{
tabl2.ImportRow(auctionTable.Rows[i]);
}
}
return tabl2;
}
public DataTable chequeListByChecked(bool check,int ColoumnIndex)
{
DataTable tabl4=new DataTable();
tabl4=table.Clone();
for (int i = 0; i < table.Rows.Count; i++)
{
if (Convert.ToBoolean(table.Rows[i][ColoumnIndex])==check)
{
tabl4.ImportRow(table.Rows[i]);
}
}
return tabl4;
}
public DataTable productBuyTableByVariable(string Variable, int ColoumnIndex)
{
DataTable table6=new DataTable();
table6 = productBuyTable.Clone();
for (int i = 0; i < productBuyTable.Rows.Count; i++)
{
if (productBuyTable.Rows[i][ColoumnIndex].ToString().StartsWith(Variable))
{
table6.ImportRow(productBuyTable.Rows[i]);
}
}
return table6;
}
// String Variabllera göre listeleyen fonksiyon// coloumn index gerekli
public DataTable chargeListByVariable(string Variable, int ColoumnIndex)
{
DataTable tabl2 = new DataTable();
tabl2 = tablecharge3.Clone();
for (int i = 0; i < tablecharge3.Rows.Count; i++)
{
if (tablecharge3.Rows[i][ColoumnIndex].ToString().StartsWith(Variable))
{
tabl2.ImportRow(tablecharge3.Rows[i]);
}
}
return tabl2;
}
private DataSet GenerateExcelData(string fileName)
{
Console.WriteLine("generate excel data started");
DataSet projectDs = new DataSet();
DataTable projectTable = new DataTable();
OleDbConnection oledbConn = null;
try
{
if (!File.Exists(fileName)) throw new Exception("No excel input file specified");
oledbConn = new OleDbConnection(String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 8.0;HDR=YES;\""));
oledbConn.Open();
OleDbCommand cmd = new OleDbCommand(); ;
OleDbDataAdapter oleda = new OleDbDataAdapter();
DataSet ds = new DataSet();
cmd.Connection = oledbConn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select * from [Sheet1$]";
oleda = new OleDbDataAdapter(cmd);
oleda.Fill(ds, "Project");
var groups = ds.Tables[0].AsEnumerable().GroupBy(t => t.Field<string>("Project Name"));
int i = 0;
foreach (var group in groups)
{
projectTable = ds.Tables[0].Clone();
projectTable.TableName = "Project" + i++;
var rowList = ds.Tables[0].AsEnumerable().Where(t => t.Field<string>("Project Name") == group.Key);
//If excel data contains TASK ID sort all the tasks by TASK ID
if (DataSetBuilder.Mapping.TaskMap.ContainsKey("TASK_ID") && rowList.CopyToDataTable().Columns.Contains(DataSetBuilder.Mapping.TaskMap["TASK_ID"]))
{
rowList = rowList.OrderBy(t => t.Table.Columns[DataSetBuilder.Mapping.TaskMap["TASK_ID"]]);
}
foreach (DataRow row in rowList)
projectTable.ImportRow(row);
projectDs.Tables.Add(projectTable);
}
return projectDs;
}
// need to catch possible exceptions
catch (Exception ex)
{
throw ex;
}
finally
{
oledbConn.Close();
}
Console.WriteLine("generate excel data done successfully");
}
/// <summary>
/// 导入所有父行（包括自己）
/// </summary>
private static void ImportParentRow(DataTable dtSource, DataTable dtSlt, DataRow currentRow)
{
if (!dtSlt.Rows.Contains(currentRow["ID"])) //不存在则导入行
{
dtSlt.ImportRow(currentRow);
}
if (!string.IsNullOrEmpty(currentRow["PARENT_ID"] + "")) //如果还有父项
{
DataRow row = dtSource.Select(string.Format("ID='{0}'", currentRow["PARENT_ID"]))[0];
ImportParentRow(dtSource, dtSlt, row);
}
}
public static string FillExpGrid(int PageNumber, int PageSize, string filter)
{
if (HttpContext.Current.Session["UserName"] == null)
{
return "Error: You are not logged-in.";
}
DBClass db = new DBClass("SCM");
db.Con.Open();
if (String.IsNullOrEmpty(filter))
db.Com.CommandText = "SELECT [AGglCode], [FOglCode], [SDglCode], [COGSglCode] FROM [costsetupOverheadMainheadGLCode] ORDER BY [id]";
else
db.Com.CommandText = "SELECT [AGglCode], [FOglCode], [SDglCode], [COGSglCode] FROM [costsetupOverheadMainheadGLCode] where Branchid = '"+filter+"' ORDER BY [id]";
SqlDataAdapter da = new SqlDataAdapter(db.Com);
DataTable dt = new DataTable();
da.Fill(dt);
DataTable paggedtable = new DataTable();
paggedtable = dt.Clone();
int initial = (PageNumber - 1) * PageSize + 1;
int last = initial + PageSize;
for (int i = initial - 1; i < last - 1; i++)
{
if (i >= dt.Rows.Count)
{
break;
}
paggedtable.ImportRow(dt.Rows[i]);
}
System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
Dictionary<string, object> row;
foreach (DataRow dr in paggedtable.Rows)
{
row = new Dictionary<String, Object>();
foreach (DataColumn col in paggedtable.Columns)
{
row.Add(col.ColumnName, dr[col]);
}
rows.Add(row);
}
db.Con.Close();
db = null;
return String.Format("{{\"total\":{0},\"rows\":{1}}}", dt.Rows.Count, serializer.Serialize(rows));
}
/// <summary>
/// ��ȡ���ڵ���ΪparentId�����нڵ㣬����DataTable����
/// </summary>
/// <param name="parentId">���ڵ���</param>
/// <returns>DataTable��ʽ�����к��ӽڵ�����</returns>
private DataTable GetChilds(int parentId)
{
DataTable childNodes = new DataTable();
childNodes = this._dataTable.Clone();
foreach (DataRow dr in this._dataTable.Rows)
{
if (Convert.ToInt32(dr["ParentId"]) == parentId)
{
childNodes.ImportRow(dr);
}
}
return childNodes;
}
private void button3_Click(object sender, EventArgs e)
{
DataTable list3 = new DataTable();
string importtext = textBox1.Text;
list3 = list2.Clone();
DataRow[] dr = list2.Select("dataname like '%" + importtext + "%'");
for (int i = 0; i < dr.Length; i++)
{
list3.ImportRow(dr[i]);
}
listBox2.DataSource = list3;
listBox2.DisplayMember = "dataname";
listBox2.ValueMember = "quandlcode";
}
public void BindDropDownList()
{
string query = "(&(objectCategory=person))";
string[] columns = new string[] { "displayName", "sAMAccountName", "userAccountControl" };
string ldapPath = "LDAP://dc=classroom,dc=org";
DataSet ds = (new LdapAuthentication(ldapPath)).FindUsers(query, columns, ldapPath, true);
DataTable users = new DataTable();
foreach (string prop in columns)
{
users.Columns.Add(prop, typeof(string));
}
string[] exclude = new string[] {
"sh1pryor", "sh1lalley", "sh1smith", "sh1curtis", "sh1bailey", "sh1russell",
"sh1morris", "sh1patterson", "sh1barron", "sh1vanfrank", "sh1bush", "sh1twilson",
"sh1fqureshi", "sh1brunt", "sh1larsen", "sh1boren", "sh1tqureshi", "sh1garrone",
"sh1swanson", "sh1jackson", "sh1vaughn", "sh1rnew", "testjl", "Members", "training",
"sqlalerts", "exam", "sonictest", "impact", "commview", "ach", "ittest", "teachmoney",
"hrtest", "helpdesk", "ibmdirector", "ittestaccount", "dosstest", "stapler",
"Mastercard", "Test1", "ipads", "oci", "ULTEST", "TellerTest", "mshift"
};
foreach (DataTable dt in ds.Tables)
{
foreach (DataRow dr in dt.Rows)
{
if (!exclude.Contains(dr["sAMAccountName"]))
{
if (dr["sAMAccountName"].ToString() != _user &&
(dr["userAccountControl"].ToString() == "512" ||
dr["userAccountControl"].ToString() == "1114624"))
{
users.ImportRow(dr);
}
}
}
}
DataView dvSelectOUUsers = users.DefaultView;
dvSelectOUUsers.Sort = "displayName ASC";
selectOUUsers.DataSource = users;
selectOUUsers.DataTextField = "displayName";
selectOUUsers.DataValueField = "sAMAccountName";
selectOUUsers.DataBind();
}
public void ShowFor(Receipt par, String ItemName, decimal SKU, DataTable SuggestedComposition, String Batch)
{
lblItemName.Text = ItemName;
lblTotalSKUs.Text = SKU.ToString();
lblBatchNumber.Text = Batch;
suggestion = SuggestedComposition.Clone();
totalSKU = SKU;
foreach (DataRow d in SuggestedComposition.Rows)
{
suggestion.ImportRow(d);
}
gridControl1.DataSource = suggestion;
instance = par;
this.ShowDialog(par);
}
public DataTable productBuyTableByDate(DateTime date1,DateTime date2, int ColoumnIndex)
{
DataTable table7 = new DataTable();
table7 = productBuyTable.Clone();
for (int i = 0; i < productBuyTable.Rows.Count; i++)
{
if (date1 <= Convert.ToDateTime(productBuyTable.Rows[i][ColoumnIndex]))
{
if (Convert.ToDateTime(productBuyTable.Rows[i][ColoumnIndex]) <= date2)
{
table7.ImportRow(productBuyTable.Rows[i]);
}
}
}
return table7;
}
public DataTable cashListByDate(DateTime date1,DateTime date2,int ColoumnIndex)
{
DataTable tabl3 = new DataTable();
tabl3=cashTable.Clone();
for (int i = 0; i < cashTable.Rows.Count; i++)
{
if (date1<=Convert.ToDateTime(cashTable.Rows[i][ColoumnIndex]))
{
if (Convert.ToDateTime(cashTable.Rows[i][ColoumnIndex])<=date2)
{
tabl3.ImportRow(cashTable.Rows[i]);
}
}
}
return tabl3;
}
public System.Data.DataTable FiltraDataTable(System.Data.DataTable dtprincipal, string ctrFiltro)
{
/* Seleccionamos las filas que se correspondan con el identificador */
System.Data.DataRow[] rows = dtprincipal.Select(ctrFiltro);
/* Clonamos la estructura del objeto DataTable principal */
System.Data.DataTable dt1 = dtprincipal.Clone();
// Importamos los registros al nuevo DataTable
foreach (DataRow row in rows)
{
dt1.ImportRow(row);
}
return(dt1);
}
private System.Data.DataTable FilterComprobantes(string comprobantesCuentaCorriente, System.Data.DataTable tableParam)
{
System.Data.DataTable table = new System.Data.DataTable();
table.Columns.Add("IdTipoDeComprobante");
table.Columns.Add("Descripcion");
ArrayList aux = mz.erp.systemframework.Util.Parse(comprobantesCuentaCorriente, ",");
foreach (System.Data.DataRow row in tableParam.Rows)
{
if (aux.Contains(row["IdTipoDeComprobante"]))
{
table.ImportRow(row);
}
}
return(table);
}
private void ListData()
{
DataTable dt = new DataTable();
Koneksi koneksi = new Koneksi();
dt = koneksi.tampil_data_dw("SELECT pemb_id as 'PEMBIAYAAN ID',pemb_kd_bidang as 'KODE BIDANG',pemb_keterangan as 'KETERANGAN' FROM dim_pembiayaan");
try
{
paging_PageIndex = 1;
if (dt.Rows.Count > 0)
{
DataTable tmpTable = new DataTable();
tmpTable = dt.Clone();
if (dt.Rows.Count >= paging_NoOfRecPerPage)
{
for (int i = 0; i < paging_NoOfRecPerPage; i++)
{
tmpTable.ImportRow(dt.Rows[i]);
}
}
else
{
for (int i = 0; i < dt.Rows.Count; i++)
{
tmpTable.ImportRow(dt.Rows[i]);
}
}
listDatadimensi.DataContext = tmpTable.DefaultView;
tmpTable.Dispose();
dt_TempData = dt;
}
else
{
// MessageBox.Show("Message");
}
}
catch (Exception ex)
{
MessageBox.Show("Error Message" + ex);
}
} //void closing
private System.Data.DataTable ShowData(int pageNumber)
{
System.Data.DataTable dt = new System.Data.DataTable();
int startIndex = PageSize * (pageNumber - 1);
var result = DataSource.AsEnumerable().Where((s, k) => (k >= startIndex && k < (startIndex + PageSize)));
foreach (DataColumn colunm in DataSource.Columns)
{
dt.Columns.Add(colunm.ColumnName);
}
foreach (var item in result)
{
dt.ImportRow(item);
}
grd_order.Rows.Clear();
foreach (var item in result)
{
// grd_order.Rows.Add(item);
//grd_order.Rows[i].Cells[1].Value = dtselect.Rows[i]["Client_Order_Number"].ToString();
//grd_order.Rows[i].Cells[2].Value = dtselect.Rows[i]["Client_Name"].ToString();
//grd_order.Rows[i].Cells[3].Value = dtselect.Rows[i]["Sub_ProcessName"].ToString();
//grd_order.Rows[i].Cells[4].Value = dtselect.Rows[i]["Date"].ToString();
//grd_order.Rows[i].Cells[5].Value = dtselect.Rows[i]["Client_Order_Ref"].ToString();
//grd_order.Rows[i].Cells[6].Value = dtselect.Rows[i]["Order_Type"].ToString();
//grd_order.Rows[i].Cells[7].Value = dtselect.Rows[i]["Borrower_Name"].ToString();//BArrower Namr
//grd_order.Rows[i].Cells[8].Value = dtselect.Rows[i]["Address"].ToString();
//grd_order.Rows[i].Cells[9].Value = dtselect.Rows[i]["County"].ToString();
//grd_order.Rows[i].Cells[10].Value = dtselect.Rows[i]["State"].ToString();
//grd_order.Rows[i].Cells[11].Value = dtselect.Rows[i]["County_Type"].ToString();
//grd_order.Rows[i].Cells[12].Value = dtselect.Rows[i]["Date"].ToString();//Process date Should be Completed or Recived date
//grd_order.Rows[i].Cells[13].Value = dtselect.Rows[i]["Order_Status"].ToString();
//grd_order.Rows[i].Cells[14].Value = dtselect.Rows[i]["Progress_Status"].ToString();
//grd_order.Rows[i].Cells[15].Value = dtselect.Rows[i]["User_Name"].ToString();
//grd_order.Rows[i].Cells[16].Value = dtselect.Rows[i]["ORDER_PRIORITY_STATUS"].ToString();
//grd_order.Rows[i].Cells[17].Value = "";//Client TAT
//grd_order.Rows[i].Cells[18].Value = "";//EMP TAT
//grd_order.Rows[i].Cells[19].Value = dtselect.Rows[i]["Order_ID"].ToString();
//grd_order.Rows[i].Cells[20].Value = "Delete";
}
//txtPaging.Text = string.Format("Page {0} Of {1} Pages", pageNumber, (DataSource.Rows.Count / PageSize) + 1);
return(dt);
}
private bool obtieneDatosDeProducto()
{
if ((textBoxClave.Text == "") || (textBoxClave.Text == null))
{
MessageBox.Show("Introduce una clave de producto");
return(false);
}
try
{
System.Data.DataTable foundRowsTable = new System.Data.DataTable();
DataRow[] foundRows;
foundRowsTable = new System.Data.DataTable();
foundRowsTable = tablaEspecificaciones.Clone();
foundRows = tablaEspecificaciones.Select("clave like " + "'" + textBoxClave.Text + "' AND clave not like '%OBSOLETO%'");
foreach (DataRow row in foundRows)
{
foundRowsTable.ImportRow(row);
}
if (foundRowsTable.Rows.Count == 1)
{
textBoxClave.Text = foundRowsTable.Rows[0]["clave"].ToString();
textBoxCliente.Text = foundRowsTable.Rows[0]["cliente"].ToString();
textBoxCalibre.Text = foundRowsTable.Rows[0]["calibre"].ToString();
textBoxMaterial.Text = foundRowsTable.Rows[0]["material"].ToString();
textBoxColor.Text = foundRowsTable.Rows[0]["color"].ToString();
textBoxPerfil.Text = foundRowsTable.Rows[0]["perfil"].ToString();
textBoxCorte.Text = foundRowsTable.Rows[0]["corte"].ToString();
return(true);
}
else
{
MessageBox.Show("Clave no encontrada");
return(false);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
return(false);
}
}
private void LoadData()
{
int nStartPos = 0; //当前页面开始记录行
int nEndPos = 0; //当前页面结束记录行
DataSet ds;
ds = new projectBAL().Select(filetype, pId, gdmethod);
DataTable dt = ds.Tables[0];
System.Data.DataTable dtTemp = dt.Clone(); //克隆DataTable结构框架
if (dtTemp != null || dtTemp.Rows.Count > 0)
{
if (pageCurrent == pageCount)
{
nEndPos = nMax;
}
else
{
nEndPos = pageSize * pageCurrent;
}
nStartPos = nCurrent;
lblPageCount.Text = pageCount.ToString(); //在bdnInfo中添加lable记录总页数
txtCurrentPage.Text = Convert.ToString(pageCurrent); //在bdnInfo中添加Textbox记录当前页数
//从元数据源复制记录行
for (int i = nStartPos; i < nEndPos; i++)
{
if (i < dt.Rows.Count)
{
dtTemp.ImportRow(dt.Rows[i]);
nCurrent++;
}
}
bdsInfo.DataSource = dtTemp;
bdnInfo.BindingSource = bdsInfo;
dataGridView1.DataSource = bdsInfo;
}
else
{
return;
}
}
//condences datatable one in order to export it into an excel spreadsheet
public static void ChopTable(DataTable tableOne, DataTable tableTwo)
{
DataColumn system = new DataColumn("System");
DataColumn priority_Number = new DataColumn("Priority_Number");
DataColumn category = new DataColumn("Category");
DataColumn bid = new DataColumn("BID");
DataColumn opened_Date = new DataColumn("Opened_Date");
DataColumn title = new DataColumn("Title");
tableTwo.Columns.Add(system);
tableTwo.Columns.Add(priority_Number);
tableTwo.Columns.Add(category);
tableTwo.Columns.Add(bid);
tableTwo.Columns.Add(opened_Date);
tableTwo.Columns.Add(title);
foreach (DataRow row in tableOne.Rows)
{
tableTwo.ImportRow(row);
}
}
private void SetTodaysSkierVisits()
{
lblLastUpdate.Text = CF.GetSQLField(AM.MirrorConn, "SELECT TIME(MAX(dtverwdat)) FROM axess_cwc.tablesertrans");
//dateTimePicker1.Value = DateTime.Today.AddDays(-1); //set to DateTime.Today for release;
string Today = DateTime.Today.ToString(Mirror.AxessDateFormat);
string Tomorrow = DateTime.Today.AddDays(1).ToString(Mirror.AxessDateFormat);
//System.Data.DataTable dtTodaysSkierVisits = CF.LoadTable(DW.dwConn, $"SELECT A.tgroup, A.descrip, COUNT(*) AS Visits FROM {DW.ActiveDatabase}.tgroups AS A inner join {DW.ActiveDatabase}.skivisits AS B ON B.tgroup=A.tgroup LEFT JOIN {DW.ActiveDatabase}.altadata AS C ON C.descrip = A.descrip WHERE B.readdate='{DateTime.Today.ToString(Mirror.AxessDateFormat)}' AND B.ALTAFLAG='Y' AND A.tgroup NOT IN ('AJ','AR','SC','SS','GC','SO') GROUP BY A.tgroup ORDER BY C.sorder", "SkiVisits");
System.Data.DataTable dtTodaysSkierVisits = CF.LoadTable(AM.MirrorConn, $"SELECT HOUR(DTVERWDAT) AS SkierHour, COUNT(DISTINCT concat_ws(' - ',nkassanr, nseriennr)) AS SkierRides FROM axess_cwc.tablesertrans WHERE dtverwdat >= '{Today}' AND dtverwdat < '{Tomorrow}' AND NZUTRNR < 50 GROUP BY HOUR(dtverwdat)", "todaysreads");
//Int32 SkierVisitsTotal = 0;
//foreach (DataRow tRow in dtTodaysSkierVisits.Rows)
//{
// SkierVisitsTotal += Convert.ToInt32(tRow["Visits"].ToString());
//}
object[] rowVals = new object[2];
System.Data.DataTable table = new System.Data.DataTable();
table.Columns.Add("SkierHour", typeof(string));
table.Columns.Add("SkierRides", typeof(Int64));
DataRowCollection rowCollection = table.Rows;
foreach (DataRow DR in dtTodaysSkierVisits.Rows)
{
rowVals[0] = Hours[DR.Field <Int64>("SkierHour")];
rowVals[1] = DR.Field <Int64>("SkierRides");
DataRow row = rowCollection.Add(rowVals);
}
System.Data.DataTable DTSkiers = new System.Data.DataTable();
DTSkiers.Columns.Add("SkierHour", typeof(string));
DTSkiers.Columns.Add("SkierRides", typeof(Int64));
foreach (DataRow row in rowCollection)
{
DTSkiers.ImportRow(row);
}
gvTodaysSkierVisits.DataSource = DTSkiers;
lblLastUpdate.Text = CF.GetSQLField(AM.MirrorConn, $"SELECT MAX(dtverwdat) FROM {AM.ActiveDatabase}.tablesertrans");
txtTodayVisits.Text = CF.GetSQLField(AM.MirrorConn, $"SELECT COUNT(DISTINCT concat_ws('-',nkassanr, nseriennr)) FROM {AM.ActiveDatabase}.tablesertrans WHERE dtverwdat >= '{Today}' AND dtverwdat < '{Tomorrow}' AND nzutrnr < 50");
Refresh();
}
/*/// <summary>
* /// 绑定DropDownList的方法
* /// </summary>
* /// <param name="dropDownList"></param>
* /// <param name="configTypeName"></param>
* /// <param name="tipString"></param>
* protected void DropDownListDataBind(DropDownList dropDownList, ConfigTypeName configTypeName, string tipString)
* {
* System.Data.DataTable dt = new ConfigureBLL().GetConfig(configTypeName.ToString());
* dropDownList.DataSource = dt;
* dropDownList.DataTextField = "configvalue";
* dropDownList.DataValueField = "configkey";
* dropDownList.DataBind();
* dropDownList.Items.Insert(0, new ListItem(tipString, string.Empty));
* }*/
/// <summary>
/// 绑定DropDownList的方法
/// </summary>
/// <param name="dropDownList"></param>
/// <param name="configTypeName"></param>
/// <param name="tipString"></param>
protected void DropDownListDataBind(DropDownList dropDownList, System.Data.DataTable dtConfig, ConfigTypeName configTypeName, string tipString)
{
try
{
//DataTable dt = new ConfigureBLL().GetConfig(configTypeName.ToString());
System.Data.DataTable dtNew = dtConfig.Clone();
DataRow[] drArray = dtConfig.Select("configtypename='" + configTypeName + "'");
for (int i = 0; i < drArray.Length; i++)
{
dtNew.ImportRow(drArray[i]);
}
dropDownList.DataSource = dtNew;
dropDownList.DataTextField = "configvalue";
dropDownList.DataValueField = "configkey";
dropDownList.DataBind();
dtNew = null;
dropDownList.Items.Insert(0, new ListItem(tipString, string.Empty));
}
catch (Exception ex)
{
LogHelper.WriteLine(ex.Message + ex.StackTrace);
ExecuteScript("AlertDialog('程序出错！', null);");
}
}
private void LoadProductionRecord()
{
recordLoaded = false;
dgvMoreDetail.DataSource = null;
DataTable dt = NewProductionRecordTable();
dt_ProRecord = dalProRecord.SelectWithItemInfo();
DataTable dt_Mac = dalMac.Select();
int index = 1;
foreach (DataRow row in dt_ProRecord.Rows)
{
int macID = int.TryParse(row[dalPlan.machineID].ToString(), out macID) ? macID : 0;
string fac = tool.getFactoryNameFromMachineID(macID.ToString(), dt_Mac);
DateTime proDate = Convert.ToDateTime(row[dalProRecord.ProDate]).Date;
int planID = int.TryParse(row[dalPlan.planID].ToString(), out planID) ? planID : 0;
int sheetID = int.TryParse(row[dalProRecord.SheetID].ToString(), out sheetID) ? sheetID : 0;
string shift = row[dalProRecord.Shift].ToString();
string itemCode = row[dalItem.ItemCode].ToString();
string itemName = row[dalItem.ItemName].ToString();
int totalStockIn = int.TryParse(row[dalProRecord.TotalProduced].ToString(), out totalStockIn) ? totalStockIn : 0;
string rawMat = row[dalItem.ItemMaterial].ToString();
string colorMat = row[dalItem.ItemMBatch].ToString();
bool dataMatched = true;
#region filter date
DateTime dateFrom = dtpFrom.Value.Date;
DateTime dateTo = dtpTo.Value.Date;
if (proDate < dateFrom || proDate > dateTo)
{
dataMatched = false;
}
#endregion
#region filter item
string filterName = cmbPartName.Text;
string filterCode = cmbPartCode.Text;
if (!string.IsNullOrEmpty(filterName) && filterName != itemName)
{
dataMatched = false;
}
if (!string.IsNullOrEmpty(filterCode) && filterCode != itemCode)
{
dataMatched = false;
}
#endregion
#region filter factory
string filterFac = cmbFac.Text;
if (filterFac != "All" && !string.IsNullOrEmpty(filterFac) && filterFac != fac)
{
dataMatched = false;
}
#endregion
#region filter machine
string filterMac = cmbMac.Text;
if (!string.IsNullOrEmpty(filterMac) && filterMac != macID.ToString())
{
dataMatched = false;
}
#endregion
if (dataMatched)
{
DataRow dt_Row = dt.NewRow();
dt_Row[header_Index] = index;
dt_Row[header_Fac] = fac;
dt_Row[header_Mac] = macID;
if (cbShowOnlyOneRowForEachPlan.Checked)
{
dt_Row[header_ProDateTo] = proDate;
dt_Row[header_ProDateFrom] = proDate;
}
else
{
dt_Row[header_ProDate] = proDate;
dt_Row[header_SheetID] = sheetID;
dt_Row[header_Shift] = shift;
}
dt_Row[header_PlanID] = planID;
dt_Row[header_PartName] = itemName;
dt_Row[header_StockIn] = totalStockIn;
if (cbShowRawMat.Checked)
{
dt_Row[header_RawMat] = rawMat;
}
if (cbShowColorMat.Checked)
{
dt_Row[header_ColorMat] = colorMat;
}
dt.Rows.Add(dt_Row);
index++;
}
}
#region show only 1 row for each plan
if (cbShowOnlyOneRowForEachPlan.Checked)
{
DataTable dt_FilterDuplicatePlan = NewProductionRecordTable();
string previousPlanID = null;
int totalStockIn = 0;
//sorting dt by planID and date
dt.DefaultView.Sort = header_PlanID + " ASC";
dt = dt.DefaultView.ToTable();
foreach (DataRow row in dt.Rows)
{
DateTime proDate = Convert.ToDateTime(row[header_ProDateTo]);
if (previousPlanID == row[header_PlanID].ToString())
{
totalStockIn += Convert.ToInt32(row[header_StockIn].ToString());
dt_FilterDuplicatePlan.Rows[dt_FilterDuplicatePlan.Rows.Count - 1][header_ProDateFrom] = proDate;
dt_FilterDuplicatePlan.Rows[dt_FilterDuplicatePlan.Rows.Count - 1][header_StockIn] = totalStockIn;
}
else
{
previousPlanID = row[header_PlanID].ToString();
totalStockIn = Convert.ToInt32(row[header_StockIn].ToString());
dt_FilterDuplicatePlan.ImportRow(row);
}
//if(previousPlanID == null)
//{
// previousPlanID = row[header_PlanID].ToString();
// totalStockIn = Convert.ToInt32(row[header_StockIn].ToString());
// dt_FilterDuplicatePlan.Rows.Add(row);
//}
//else if(previousPlanID == row[header_PlanID].ToString())
//{
// totalStockIn += Convert.ToInt32(row[header_StockIn].ToString());
// dt_FilterDuplicatePlan.Rows[dt_FilterDuplicatePlan.Rows.Count - 1][header_StockIn] = totalStockIn;
//}
//else
//{
// previousPlanID = row[header_PlanID].ToString();
// totalStockIn = Convert.ToInt32(row[header_StockIn].ToString());
// dt_FilterDuplicatePlan.Rows.Add(row);
//}
}
dt_FilterDuplicatePlan.DefaultView.Sort = header_Mac + " ASC, " + header_ProDateFrom + " ASC";
dt_FilterDuplicatePlan = dt_FilterDuplicatePlan.DefaultView.ToTable();
int indexNo = 1;
foreach (DataRow row in dt_FilterDuplicatePlan.Rows)
{
row[header_Index] = indexNo;
indexNo++;
}
dt = dt_FilterDuplicatePlan.Copy();
}
#endregion
dgvProductionRecord.DataSource = dt;
dgvProductionRecord.ClearSelection();
recordLoaded = true;
}
private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{
List <ErrorList> list = new List <ErrorList>();
ExcelTableFinal = new DataTable();
try
{
CallParameter cp3 = (CallParameter)e.Argument;
ExcelTable = cr.readExcelFileToDataTable(cp3.FileName, "ProductSheet$");
while (ExcelTable.Columns.Count > 17)
{
ExcelTable.Columns.RemoveAt(17);
}
ExcelTableFinal = ExcelTable.Clone();
ExcelTable.AsEnumerable().Where(row => row["product id"] != null && row["product id"].ToString() != "").ToList().ForEach(row => ExcelTableFinal.ImportRow(row));
if (ExcelTableFinal != null)
{
if (ExcelTable.Columns[0].ColumnName.Trim().ToUpper() != "PRODUCT ID" ||
ExcelTable.Columns[1].ColumnName.Trim().ToUpper() != "PRODUCT NAME" ||
ExcelTable.Columns[2].ColumnName.Trim().ToUpper() != "PRODUCT DESCRIPTION" ||
ExcelTable.Columns[3].ColumnName.Trim().ToUpper() != "UOM" ||
ExcelTable.Columns[4].ColumnName.Trim().ToUpper() != "SORT ORDER" ||
ExcelTable.Columns[5].ColumnName.Trim().ToUpper() != "ACTIVE" ||
ExcelTable.Columns[6].ColumnName.Trim().ToUpper() != "BC ACTIVE" ||
ExcelTable.Columns[7].ColumnName.Trim().ToUpper() != "PRODUCT CATEGORY" ||
ExcelTable.Columns[8].ColumnName.Trim().ToUpper() != "PRODUCT CODE" ||
ExcelTable.Columns[9].ColumnName.Trim().ToUpper() != "PRICE DISPLAY CODE" ||
ExcelTable.Columns[10].ColumnName.Trim().ToUpper() != "COST CENTRE CODE" ||
ExcelTable.Columns[11].ColumnName.Trim().ToUpper() != "MINI BILL COMPLETED" ||
ExcelTable.Columns[12].ColumnName.Trim().ToUpper() != "STATE" ||
ExcelTable.Columns[13].ColumnName.Trim().ToUpper() != "ISSTUDIOMPRODUCT" ||
ExcelTable.Columns[14].ColumnName.Trim().ToUpper() != "INTERNAL DESCRIPTION" ||
ExcelTable.Columns[15].ColumnName.Trim().ToUpper() != "ADDITIONAL NOTES" ||
ExcelTable.Columns[16].ColumnName.Trim().ToUpper() != "OPERATION ONLY"
)
{
ErrorList l = new ErrorList();
l.MessageText = "Column name or number does not match standard format.";
list.Add(l);
e.Result = list;
}
else
{
e.Result = IsSourceValidate(ExcelTableFinal);
}
}
else
{
ErrorList l = new ErrorList();
l.MessageText = "The excel is empty.";
list.Add(l);
e.Result = list;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public System.Data.DataTable FormatDataTable(System.Data.DataTable dt)
{
List <string> colNames = new List <string>();
List <string> DateNames = new List <string>();
string DepartmentColumn = "";
System.Data.DataTable dtCloned = dt.Clone();
foreach (DataColumn c in dt.Columns)
{
if (c.DataType == typeof(String))
{
if (c.ColumnName.Equals("Avdelings Navn") || c.ColumnName.Equals("Rabatt Type") || c.ColumnName.Equals("AvdelingsNavn"))
{
DepartmentColumn = c.ColumnName;
}
}
else
if (c.DataType == typeof(Decimal))
{
dtCloned.Columns[c.ColumnName].DataType = typeof(Decimal);
colNames.Add(c.ColumnName);
}
else if (c.DataType == typeof(int))
{
dtCloned.Columns[c.ColumnName].DataType = typeof(int);
colNames.Add(c.ColumnName);
}
else if (c.DataType == typeof(float))
{
dtCloned.Columns[c.ColumnName].DataType = typeof(float);
colNames.Add(c.ColumnName);
}
//if (c.DataType == typeof(DateTime))
//{
// DateNames.Add(c.ColumnName);
//}
}
String lstDepartment = "";
foreach (DataRow row in dt.Rows)
{
if (DepartmentColumn != "")
{
String Departmentname = row.Field <String>(DepartmentColumn);
if (!lstDepartment.Equals(Departmentname))
{
DataRow row2 = dtCloned.NewRow();
// probalby set background color here somehow
row2.SetField(DepartmentColumn, Departmentname);
row.SetField(DepartmentColumn, "");
dtCloned.Rows.Add(row2);
lstDepartment = Departmentname;
}
else
{
row.SetField(DepartmentColumn, "");
}
}
dtCloned.ImportRow(row);
}
foreach (DataRow dr in dtCloned.Rows)
{
foreach (DataColumn dc in dtCloned.Columns)
{
if (colNames.Contains(dc.ColumnName))
{
double value;
string whatYouWant = "";
double dts = 00;
if (double.TryParse((dr[dc]).ToString(), out value))
{
dts = Convert.ToDouble(dr[dc]);
if (decimalCount == 0)
{
whatYouWant = dts.ToString("#,##0");
}
else if (decimalCount == 2)
{
whatYouWant = dts.ToString("#,##0.00");
}
int intvalue;
if (int.TryParse(whatYouWant.Trim(), out intvalue))
{
dr[dc] = intvalue;
}
else
{
if (dc.DataType == typeof(int))
{
int.TryParse(whatYouWant, out intvalue);
dr[dc] = intvalue;
}
else
{
dr[dc] = whatYouWant;
}
}
}
//if (double.TryParse((dr[dc]).ToString(), out value))
//{
// dts = Convert.ToDouble(dr[dc]);
// if (decimalCount == 0)
// {
// whatYouWant = dts.ToString("#,##0");
// }
// else if (decimalCount == 2)
// {
// //whatYouWant = dts.ToString("C2");
// whatYouWant = dts.ToString("#,##0.00");
// }
// if (dr[dc].GetType() == typeof(int))
// {
// int intvalue;
// dr[dc] = int.TryParse(whatYouWant, out intvalue);
// }
// else
// {
// dr[dc] = whatYouWant;
// }
//}
}
//if (DateNames.Contains(dc.ColumnName))
//{
// if (!dr[dc].Equals(null))
// {
// DateTime tmpDateTime = Convert.ToDateTime(dr[dc]);
// dr[dc] = Convert.ToDateTime(dr[dc]);
// }
//}
}
}
return(dtCloned);
}
/// <summary>
/// Arrange cycles by as of date and planning period
/// </summary>
/// <param name="pdtmFromMonth">From Month</param>
/// <param name="pdtmToMonth">To Month</param>
/// <param name="pdtbCycles">All cycles</param>
/// <param name="parrPlanningPeriod">All Planning Period</param>
/// <param name="sbCycleIDs">out: cycle ids in range</param>
/// <returns>Arranged cycles</returns>
private DataTable ArrangeCycles(DateTime pdtmFromMonth, DateTime pdtmToMonth, DataTable pdtbCycles,
ArrayList parrPlanningPeriod, out StringBuilder sbCycleIDs)
{
DataTable dtbResult = pdtbCycles.Clone();
DateTime dtmFromDate = new DateTime(pdtmFromMonth.Year, pdtmFromMonth.Month, 1);
DateTime dtmToDate = dtmFromDate.AddMonths(1).AddDays(-1);
if (pdtmToMonth > DateTime.MinValue)
{
dtmToDate = pdtmToMonth;
}
sbCycleIDs = new StringBuilder();
DataTable dtbTemp = pdtbCycles.Clone();
ArrayList arrMonths = GetAllMonthInRange(dtmFromDate, dtmToDate);
#region find all cycle go thru the date range
foreach (DateTime dtmPeriod in parrPlanningPeriod)
{
DataRow[] drowPeriod = pdtbCycles.Select("PlanningPeriod = '" + dtmPeriod.ToString("G") + "'"
, "Version DESC");
foreach (DataRow period in drowPeriod)
{
DateTime dtmFromDateCycle = (DateTime)period["FromDate"];
DateTime dtmToDateCycle = (DateTime)period["ToDate"];
ArrayList arrCycleMonths = GetAllMonthInRange(dtmFromDateCycle, dtmToDateCycle);
foreach (DateTime dtmDate in arrMonths)
{
if (arrCycleMonths.Contains(dtmDate))
{
dtbTemp.ImportRow(period);
}
}
}
}
#endregion
#region sorting all cycle
// order by planning period, from date and version
DataRow[] drowCycles = dtbTemp.Select("", "PlanningPeriod ASC, FromDate ASC, Version DESC");
DateTime dtmPreFromDate = DateTime.MinValue;
int intPreVersion = -1;
DateTime dtmPlanningPeriod = DateTime.MinValue;
if (drowCycles.Length > 0)
{
dtmPlanningPeriod = (DateTime)drowCycles[0]["PlanningPeriod"];
}
for (int i = 0; i < drowCycles.Length; i++)
{
DataRow drowCycle = drowCycles[i];
// from date of current cycle
DateTime dtmCurFromDate = (DateTime)drowCycle["FromDate"];
// version of current cycle
int intVersion = Convert.ToInt32(drowCycle["Version"]);
// this cycle is old version of period, from date is greater than new version then ignore it
if (intVersion < intPreVersion && dtmCurFromDate > dtmPreFromDate &&
dtmPlanningPeriod.Equals(drowCycle["PlanningPeriod"]))
{
continue;
}
// re-assign value
intPreVersion = intVersion;
dtmPreFromDate = dtmCurFromDate;
dtmPlanningPeriod = (DateTime)drowCycle["PlanningPeriod"];
// update ToDate of previous cycle
if (i > 0)
{
// previous cycle
DataRow drowPreCycle = drowCycles[i - 1];
// as of date of current cycle
DateTime dtmAsOfDate = (DateTime)drowCycle["FromDate"];
// update to date of previous cycle
drowPreCycle["ToDate"] = dtmAsOfDate.AddDays(-1);
}
}
if (drowCycles.Length > 0)
{
drowCycles[drowCycles.Length - 1]["ToDate"] = dtmToDate;
}
// import to result table
foreach (DataRow drowCycle in drowCycles)
{
sbCycleIDs.Append(drowCycle["DCOptionMasterID"].ToString() + ",");
dtbResult.ImportRow(drowCycle);
}
#endregion
sbCycleIDs.Append("0");
return(dtbResult);
}
private void InsertAllDataToSheet(string path, string fileName)
{
DataGridView dgv = dgvCommon;
DataTable dt = (DataTable)dgv.DataSource;
Excel.Application excelApp = new Excel.Application
{
Visible = true
};
Workbook g_Workbook = excelApp.Workbooks.Open(
path,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
object misValue = Missing.Value;
if (dt.Rows.Count > 0)//if datagridview have data
{
Worksheet xlWorkSheet = null;
int count = g_Workbook.Worksheets.Count;
xlWorkSheet = g_Workbook.Worksheets.Add(Type.Missing,
g_Workbook.Worksheets[count], Type.Missing, Type.Missing);
xlWorkSheet.Name = "COMMON PART";
xlWorkSheet.PageSetup.LeftHeader = "&\"Courier New\"&8 " + DateTime.Now.ToString("dd/MM/yyyy HH:mm:ss");
xlWorkSheet.PageSetup.CenterHeader = "&\"Courier New\"&12 SPP INVENTORY REPORT: COMMON PART";
xlWorkSheet.PageSetup.RightHeader = "&\"Courier New\"&8 PG -&P";
xlWorkSheet.PageSetup.CenterFooter = "&\"Courier New\"&8 Printed By " + dalUser.getUsername(MainDashboard.USER_ID);
xlWorkSheet.PageSetup.CenterHorizontally = true;
xlWorkSheet.PageSetup.PaperSize = XlPaperSize.xlPaperA4;
xlWorkSheet.PageSetup.Orientation = XlPageOrientation.xlPortrait;
xlWorkSheet.PageSetup.Zoom = false;
xlWorkSheet.PageSetup.FitToPagesWide = 1;
xlWorkSheet.PageSetup.FitToPagesTall = false;
double pointToCMRate = 0.035;
xlWorkSheet.PageSetup.TopMargin = 1.2 / pointToCMRate;
xlWorkSheet.PageSetup.BottomMargin = 1.2 / pointToCMRate;
xlWorkSheet.PageSetup.HeaderMargin = 0.6 / pointToCMRate;
xlWorkSheet.PageSetup.FooterMargin = 0.6 / pointToCMRate;
xlWorkSheet.PageSetup.LeftMargin = 0.7 / pointToCMRate;
xlWorkSheet.PageSetup.RightMargin = 0.7 / pointToCMRate;
xlWorkSheet.PageSetup.PrintTitleRows = "$1:$1";
// Paste clipboard results to worksheet range
copyDGVtoClipboard(dgvCommon);
xlWorkSheet.Select();
Range CR = (Range)xlWorkSheet.Cells[1, 1];
CR.Select();
xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
#region old format setting
Range tRange = xlWorkSheet.UsedRange;
tRange.Font.Size = 11;
tRange.RowHeight = 30;
tRange.VerticalAlignment = XlHAlign.xlHAlignCenter;
tRange.Font.Name = "Courier New";
tRange.BorderAround(Type.Missing, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Type.Missing);
//tRange.Borders.LineStyle = XlLineStyle.xlContinuous;
//tRange.Borders.Weight = XlBorderWeight.xlThin;
//Range FirstRow = (Range)xlWorkSheet.Application.Rows[1, Type.Missing];
Range FirstRow = xlWorkSheet.get_Range("a1:d1").Cells;
FirstRow.WrapText = true;
FirstRow.Font.Size = 8;
FirstRow.HorizontalAlignment = XlHAlign.xlHAlignCenter;
FirstRow.VerticalAlignment = XlHAlign.xlHAlignCenter;
FirstRow.RowHeight = 20;
FirstRow.Interior.Color = Color.WhiteSmoke;
FirstRow.Borders.LineStyle = XlLineStyle.xlContinuous;
FirstRow.Borders.Weight = XlBorderWeight.xlThin;
//tRange.EntireColumn.AutoFit();
//DataTable dt = (DataTable)dgv.DataSource;
int TypeIndex = dgv.Columns["TYPE"].Index;
int SizeIndex = dgv.Columns["SIZE"].Index;
int UnitIndex = dgv.Columns["UNIT"].Index;
int StockIndex = dgv.Columns["STOCK(PCS)"].Index;
xlWorkSheet.Cells[1, TypeIndex + 1].ColumnWidth = 25;
xlWorkSheet.Cells[1, SizeIndex + 1].ColumnWidth = 6;
xlWorkSheet.Cells[1, UnitIndex + 1].ColumnWidth = 6;
xlWorkSheet.Cells[1, StockIndex + 1].ColumnWidth = 25;
//xlWorkSheet.Cells[1, StdIndex + 1].Font.Italic = true;
//rangeForecast1.Font.Italic = true;
//DataTable dt_Source = (DataTable)dgv.DataSource;
for (int j = 0; j <= dt.Rows.Count - 1; j++)
{
Range rangeStock = (Range)xlWorkSheet.Cells[j + 2, StockIndex + 1];
Range rangeType = (Range)xlWorkSheet.Cells[j + 2, TypeIndex + 1];
rangeStock.HorizontalAlignment = XlHAlign.xlHAlignCenter;
rangeType.HorizontalAlignment = XlHAlign.xlHAlignCenter;
System.Drawing.Color color = System.Drawing.Color.Black;
rangeStock.Borders[XlBordersIndex.xlEdgeRight].Color = color;
rangeStock.Borders[XlBordersIndex.xlEdgeLeft].Color = color;
rangeType.Borders[XlBordersIndex.xlEdgeRight].Color = color;
rangeType.Borders[XlBordersIndex.xlEdgeLeft].Color = color;
}
#endregion
releaseObject(xlWorkSheet);
Clipboard.Clear();
dgvCommon.ClearSelection();
}
dgv = dgvUnique;
dt = (DataTable)dgv.DataSource;
#region divide by type
DataTable dt_type = dt.Clone();
string previousType = null;
string currentType = null;
foreach (DataRow row in dt.Rows)
{
//get type. if previous type is null, set previous = current type
currentType = row["TYPE"].ToString();
if (string.IsNullOrEmpty(currentType))
{
currentType = previousType;
}
if (previousType == null || previousType == currentType)
{
previousType = currentType;
//move data to new table
dt_type.ImportRow(row);
}
else if (previousType != currentType)
{
DataRow lastRow = dt_type.Rows[dt_type.Rows.Count - 1];
if (string.IsNullOrEmpty(lastRow["CODE"].ToString()))
{
dt_type.Rows.RemoveAt(dt_type.Rows.Count - 1);
}
dgvUnique.DataSource = dt_type;
MoveUniqueDataToSheet(g_Workbook, previousType);
previousType = currentType;
dt_type = dt.Clone();
if (!string.IsNullOrEmpty(row["TYPE"].ToString()))
{
dt_type.ImportRow(row);
}
}
}
if (dt_type.Rows.Count > 0)
{
dgvUnique.DataSource = dt_type;
MoveUniqueDataToSheet(g_Workbook, previousType);
}
dgvUnique.DataSource = dt;
#endregion
g_Workbook.Worksheets.Item[1].Delete();
g_Workbook.Save();
releaseObject(g_Workbook);
//frmLoading.CloseForm();
Cursor = Cursors.Arrow; // change cursor to normal type
}
private void btnAutoInOut_Click(object sender, EventArgs e)
{
DialogResult dialogResult = MessageBox.Show("Confirm to process In/Out action?", "Message",
MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dialogResult == DialogResult.Yes)
{
DataTable dt = NewDeliverTable();
//DataRow dt_row;
DataTable dt_deliver = (DataTable)dgvDeliver.DataSource;
foreach (DataRow row in dt_deliver.Rows)
{
string matCode = row[headerMatCode].ToString();
bool itemChecked = row[headerCheck] == DBNull.Value ? false : Convert.ToBoolean(row[headerCheck].ToString());
if (!string.IsNullOrEmpty(matCode) && itemChecked)
{
dt.ImportRow(row);
}
}
MatTrfDate = dtpTrfDate.Text;
frmInOutEdit frm = new frmInOutEdit(dt);
frm.StartPosition = FormStartPosition.CenterScreen;
frm.ShowDialog();//Item Edit
DataTable dt_MatPlan = dalMatPlan.Select();
if (frmInOutEdit.TrfSuccess)
{
foreach (DataRow row in dt.Rows)
{
string from = row[headerFrom].ToString();
float preparingQty = float.TryParse(row[headerQty].ToString(), out float i) ? Convert.ToSingle(row[headerQty].ToString()) : 0;
string to = row[headerTo].ToString();
string matCode = row[headerMatCode].ToString();
foreach (DataRow mat in dt_MatPlan.Rows)
{
bool active = Convert.ToBoolean(mat[dalMatPlan.Active]);
if (active)
{
float checkingQty = float.TryParse(mat[dalMatPlan.Prepare].ToString(), out float j) ? Convert.ToSingle(mat[dalMatPlan.Prepare].ToString()) : 0;
bool match = true;
if (matCode != mat[dalMatPlan.MatCode].ToString())
{
match = false;
}
else if (from != mat[dalMatPlan.MatFrom].ToString())
{
match = false;
}
else if (to != mat[dalMac.MacLocation].ToString())
{
match = false;
}
else if (preparingQty != checkingQty)
{
match = false;
}
if (match)
{
//get plan id
int PlanID = int.TryParse(mat[dalMatPlan.PlanID].ToString(), out int k) ? Convert.ToInt32(mat[dalMatPlan.PlanID]) : 0;
float Transferred = float.TryParse(mat[dalMatPlan.Transferred].ToString(), out float l) ? Convert.ToSingle(mat[dalMatPlan.Transferred].ToString()) : 0;
uMatPlan.mat_code = matCode;
uMatPlan.plan_id = PlanID;
uMatPlan.mat_transferred = preparingQty + Transferred;
uMatPlan.mat_preparing = 0;
uMatPlan.mat_from = "";
uMatPlan.updated_date = DateTime.Now;
uMatPlan.updated_by = MainDashboard.USER_ID;
if (!dalMatPlan.MatPrepareUpdate(uMatPlan))
{
MessageBox.Show("Failed to update material preparing data.");
}
}
}
}
}
itemChecking = false;
btnItemCheck.Text = "ITEM CHECK";
btnAutoInOut.Visible = false;
dgvDeliver.Columns[headerCheck].Visible = false;
cbCheckAll.Checked = false;
cbCheckAll.Visible = false;
dtpTrfDate.Visible = false;
LoadDeliverData();
}
}
}
private System.Data.DataTable taxDetails4Print(List<invoiceoutdetail> IODetails, string documentID)
{
int HSNLength = 0;
System.Data.DataTable dt = new System.Data.DataTable();
System.Data.DataTable dtTax = new System.Data.DataTable();
try
{
if (documentID == "PRODUCTINVOICE")
{
HSNLength = 4;
}
else if (documentID == "SERVICEINVOICE")
{
HSNLength = 6;
}
{
dt.Columns.Add("HSNCode", typeof(string));
dt.Columns.Add("TaxCode", typeof(string));
dt.Columns.Add("Amount", typeof(double));
dt.Columns.Add("TaxAmount", typeof(double));
dt.Columns.Add("TaxItem", typeof(string));
dt.Columns.Add("TaxItemPercentage", typeof(double));
dt.Columns.Add("TaxItemAmount", typeof(double));
}
//fill hsn code wise tax details in dt
foreach (invoiceoutdetail iod in IODetails)
{
string tstr = iod.TaxDetails;
string[] lst1 = tstr.Split('\n');
for (int j = 0; j < lst1.Length - 1; j++)
{
string[] lst2 = lst1[j].Split('-');
if (Convert.ToDouble(lst2[1]) > 0)
{
dt.Rows.Add();
dt.Rows[dt.Rows.Count - 1][0] = iod.HSNCode.Substring(0, HSNLength);
dt.Rows[dt.Rows.Count - 1][1] = iod.TaxCode;
dt.Rows[dt.Rows.Count - 1][2] = iod.Quantity * iod.Price;
dt.Rows[dt.Rows.Count - 1][3] = iod.Tax;
dt.Rows[dt.Rows.Count - 1][4] = lst2[0];
dt.Rows[dt.Rows.Count - 1][5] = iod.HSNCode; //need to replace with percentage
dt.Rows[dt.Rows.Count - 1][6] = lst2[1];
}
}
}
}
catch (Exception ex)
{
}
try
{
//fill tax rate for each tax item in dt
TaxCodeWorkingDB tcwdb = new TaxCodeWorkingDB();
List<taxcodeworking> tcwDetails = tcwdb.getTaxCodeDetails();
for (int i = 0; i < (dt.Rows.Count); i++)
{
foreach (taxcodeworking tcwd in tcwDetails)
{
if (dt.Rows[i][1].ToString() == tcwd.TaxCode && dt.Rows[i][4].ToString() == tcwd.TaxItemName)
{
dt.Rows[i][5] = tcwd.OperatorValue;
break;
}
}
}
//prepare HSN Code wise totals in a new table
System.Data.DataTable dttotal = new System.Data.DataTable();
dttotal = dt.Copy();
dttotal.Clear();
for (int i = 0; i < (dt.Rows.Count); i++)
{
Boolean fount = false;
string tstr1 = dt.Rows[i][0].ToString();
string tstr2 = dt.Rows[i][4].ToString();
string tstr3 = dt.Rows[i][5].ToString();
for (int j = 0; j < (dttotal.Rows.Count); j++)
{
string tstr4 = dttotal.Rows[j][0].ToString();
string tstr5 = dttotal.Rows[j][4].ToString();
string tstr6 = dttotal.Rows[j][5].ToString();
if (tstr1 == tstr4 && tstr2 == tstr5 && tstr3 == tstr6)
{
dttotal.Rows[j][2] = Convert.ToDouble(dttotal.Rows[j][2].ToString()) +
Convert.ToDouble(dt.Rows[i][2].ToString());
dttotal.Rows[j][6] = Convert.ToDouble(dttotal.Rows[j][6].ToString()) +
Convert.ToDouble(dt.Rows[i][6].ToString());
fount = true;
}
}
if (!fount)
{
dttotal.ImportRow(dt.Rows[i]);
}
}
string tstr = "";
////for (int i = 0; i < (dttotal.Rows.Count); i++)
////{
//// tstr = tstr+
//// dttotal.Rows[i][0].ToString() + "," +
//// dttotal.Rows[i][1].ToString() + "," +
//// dttotal.Rows[i][2].ToString() + "," +
//// dttotal.Rows[i][3].ToString() + "," +
//// dttotal.Rows[i][4].ToString() + "," +
//// dttotal.Rows[i][5].ToString() + "," +
//// dttotal.Rows[i][6].ToString() + "\n";
////}
////MessageBox.Show(tstr);
//create print table
tstr = "";
//find distinct tax item in dttotal
DataTable dtDistinct = dttotal.AsEnumerable().GroupBy(row => row.Field<string>("TaxItem")).Select(group => group.First()).CopyToDataTable();
////for (int i = 0; i < (dtDistinct.Rows.Count); i++)
////{
//// tstr = tstr +
//// dtDistinct.Rows[i][0].ToString() + "," +
//// dtDistinct.Rows[i][1].ToString() + "," +
//// dtDistinct.Rows[i][2].ToString() + "," +
//// dtDistinct.Rows[i][3].ToString() + "," +
//// dtDistinct.Rows[i][4].ToString() + "," +
//// dtDistinct.Rows[i][5].ToString() + "," +
//// dtDistinct.Rows[i][6].ToString() + "\n";
////}
////MessageBox.Show(tstr);
//create columns in dttax table. dynamically creating the columns for each tax item
{
dtTax.Columns.Add("HSNCode", typeof(string));
dtTax.Columns.Add("Amount", typeof(double));
for (int i = 0; i < dtDistinct.Rows.Count && i < 3; i++)
{
dtTax.Columns.Add(dtDistinct.Rows[i][4].ToString(), typeof(string));
dtTax.Columns.Add(dtDistinct.Rows[i][4].ToString() + "Amount", typeof(double));
}
dtTax.Columns.Add("Total", typeof(double));
}
//add data in dttax table
for (int i = 0; i < (dttotal.Rows.Count); i++)
{
Boolean hsnFount = false;
string tstr1 = dttotal.Rows[i][0].ToString(); //for domestic
int j = 0;
for (j = 0; j < (dtTax.Rows.Count); j++)
{
string tstr2 = dtTax.Rows[j][0].ToString(); //for domestic
if (tstr1 == tstr2)
{
hsnFount = true;
break;
}
}
if (!hsnFount)
{
dtTax.Rows.Add();
j = dtTax.Rows.Count - 1;
dtTax.Rows[j][0] = tstr1;
dtTax.Rows[j][1] = dttotal.Rows[i][2]; ;
}
string tstr3 = dttotal.Rows[i][4].ToString();
string tstr4 = dttotal.Rows[i][4].ToString() + "Amount";
try
{
dtTax.Rows[j][tstr3] = dttotal.Rows[i][5];
dtTax.Rows[j][tstr4] = dttotal.Rows[i][6];
string t1 = String.IsNullOrEmpty(dtTax.Rows[j]["Total"].ToString()) ? "0" : dtTax.Rows[j]["Total"].ToString();
string t2 = String.IsNullOrEmpty(dttotal.Rows[i][6].ToString()) ? "0" : dttotal.Rows[i][6].ToString();
double d1 = Convert.ToDouble(t1) + Convert.ToDouble(t2);
dtTax.Rows[j]["Total"] = d1;
}
catch (Exception ex)
{
MessageBox.Show("Error creating HSN wise tax summary");
}
}
tstr = "";
////double td1=0, td2=0, td3 = 0;
////for (int i = 0; i < (dtTax.Rows.Count); i++)
////{
//// for (int j=0; j<dtTax.Columns.Count;j++)
//// {
//// tstr = tstr + dtTax.Rows[i][j].ToString() + ",";
//// }
//// tstr = tstr+"\n";
//// td1 = td1 + Convert.ToDouble(dtTax.Rows[i][1].ToString());
//// td2 = td2 + Convert.ToDouble(dtTax.Rows[i][dtTax.Columns.Count-1].ToString());
////}
////MessageBox.Show(tstr);
////MessageBox.Show(td1.ToString());
////MessageBox.Show(td2.ToString());
}
catch (Exception ex)
{
MessageBox.Show("taxDetails4Print() : Error - " + ex.ToString());
}
return dtTax;
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="dt"></param>
/// <param name="head"></param>
/// <param name="absFileName"></param>
public static void ExportToExcelForTwo(System.Data.DataTable dt, string[] head, string absFileName)
{
//设置多少行为一个Sheet
int RowsToDivideSheet = 65535;
//计算Sheet数
int sheetCount = (dt.Rows.Count - 1) / RowsToDivideSheet + 1;
GC.Collect();
Application excel = null;
_Workbook xBk = null;
_Worksheet xSt = null;
try
{
excel = new ApplicationClass();
xBk = excel.Workbooks.Add(true);
//excel.Visible = true;
//循环中要使用的变量
int dvRowStart;
int dvRowEnd;
//对全部Sheet进行操作
for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++)
{
//计算起始行
dvRowStart = sheetIndex * RowsToDivideSheet;
dvRowEnd = dvRowStart + RowsToDivideSheet - 1;
if (dvRowEnd > dt.Rows.Count - 1)
{
dvRowEnd = dt.Rows.Count - 1;
}
//创建一个Sheet
if (null == xSt)
{
xSt = (_Worksheet)xBk.Worksheets.Add(Type.Missing, Type.Missing, 1, Type.Missing);
}
else
{
xSt = (_Worksheet)xBk.Worksheets.Add(Type.Missing, xSt, 1, Type.Missing);
}
//设置SheetName
xSt.Name = "Excel";
if (sheetCount > 1)
{
xSt.Name += ((int)(sheetIndex + 1)).ToString();
}
//题头导出
int rowCount = head.Length;
int colCount = 1;
object[,] dataArray = new object[colCount, rowCount];
for (int i = 0; i < rowCount; i++)
{
for (int j = 0; j < colCount; j++)
{
dataArray[j, i] = head[i];
}
}
xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).NumberFormatLocal = "@"; //设置单元格格式为文本
xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).Value2 = dataArray;
xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).Font.Name = "Arial";
xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).Font.Size = 10;
//数据导出
System.Data.DataTable SheetTable = new System.Data.DataTable();
foreach (DataColumn dc in dt.Columns)
{
DataColumn newdc = new DataColumn();
newdc.ColumnName = dc.ColumnName;
newdc.DataType = dc.DataType;
SheetTable.Columns.Add(newdc);
}
for (int drvIndex = dvRowStart; drvIndex <= dvRowEnd; drvIndex++)
{
SheetTable.ImportRow(dt.Rows[drvIndex]);
}
//保存数据
int MRProw = -1;//记忆MRP所在行号
rowCount = SheetTable.Rows.Count;
colCount = SheetTable.Columns.Count;
object[,] dataArray1 = new object[rowCount, colCount];
excel.DisplayAlerts = false;
for (int i = 0; i < rowCount; i++)
{
for (int j = 0; j < colCount; j++)
{
dataArray1[i, j] = SheetTable.Rows[i][j];
}
if (SheetTable.Rows[i][1].ToString().Length == 0)
{
dataArray1[i, 1] = SheetTable.Rows[i][2];
SheetTable.Rows[i][1] = SheetTable.Rows[i][2];
xSt.get_Range(xSt.Cells[i + 2, 2], xSt.Cells[i + 2, 3]).Merge(Type.Missing);
}
}
xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).Value2 = dataArray1;
int start = 0;
int start1 = 0;
string strstart = SheetTable.Rows[start][0].ToString();
string strstart1 = SheetTable.Rows[start1][1].ToString();
double num1, num2;
for (int i = 0; i < rowCount; i++)
{
if (strstart.Trim().ToUpper() != SheetTable.Rows[i][0].ToString().Trim().ToUpper())
{
xSt.get_Range(xSt.Cells[start + 2, 1], xSt.Cells[i + 1, 1]).Merge(Type.Missing);
start = i;
strstart = SheetTable.Rows[start][0].ToString();
}
if (SheetTable.Rows[i][1].ToString().CompareTo("MRP") == 0)
{
MRProw = i;
}
if (SheetTable.Rows[i][1].ToString().Contains("Promised Capacity"))
{
for (int j = 3; j < colCount; j++)
{
if (Convert.ToDouble(SheetTable.Rows[MRProw][j].ToString()) > Convert.ToDouble(SheetTable.Rows[i][j].ToString()))
{
xSt.get_Range(xSt.Cells[i + 2, j + 1], xSt.Cells[i + 2, j + 1]).Interior.ColorIndex = 3;
}
}
}
if (SheetTable.Rows[i][1].ToString().Contains("Bottleneck Capacity"))
{
for (int j = 3; j < colCount; j++)
{
if (Convert.ToDouble(SheetTable.Rows[MRProw][j].ToString()) > Convert.ToDouble(SheetTable.Rows[i][j].ToString()))
{
xSt.get_Range(xSt.Cells[i + 2, j + 1], xSt.Cells[i + 2, j + 1]).Interior.ColorIndex = 3;
}
}
}
if (strstart1.Trim().ToUpper() != SheetTable.Rows[i][1].ToString().Trim().ToUpper())
{
xSt.get_Range(xSt.Cells[start1 + 2, 2], xSt.Cells[i + 1, 2]).Merge(Type.Missing);
start1 = i;
strstart1 = SheetTable.Rows[start1][1].ToString();
}
}
if (start != rowCount)
{
xSt.get_Range(xSt.Cells[start + 2, 1], xSt.Cells[rowCount + 1, 1]).Merge(Type.Missing);
}
if (start1 != rowCount)
{
xSt.get_Range(xSt.Cells[start1 + 2, 2], xSt.Cells[rowCount + 1, 2]).Merge(Type.Missing);
}
excel.DisplayAlerts = true;
xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).NumberFormatLocal = "0.00_ "; //保留小数位数为2;
xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).Font.Name = "Arial";
xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).Font.Size = 10;
//单元格边框
xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[rowCount + 1, colCount]).Borders.LineStyle = 1;
xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[rowCount + 1, colCount]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; //设置居中对齐
xSt.Columns.AutoFit(); //自适应宽度
}
//删除Sheet1
excel.DisplayAlerts = false; //注意一定要加上这句
((Microsoft.Office.Interop.Excel.Worksheet)xBk.Worksheets["Sheet1"]).Delete();
excel.DisplayAlerts = true; //注意一定要加上这句
object objOpt = System.Reflection.Missing.Value;
excel.Visible = false;
xBk.SaveCopyAs(absFileName);
xBk.Close(false, null, null);
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
xBk = null;
excel = null;
xSt = null;
}
catch (Exception e)
{
//throw (e);
xBk.Close(false, null, null);
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
xBk = null;
excel = null;
xSt = null;
}
}
/*<summary>
* 将DataTable中的数据导出到Excel中，并在服务器端AppData文件夹中生成xls文件
* </summary>
* <param name="dt">要导出数据的DataTable</param>
* <param name="head">题头数据</param>
* <param name="absFileName">文件的绝对路径</param>
* <returns></returns>
*/
public static void ExportToExcelDiagramfor9(System.Data.DataTable dt, string[] head, string absFileName)
{
//设置多少行为一个Sheet
int RowsToDivideSheet = 65535;
//计算Sheet数
int sheetCount = (dt.Rows.Count - 1) / RowsToDivideSheet + 1;
GC.Collect();
string excelTemplateDPath; //EXCEL模板默认服务器物理存放路径
string tempEFilePath = "\\TempFiles"; //EXCEL临时文件保存服务器物理存放路径
string tempEFileXPath = "/TempFiles"; //EXCEL临时文件保存服务器虚拟存放路径
Excel.Application excel = null;
Excel._Workbook xBk = null;
Excel._Worksheet xSt = null;
Excel.Workbooks workbooks; //工作簿集合
Excel.Sheets sheets; //SHEET页集合
#region
//读取配置文件中路线模板路径及名称并验证是否存在 //获取配置文件中路线模板路径及名称
excelTemplateDPath = System.Web.HttpContext.Current.Server.MapPath("~/moban");
tempEFileXPath = "~/moban" + tempEFileXPath;
tempEFilePath = excelTemplateDPath + tempEFilePath;
excelTemplateDPath += "\\TaskTemplet9.xls";
//验证EXCEL临时文件夹是否存在
if (!File.Exists(tempEFilePath))
{
Directory.CreateDirectory(tempEFilePath);
}
#endregion
try
{
#region
//启动excel进程并加载模板
//启动EXCEL进程
excel = new Excel.Application();
excel.Visible = false;
excel.UserControl = true;
excel.DisplayAlerts = false;
//加载读取模板
workbooks = excel.Workbooks;
xBk = workbooks.Add(excelTemplateDPath);
sheets = xBk.Worksheets;
xSt = (Excel._Worksheet)sheets.get_Item(1);
#endregion
//循环中要使用的变量
int dvRowStart;
int dvRowEnd;
//对全部Sheet进行操作
for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++)
{
//计算起始行
dvRowStart = sheetIndex * RowsToDivideSheet;
dvRowEnd = dvRowStart + RowsToDivideSheet - 1;
if (dvRowEnd > dt.Rows.Count - 1)
{
dvRowEnd = dt.Rows.Count - 1;
}
//创建一个Sheet
if (null == xSt)
{
xSt = (Excel._Worksheet)xBk.Worksheets.Add(Type.Missing, Type.Missing, 1, Type.Missing);
}
else
{
xSt = (Excel._Worksheet)xBk.Worksheets.Add(Type.Missing, xSt, 1, Type.Missing);
}
//设置SheetName
xSt.Name = "Excel";
if (sheetCount > 1)
{
xSt.Name += ((int)(sheetIndex + 1)).ToString();
}
//题头导出
int rowCount = head.Length;
int colCount = 1;
object[,] dataArray = new object[colCount, rowCount];
for (int i = 0; i < rowCount; i++)
{
for (int j = 0; j < colCount; j++)
{
dataArray[j, i] = head[i];
}
}
xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).NumberFormatLocal = "@"; //设置单元格格式为文本
xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).Value2 = dataArray;
xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).Font.Name = "Arial";
xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).Font.Size = 10;
//数据导出
System.Data.DataTable SheetTable = new System.Data.DataTable();
foreach (DataColumn dc in dt.Columns)
{
DataColumn newdc = new DataColumn();
newdc.ColumnName = dc.ColumnName;
newdc.DataType = dc.DataType;
SheetTable.Columns.Add(newdc);
}
for (int drvIndex = dvRowStart; drvIndex <= dvRowEnd; drvIndex++)
{
SheetTable.ImportRow(dt.Rows[drvIndex]);
}
//保存数据
rowCount = SheetTable.Rows.Count;
colCount = SheetTable.Columns.Count;
object[,] dataArray1 = new object[rowCount, colCount];
for (int i = 0; i < rowCount; i++)
{
for (int j = 0; j < colCount; j++)
{
dataArray1[i, j] = SheetTable.Rows[i][j];
}
}
xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).NumberFormatLocal = "0.00_ "; //保留小数位数为2;
xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).Value2 = dataArray1;
xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).Font.Name = "Arial";
xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).Font.Size = 10;
//单元格边框
xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[rowCount + 1, colCount]).Borders.LineStyle = 1;
xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[rowCount + 1, colCount]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; //设置居中对齐
xSt.Columns.AutoFit(); //自适应宽度
//设置图标的标题
string Charttitle = dt.Rows[0][0].ToString();
}
//删除Sheet1
excel.DisplayAlerts = false; //注意一定要加上这句
((Microsoft.Office.Interop.Excel.Worksheet)xBk.Worksheets["Sheet1"]).Delete();
excel.DisplayAlerts = true; //注意一定要加上这句
object objOpt = System.Reflection.Missing.Value;
excel.Visible = false;
xBk.SaveCopyAs(absFileName);
xBk.Close(false, null, null);
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
xBk = null;
excel = null;
xSt = null;
}
catch (Exception e)
{
//throw (e);
xBk.Close(false, null, null);
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
xBk = null;
excel = null;
xSt = null;
}
}
/*<summary>
* 将DataTable中的数据导出到Excel中，并在服务器端AppData文件夹中生成xls文件
* </summary>
* <param name="dt">要导出数据的DataTable</param>
* <param name="head">题头数据</param>
* <param name="absFileName">文件的绝对路径</param>
* <returns></returns>
*/
public static void ExportToExcel(System.Data.DataTable dt, string[] head, string absFileName)
{
//设置多少行为一个Sheet
int RowsToDivideSheet = 65535;
//计算Sheet数
int sheetCount = (dt.Rows.Count - 1) / RowsToDivideSheet + 1;
GC.Collect();
Application excel = null;
_Workbook xBk = null;
_Worksheet xSt = null;
try
{
excel = new ApplicationClass();
xBk = excel.Workbooks.Add(true);
//excel.Visible = true;
//循环中要使用的变量
int dvRowStart;
int dvRowEnd;
//对全部Sheet进行操作
for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++)
{
//计算起始行
dvRowStart = sheetIndex * RowsToDivideSheet;
dvRowEnd = dvRowStart + RowsToDivideSheet - 1;
if (dvRowEnd > dt.Rows.Count - 1)
{
dvRowEnd = dt.Rows.Count - 1;
}
//创建一个Sheet
if (null == xSt)
{
xSt = (_Worksheet)xBk.Worksheets.Add(Type.Missing, Type.Missing, 1, Type.Missing);
}
else
{
xSt = (_Worksheet)xBk.Worksheets.Add(Type.Missing, xSt, 1, Type.Missing);
}
//设置SheetName
xSt.Name = "Excel";
if (sheetCount > 1)
{
xSt.Name += ((int)(sheetIndex + 1)).ToString();
}
//题头导出
int rowCount = head.Length;
int colCount = 1;
object[,] dataArray = new object[colCount, rowCount];
for (int i = 0; i < rowCount; i++)
{
for (int j = 0; j < colCount; j++)
{
dataArray[j, i] = head[i];
}
}
xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).NumberFormatLocal = "@"; //设置单元格格式为文本
xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).Value2 = dataArray;
xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).Font.Name = "Arial";
xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).Font.Size = 10;
//数据导出
System.Data.DataTable SheetTable = new System.Data.DataTable();
foreach (DataColumn dc in dt.Columns)
{
DataColumn newdc = new DataColumn();
newdc.ColumnName = dc.ColumnName;
newdc.DataType = dc.DataType;
SheetTable.Columns.Add(newdc);
}
for (int drvIndex = dvRowStart; drvIndex <= dvRowEnd; drvIndex++)
{
SheetTable.ImportRow(dt.Rows[drvIndex]);
}
//保存数据
rowCount = SheetTable.Rows.Count;
colCount = SheetTable.Columns.Count;
object[,] dataArray1 = new object[rowCount, colCount];
for (int i = 0; i < rowCount; i++)
{
for (int j = 0; j < colCount; j++)
{
dataArray1[i, j] = SheetTable.Rows[i][j];
}
}
//注释掉下句可以解决无法读取时间的格式的问题
//xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).NumberFormatLocal = "0"; //保留小数位数为2;
xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).Value2 = dataArray1;
xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).Font.Name = "Arial";
xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).Font.Size = 10;
//单元格边框
xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[rowCount + 1, colCount]).Borders.LineStyle = 1;
xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[rowCount + 1, colCount]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; //设置居中对齐
xSt.Columns.AutoFit(); //自适应宽度
}
//删除Sheet1
excel.DisplayAlerts = false; //注意一定要加上这句
((Microsoft.Office.Interop.Excel.Worksheet)xBk.Worksheets["Sheet1"]).Delete();
excel.DisplayAlerts = true; //注意一定要加上这句
object objOpt = System.Reflection.Missing.Value;
excel.Visible = false;
xBk.SaveCopyAs(absFileName);
xBk.Close(false, null, null);
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
xBk = null;
excel = null;
xSt = null;
}
catch (Exception e)
{
//throw (e);
xBk.Close(false, null, null);
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
xBk = null;
excel = null;
xSt = null;
}
}
