Example #1
10
    /// <summary>
    /// ค้นหาข้อความใน DataTable
    /// </summary>
    /// <param name="dtSearch">DataTable ที่ต้องการค้นหา</param>
    /// <param name="strWord">คำที่ต้องการค้นหา</param>
    /// <param name="strField">Field ที่ต้องการค้นหา</param>
    /// <returns>True=พบ , False=ไม่พบ</returns>
    /// <remarks>2013-08-16</remarks>
    public bool Search(DataTable dtSearch, string strWord, string strField)
    {
        bool rtnBool = false;

        #region LINQ
        var varResult = (from word in dtSearch.AsEnumerable()
                         where word[strField].ToString() == strWord
                         select word);
        if (varResult != null && varResult.Count() > 0)
        {
            DataTable dtResult = new DataTable();
            dtResult = varResult.CopyToDataTable();
            rtnBool = true;
        }
        #endregion

        return rtnBool;
    }
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {

            var dsgetMenu = CacheUtils.GetCacheFromFile("dsGetMenu" + Session["userid"]) as DataSet;
            if (dsgetMenu == null) return;
            DataView dvgetMenu = new DataView(dsgetMenu.Tables[0]);
            dtMenu = dsgetMenu.Tables[0];
            dvgetMenu.RowFilter = string.Format("[parentid] IS NULL");
            dtRoot = dvgetMenu.ToTable();

            StringBuilder sb = new StringBuilder();
            string urlWithSessionID = "~" + Response.ApplyAppPathModifier(Request.Url.PathAndQuery);
            GetMenuHTML(ref sb, dtRoot.AsEnumerable().ToList(), urlWithSessionID);

            HTMLMenu = sb.ToString();
        }
    }
Example #3
1
    protected void Page_Load(object sender, EventArgs e)
    {
        bindbullTable(null, 20);
        if (!IsPostBack)
        {
            
                DataTable fy = new DataTable();
                fy = uph.GetLine1();
                var q = from r in fy.AsEnumerable()
                        select new ListItem(r[0].ToString());

                string FY = fy.ToString();
                this.DL_PdLine.Items.AddRange(q.ToArray());
                bindbullTable(null, 20);
        }
        DataTable sn = new DataTable();
        sn = uph.GetAlarmALL();
        bindbullTable(sn, 5);

    }
Example #4
1
    /// <summary>
    /// 安节点排序,并格式化点组的 IPC 如1点组,2点组
    /// </summary>
    /// <param name="dt"></param>
    /// <returns></returns>
    public static DataTable FormatTable(DataTable dt)
    {
        if (dt.Rows.Count > 0)
        {
            var x = from y in dt.AsEnumerable()
                    orderby y["NodeId"] ascending
                    select y;

            DataTable dtreuslt = x.CopyToDataTable();
            foreach (DataRow row in dtreuslt.Rows)
            {
                int tmplive = Convert.ToInt32(row["live"]);
                if (tmplive > 3)
                {
                    row["des"] = "<b>" + "".PadLeft(tmplive - 3, '.') + "</b>" + row["des"].ToString();
                }
            }
            return dtreuslt;
        }
        else
        {
            return dt;
        }
    }
    protected void Page_Load(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection(WebConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString);
        SqlCommand cmd = new SqlCommand();
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "select * from Products";
        cmd.Connection = con;
        SqlDataAdapter adapter = new SqlDataAdapter();
        adapter.SelectCommand = cmd;
        DataSet dataSet = new DataSet();
        try
        {
            con.Open();
            adapter.Fill(dataSet, "Products");
            var result = from item in dataSet.Tables["Products"].AsEnumerable()
                         where item.Field<int>("ProductID") == 826388
                         select new { ProductName = item.Field<string>("ProductName"), UnitPrice = item.Field<decimal>("UnitPrice"), UnitsInStock = item.Field<int>("UnitsInStock") };
            GridView1.DataSource = result;
            GridView1.DataBind();
        }
        catch (Exception)
        {
            throw new ApplicationException("Data error");
        }
        finally
        {
            con.Close();
        }

        DataTable tab = new DataTable();
        tab.Columns.Add("FileName", typeof(string));
        tab.Columns.Add("FileSize", typeof(Int64));
        tab.Columns.Add("CreationTime", typeof(DateTime));

        DataRow row = null;

        DirectoryInfo dirInfo = new DirectoryInfo(@"C:\");
        foreach (var item in dirInfo.GetFiles())
        {
            row = tab.NewRow();
            row[0] = item.Name;
            row[1] = item.Length;
            row[2] = item.CreationTime;
            tab.Rows.Add(row);
        }

        var resultFiles = from item in tab.AsEnumerable()
                          where item.Field<Int64>("FileSize") > 10
                          select new { FileName = item.Field<string>("FileName"), FileSize = item.Field<Int64>("FileSize"), CreationTime = item.Field<DateTime>("CreationTime") };
        GridView2.DataSource = resultFiles;
        GridView2.DataBind();

        XDocument xmlFile = XDocument.Load(@"C:\test.xml");
        var resultXml = from item in xmlFile.Descendants("Product")
                        select new
                        {
                            ProductName = item.Element("ProductName").Value,
                            Cost = item.Element("Cost").Value
                        };
        DataTable table = new DataTable();
        table.Columns.Add("ProductName", typeof(string));
        table.Columns.Add("Cost", typeof(int));

        DataRow RowX = null;
        foreach (var item in resultXml)
        {
            RowX = table.NewRow();
            RowX[0] = item.ProductName;
            RowX[1] = item.Cost;
            table.Rows.Add(RowX);
        }

        var finalOut = from item in table.AsEnumerable()
                       where item.Field<int>("Cost") > 10
                       select new { ProductName = item.Field<string>("ProductName"), Cost = item.Field<int>("Cost") };

        GridView3.DataSource = finalOut;
        GridView3.DataBind();

        JoinQuery();
        StronglyTypedDataSet();
        DataViewDemo();
    }
 public static IEnumerable<DataRow> Intersect(this DataTable table, DataTable other)
 {
     return table.AsEnumerable().Intersect(other.AsEnumerable());
 }
    public static ArrayList GetAutoCompleteData(string username)
    {
        ArrayList result = new ArrayList();
        if (HttpContext.Current.Session["downline"] != null)
        {
            DataTable dtSelect = new DataTable();
            dtSelect = (DataTable) HttpContext.Current.Session["downline"];
            List<string> list =  (from a in dtSelect.AsEnumerable().Where(a => a["NAME"].ToString().StartsWith(username)) select a.Field<string>("NAME")).ToList();
            //List<string> list = (from row in dtSelect.AsEnumerable().Where(row=>row["NAME"].ToString().StartsWith(username))).ToList(); //select row.Field<string>("NAME")).ToList();

            foreach (DataRow dr in dtSelect.Rows)
            {
                if (dr["NAME"].ToString().ToLower().StartsWith(username))
                {
                    result.Add(dr["NAME"]);
                }
            }
        }
        return result;
    }
    public String recoverPassword(String SendtoAddress)
    {
        String fromAddress = "*****@*****.**";
        String toAddress = SendtoAddress;
        const String password = "******";
        const String subject = "Retrieving Password";
        String query = String.Format("SELECT u.u_name, u.passd FROM UserAcc AS u, Candidate AS c WHERE c.email= '{0}' AND u.u_id=c.u_id", SendtoAddress);
        DataTable myTable = new DataTable();
        DBConnection myConnection = new DBConnection();
        myConnection.connect("LILY-PC", "projectdb1", "admin", "123");
        myTable = myConnection.executeReturningQuery(query);
        DataRow[] rowArray = myTable.AsEnumerable().Take(1).ToArray();
        object[] objectArray = rowArray[0].ItemArray;
        String[] stringArray = Array.ConvertAll(objectArray, (p => Convert.ToString(p)));
        String body = stringArray[0] + stringArray[1];

        try
        {
            SmtpClient smtp = new SmtpClient
            {
                Host = "smtp.gmail.com",
                Port = 587,
                EnableSsl = true,
                DeliveryMethod = SmtpDeliveryMethod.Network,
                Credentials = new NetworkCredential(fromAddress, password),
                Timeout = 3000
            };
            MailMessage message = new MailMessage(fromAddress, toAddress, subject, body);
            smtp.Send(message);
        }
        catch (Exception ex)
        {
        }
        return stringArray[0];
    }
Example #9
1
    /// <summary>
    /// Top like operation
    /// </summary>
    private DataTable GetTopRows(DataTable dt, int top)
    {
        if (top == -1) return dt;

        List<DataRow> listRows = dt.AsEnumerable().Take(top).ToList();

        DataTable newDt = dt.Clone();

        foreach (DataRow row in listRows)
            newDt.Rows.Add(row.ItemArray);

        return newDt;
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        /* --- initialize the DataTable named table as a temp --- */
        DataTable table = new DataTable();

        //copying the gridview into the datatable
        GridViewToDataTable(GridView2, table);

        /* --- assign the start and end time from gridview which is populated by DB --- */
        DateTime startDate = Convert.ToDateTime(GridView2.Rows[0].Cells[0].Text);
        DateTime endDate = Convert.ToDateTime(GridView2.Rows[(GridView2.Rows.Count)-1].Cells[0].Text);

        bool showChartDesc = false;

        //trying to get the values from those two input fields and convert them to the proper date
        try
        {
            startDate = Convert.ToDateTime(startDateTextBox.Text);
            endDate = Convert.ToDateTime(endDateTextBox.Text);
            showChartDesc = true;
        }
        catch (Exception exp)
        {
            Label2.CssClass = "errorText";
            Label2.Text = exp.Message + "<br /> The input was invalid. You have to select a date from " + startDate.ToLongDateString() + " to " + endDate.ToLongDateString();
        }

        // querying on datatable for selecting only the rows which have the in range timestamp.
        var query = (from x in table.AsEnumerable()
                     where startDate <= x.Field<DateTime>("timeStamp") && x.Field<DateTime>("timeStamp") <= endDate
                     select x);

        // Drawing th Chart
        Chart1.CssClass = "showGraph";
        Chart1.DataSource = query;
        Chart1.DataBind();

        // Showing the search result
        GridView2.DataSource = query.AsDataView();
        GridView2.DataBind();

        Chart1.Series["Series1"].XValueMember = "TimeStamp";
        Chart1.Series["Series1"].YValueMembers = YDropDown.SelectedValue;

        Chart1.ChartAreas["ChartArea1"].AxisY.Title = YDropDown.SelectedValue + " (KW.h)";

        //if (Chart1.Series["Series1"].XValueMember.Contains((Convert.ToDateTime("06/05/2012")).ToString()))
        //Chart1.ChartAreas["ChartArea1"].AxisX.Title = Chart1.Series["Series1"].

        if (showChartDesc)
        {
            Label2.CssClass = "normalText";
            Label2.Text = "The Chart below shows the value of - " + YDropDown.SelectedItem.Text + " - from the Register of the " + tableReading.SelectedItem.Text + " from the Sub-Group of the New Main LV Switch Board in the main group of the Metering of Electric, Gas, Water, Heat, and Steam from the Kilburn Building. You have chosen " +
                startDate.ToLongDateString() + " as your start date and " + endDate.ToLongDateString() +" as your final date" ;
        }
    }
        private IEnumerable<DataRow> LoadDataTable(string sql, Func<IEnumerable<DataRow>, IEnumerable<DataRow>> orderByFunc, DataTable table, EntityStoreSchemaFilterObjectTypes queryTypes, IEnumerable<EntityStoreSchemaFilterEntry> filters, params string[] filterAliases)
        {
            using (EntityCommand command = CreateFilteredCommand(_connection, sql, null, queryTypes, new List<EntityStoreSchemaFilterEntry>(filters), filterAliases))
            {
                using (DbDataReader reader = command.ExecuteReader(CommandBehavior.SequentialAccess))
                {
                    object[] values = new object[table.Columns.Count];
                    while (reader.Read())
                    {
                        reader.GetValues(values);
                        table.Rows.Add(values);
                    }

                    return orderByFunc(table.AsEnumerable());
                }
            }
        }
    private void cargarSemaforos()
    {
        contratosServicio _contratosServicio = new contratosServicio(Session["idEmpresa"].ToString());
        DataTable tblcontratosServicio = new DataTable();

        tblcontratosServicio = _contratosServicio.ObtenerSemaforoContratosDeServico();
        int conteoBajo = (from s in tblcontratosServicio.AsEnumerable() where s["ALERTA"].ToString()=="BAJO" select s).Count();
        int conteoMedio = (from s in tblcontratosServicio.AsEnumerable() where s["ALERTA"].ToString()=="MEDIO" select s).Count();
        int conteoAlto = (from s in tblcontratosServicio.AsEnumerable() where s["ALERTA"].ToString()=="ALTO" select s).Count();

        var listaFinal = (from s in tblcontratosServicio.AsEnumerable() select new { ALERTA = s["ALERTA"].ToString(), contador = 0 });
        DataTable tablaFinal = new System.Data.DataTable();
        tablaFinal.Columns.Add("ALERTA");
        tablaFinal.Columns.Add("contador");

        foreach (var item in (from s in listaFinal orderby s.ALERTA select s).Distinct().ToList())
        {

            //if ((from s in tablaFinal.AsEnumerable() where s["ALERTA"] == item.ALERTA select s).Count() <= 0)
            //{
                DataRow fila = tablaFinal.NewRow();

                fila["ALERTA"] = item.ALERTA;
                switch (item.ALERTA.ToLower())
                {
                    case "bajo":
                        fila["contador"] = conteoBajo;
                        break;
                    case "medio":
                        fila["contador"] = conteoMedio;
                        break;
                    case "alto":
                        fila["contador"] = conteoAlto;
                        break;
                }

                tablaFinal.Rows.Add(fila);
            //}
        }

        DataView custDV = new DataView(tablaFinal);

        Display(custDV);
    }
Example #13
1
    /// <summary>
    /// Joins 2 DataTables แบบเลือกวิธี Join ได้
    /// <para>Returns an appropriate DataTable with zero rows if the colToJoinOn does not exist in both tables.</para>
    /// </summary>
    /// <param name="dtblLeft"></param>
    /// <param name="dtblRight"></param>
    /// <param name="colToJoinOn"></param>
    /// <param name="joinType">clsData.JoinType.Left หรือ clsData.JoinType.Inner</param>
    /// <returns></returns>
    /// <example>
    /// dt = clsData.JoinTwoDataTable(dtHoldList, dtDepartment, "dept_id", clsData.JoinType.Left);
    /// </example>
    /// <remarks>
    /// <para>http://stackoverflow.com/questions/2379747/create-combined-datatable-from-two-datatables-joined-with-linq-c-sharp?rq=1</para>
    /// <para>http://msdn.microsoft.com/en-us/library/vstudio/bb397895.aspx</para>
    /// <para>http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html</para>
    /// <para>http://stackoverflow.com/questions/406294/left-join-and-left-outer-join-in-sql-server</para>
    /// </remarks>
    public DataTable JoinTwoDataTable(DataTable dtblLeft, DataTable dtblRight, string colToJoinOn, JoinType joinType)
    {
        //Change column name to a temp name so the LINQ for getting row data will work properly.
        string strTempColName = colToJoinOn + "_2";
        if (dtblRight.Columns.Contains(colToJoinOn))
            dtblRight.Columns[colToJoinOn].ColumnName = strTempColName;

        //Get columns from dtblA
        DataTable dtblResult = dtblLeft.Clone();

        //Get columns from dtblB
        var dt2Columns = dtblRight.Columns.OfType<DataColumn>().Select(dc => new DataColumn(dc.ColumnName, dc.DataType, dc.Expression, dc.ColumnMapping));

        //Get columns from dtblB that are not in dtblA
        var dt2FinalColumns = from dc in dt2Columns.AsEnumerable()
                              where !dtblResult.Columns.Contains(dc.ColumnName)
                              select dc;

        //Add the rest of the columns to dtblResult
        dtblResult.Columns.AddRange(dt2FinalColumns.ToArray());

        //No reason to continue if the colToJoinOn does not exist in both DataTables.
        if (!dtblLeft.Columns.Contains(colToJoinOn) || (!dtblRight.Columns.Contains(colToJoinOn) && !dtblRight.Columns.Contains(strTempColName)))
        {
            if (!dtblResult.Columns.Contains(colToJoinOn))
                dtblResult.Columns.Add(colToJoinOn);
            return dtblResult;
        }

        switch (joinType)
        {

            default:
            case JoinType.Inner:
                #region Inner
                //get row data
                //To use the DataTable.AsEnumerable() extension method you need to add a reference to the System.Data.DataSetExtension assembly in your project. 
                var rowDataLeftInner = from rowLeft in dtblLeft.AsEnumerable()
                                       join rowRight in dtblRight.AsEnumerable() on rowLeft[colToJoinOn] equals rowRight[strTempColName]
                                       select rowLeft.ItemArray.Concat(rowRight.ItemArray).ToArray();


                //Add row data to dtblResult
                foreach (object[] values in rowDataLeftInner)
                    dtblResult.Rows.Add(values);

                #endregion
                break;
            case JoinType.Left:
                #region Left
                var rowDataLeftOuter = from rowLeft in dtblLeft.AsEnumerable()
                                       join rowRight in dtblRight.AsEnumerable() on rowLeft[colToJoinOn] equals rowRight[strTempColName] into gj
                                       from subRight in gj.DefaultIfEmpty()
                                       select rowLeft.ItemArray.Concat((subRight == null) ? (dtblRight.NewRow().ItemArray) : subRight.ItemArray).ToArray();


                //Add row data to dtblResult
                foreach (object[] values in rowDataLeftOuter)
                    dtblResult.Rows.Add(values);

                #endregion
                break;
        }

        //Change column name back to original
        dtblRight.Columns[strTempColName].ColumnName = colToJoinOn;

        //Remove extra column from result
        dtblResult.Columns.Remove(strTempColName);

        return dtblResult;
    }
    protected void btnSent_OnClick(object sender, EventArgs e)
    {
        try
        {
            DataTable dtCustomers = new DataTable();
            dtCustomers.Columns.AddRange(new DataColumn[2]
            {
                new DataColumn("Name", typeof (string)),
                new DataColumn("Email", typeof (string))
            });

            foreach (GridViewRow row in gvUsers.Rows)
            {
                if ((row.FindControl("chkSelect") as CheckBox).Checked)
                {
                    dtCustomers.Rows.Add(row.Cells[2].Text, (row.FindControl("lnkEmail") as HyperLink).Text);
                }
            }

            System.Threading.Tasks.Parallel.ForEach(dtCustomers.AsEnumerable(), row =>
            {
                SendEmail(txtSubject.Text, row["Email"].ToString());
            });
            Session["complete"] = "yes";
        }
        catch (Exception ex)
        {
            Session["error"] = "yes";
            Helper.LogException(Session["userid"].ToString(), "Email Automation",
                "Exception Type: " + ex.GetType().ToString() + " Message: " + ex.ToString());
        }

    }
Example #15
1
    //中奖明细对账文件生成通知
    private void AwardResultNotice(string TransMessage)
    {
        System.Xml.XmlDocument XmlDoc = new XmlDocument();
        XmlDoc.Load(new StringReader(TransMessage));

        string elements = TransMessage.Substring(TransMessage.IndexOf("<body"), TransMessage.LastIndexOf("</body>") - TransMessage.IndexOf("<body")) + "</body>";

        DataSet ds = new DataSet();

        ds.ReadXml(new StringReader(elements));

        if (ds == null)
        {
            return;
        }

        if (ds.Tables.Count == 0)
        {
            return;
        }

        DataTable dtFile = ds.Tables[0];

        if (dtFile.Rows.Count < 1)
        {
            return;
        }

        int LotteryID = 0;
        string IssueName = "";
        string FileName = "";

        string Body = "";

        DAL.Tables.T_Isuses t_Isuses = new DAL.Tables.T_Isuses();

        string BonusXML = "<Schemes>";
        string AgentBonusXML = "<Schemes>";


        DataTable dtIsuse = null;

        foreach (DataRow dr in dtFile.Rows)
        {
            LotteryID = GetLotteryID(dr["lotteryId"].ToString());
            IssueName = dr["issue"].ToString();
            FileName = dr["fileName"].ToString();

            if (t_Isuses.GetCount("LotteryID = " + LotteryID.ToString() + " and [Name] = '" + IssueName + "' and year(getdate()) = year(StartTime) and LotteryID  in (select id from T_Lotteries where PrintOutType = 108)") < 1)
            {
                continue;
            }

            dtIsuse = t_Isuses.Open("ID, StartTime, EndTime, WinLotteryNumber", "LotteryID = " + LotteryID.ToString() + " and [Name] = '" + Shove._Web.Utility.FilteSqlInfusion(IssueName) + "' and LotteryID  in (select id from T_Lotteries where PrintOutType = 108) and year(getdate()) = year(StartTime) and IsOpened = 0", "");

            if ((dtIsuse == null) || (dtIsuse.Rows.Count < 1))
            {
                continue;
            }

            string DownLoadFileNameUrl = ElectronTicket_CTTCSD_DownloadGetway + "/" + ElectronTicket_CTTCSD_UserName + "/" + dr["lotteryId"].ToString() + "/Award/" + FileName;

            string Html = PF.GetHtml(DownLoadFileNameUrl, "utf-8", 120);

            if (Html == "")
            {
                Body = "<body><result>100004</result></body>";

                new Log("ElectronTicket\\CTTCSD").Write("没有获取到中奖信息:期号为:" + IssueName);
            }

            if (Html.IndexOf("<body/>") >= 0)
            {
                continue;
            }

            elements = Html.Substring(Html.IndexOf("<head>"), Html.LastIndexOf("</body>") - Html.IndexOf("<head>")) + "</body>";

            DataSet ds1 = new DataSet();

            ds1.ReadXml(new StringReader(Html));

            if (ds1 == null)
            {
                Body = "<body><result>100004</result></body>";

                new Log("ElectronTicket\\CTTCSD").Write("中奖文件获取异常:读取内容为:" + Html);

                continue;
            }

            if (Shove._Security.Encrypt.MD5(elements).ToLower() != ds1.Tables[0].Rows[0]["sign"].ToString().ToLower())
            {
                Body = "<body><result>100004</result></body>";

                new Log("ElectronTicket\\CTTCSD").Write("校验错误:" + Shove._Security.Encrypt.MD5(elements).ToLower() + "----------" + ds1.Tables[0].Rows[0]["sign"].ToString().ToLower());

                continue;
            }

            DataTable dtTicket = ds1.Tables[ds1.Tables.Count - 1];

            if (dtTicket.Rows.Count < 1)
            {
                Body = "<body><result>0</result></body>";

                continue;
            }

            LotteryID = GetLotteryID(ds1.Tables[1].Rows[0]["lotteryId"].ToString());
            IssueName = ds1.Tables[1].Rows[0]["issue"].ToString();

            string[] StrTickets = null;

            DataTable dtTicketTemp = new DataTable();

            dtTicketTemp.Columns.Add("ID", typeof(System.String));
            dtTicketTemp.Columns.Add("AwardValue", typeof(System.String));

            DataRow drTicketTemp = null;

            foreach (DataRow drTicket in dtTicket.Rows)
            {
                StrTickets = drTicket[0].ToString().Split(',');

                if (StrTickets.Length != 7)
                {
                    continue;
                }

                drTicketTemp = dtTicketTemp.NewRow();

                drTicketTemp["ID"] = StrTickets[0].ToString();
                drTicketTemp["AwardValue"] = StrTickets[3].ToString().Insert(StrTickets[3].ToString().Length - 2, ".");

                dtTicketTemp.Rows.Add(drTicketTemp);
                dtTicketTemp.AcceptChanges();
            }

            DataTable dtSchemes = MSSQL.Select("SELECT SchemeID, SchemesMultiple as Multiple, Identifiers FROM V_SchemesSendToCenter WHERE (IsuseID = " + dtIsuse.Rows[0]["ID"].ToString() + ")");

            if (dtSchemes == null)
            {
                new Log("ElectronTicket\\CTTCSD").Write("电子票开奖,第 " + IssueName + " 期,读取本地方案错误。");

                this.Response.End();

                return;
            }

            try
            {
                var query1 = from NewDtTickets in dtTicketTemp.AsEnumerable()
                             join NewdtScheme in dtSchemes.AsEnumerable()
                             on NewDtTickets.Field<string>("ID") equals NewdtScheme.Field<string>("Identifiers")
                             select new
                             {
                                 ID = NewdtScheme.Field<long>("SchemeID"),
                                 Multiple = NewdtScheme.Field<int>("Multiple"),
                                 Bonus = Shove._Convert.StrToDouble(NewDtTickets.Field<string>("AwardValue"), 0)
                             };

                var query2 = from NewDt in query1.AsQueryable()
                             group NewDt by new { NewDt.ID, NewDt.Multiple } into gg
                             select new
                             {
                                 ID = gg.Key.ID,
                                 Multiple = gg.Key.Multiple,
                                 Bonus = gg.Sum(NewDt => NewDt.Bonus)
                             };

                var query3 = from NewDt in query2.AsQueryable()
                             group NewDt by new { NewDt.ID, NewDt.Multiple } into t_dtSchemes
                             select new
                             {
                                 SchemeID = t_dtSchemes.Key.ID,
                                 Multiple = t_dtSchemes.Key.Multiple,
                                 Bonus = t_dtSchemes.Sum(NewDt => NewDt.Bonus)
                             };

                foreach (var Scheme in query3)
                {
                    BonusXML += "<Scheme SchemeID=\"" + Scheme.SchemeID.ToString() + "\" WinMoney=\"" + Scheme.Bonus.ToString() + "\" WinDescription=\"\" />";
                }
            }
            catch (Exception e)
            {
                new Log("ElectronTicket\\CTTCSD").Write("电子票开奖,第 " + IssueName + " 期详细中奖数据解析错误:" + e.Message);

                this.Response.End();

                return;
            }
        }

        BonusXML += "</Schemes>";
        AgentBonusXML += "</Schemes>";

        if (dtIsuse.Rows.Count > 0)
        {
            int ReturnValue = 0;
            string ReturnDescription = "";

            int Times = 0;
            int Result = -1;

            while ((Result < 0) && (Times < 5))
            {
                ReturnValue = 0;
                ReturnDescription = "";

                Result = DAL.Procedures.P_ElectronTicketWin(Shove._Convert.StrToLong(dtIsuse.Rows[0]["ID"].ToString(), 0), BonusXML, AgentBonusXML, ref ReturnValue, ref ReturnDescription);

                if (Result < 0)
                {
                    new Log("ElectronTicket\\CTTCSD").Write("电子票第 " + (Times + 1).ToString() + " 次派奖出现错误(IsuseOpenNotice) 期号为: " + IssueName + ",彩种为: " + LotteryID.ToString());
                    Times++;

                    if (Times < 5)
                    {
                        System.Threading.Thread.Sleep(10000);
                    }

                    continue;
                }
            }

            if (ReturnValue < 0)
            {
                new Log("ElectronTicket\\CTTCSD").Write("电子票派奖出现错误(IsuseOpenNotice) 期号为: " + IssueName + ",彩种为: " + LotteryID.ToString() + ",错误:" + ReturnDescription);

                this.Response.End();

                return;
            }
        }

        if (string.IsNullOrEmpty(Body))
        {
            Body = "<body><result>0</result></body>";
        }

        ReNotice("1301", Body);
    }
Example #16
1
    private bool VerificareFisierReceptie(DataTable rows, int dataPrimiriiIndex, int dataPrelevareIndex)
    {
        bool valid = true;

        foreach (var coduriDuplicate in rows.AsEnumerable().GroupBy(x => x[2]).Where(g => g.Count() > 1).Select(y => y.Key).ToList().Where(coduriDuplicate => coduriDuplicate.ToString() != ""))
        {
            loggerReceptie.Error("EROARE: Id zilnic: {0} se gaseste de mai multe ori: ", coduriDuplicate);
            valid = false;
        }

        DateTime outValue = new DateTime();
        int header = 0;

        foreach (DataRow row in rows.Rows)
        {
            header++;

            if (header > 2 && row[3].ToString().Trim() != "")
            {
                if (!DateTime.TryParseExact(row[dataPrelevareIndex].ToString().Trim(), "dd/MM/yyyy", new CultureInfo("fr-FR"), DateTimeStyles.None, out outValue))
                {
                    loggerReceptie.Error("EROARE: Data Prelevare: {0} este in format gresit: ", row[dataPrelevareIndex].ToString());
                    valid = false;
                }

                if (!DateTime.TryParseExact(row[dataPrimiriiIndex].ToString().Trim(), "dd/MM/yyyy", new CultureInfo("fr-FR"), DateTimeStyles.None, out outValue))
                {
                    loggerReceptie.Error("EROARE: Data Primirii: {0} este in format gresit: ", row[dataPrimiriiIndex].ToString());
                    valid = false;
                }
            }
        }

        return valid;
    }
    public String recoverPassword2(String SendtoAddress)
    {
        String query = String.Format("SELECT u.u_name, u.passd FROM UserAcc AS u, Candidate AS c WHERE c.email= '{0}' AND u.u_id=c.u_id", SendtoAddress);
        DataTable myTable = new DataTable();
        DBConnection myConnection = new DBConnection();
        myConnection.connect("LILY-PC", "projectdb1", "admin", "123");
        myTable = myConnection.executeReturningQuery(query);
        DataRow[] rowArray = myTable.AsEnumerable().Take(1).ToArray();
        object[] objectArray = rowArray[0].ItemArray;
        String[] stringArray = Array.ConvertAll(objectArray, (p => Convert.ToString(p)));
        String body = String.Format("Your login credentials for the project by DILIGENTIA are as follows, <br /><br /><b>Username:</b> {0}<br /><b>Password:</b> {1}", stringArray[0], stringArray[1]);
        //String body = stringArray[0] + stringArray[1];

        MailMessage msg = new MailMessage();
        System.Net.Mail.SmtpClient client = new System.Net.Mail.SmtpClient();
        try
        {
            msg.Subject = "Recover Password";
            msg.Body = body;
            msg.From = new MailAddress("*****@*****.**");
            msg.To.Add(SendtoAddress);
            msg.IsBodyHtml = true;
            client.Host = "smtp.gmail.com";
            System.Net.NetworkCredential basicauthenticationinfo = new System.Net.NetworkCredential("*****@*****.**", "diligentia");
            client.Port = int.Parse("587");
            client.EnableSsl = true;
            client.UseDefaultCredentials = false;
            client.Credentials = basicauthenticationinfo;
            client.DeliveryMethod = SmtpDeliveryMethod.Network;
            client.Send(msg);
            return "Sucessful Completion";
        }
        catch (Exception ex)
        {
            return (ex.Message);
        }
    }
Example #18
1
    private void Load_Data_Prestation()
    {
        try
        {

            Dictionary<object, object> param = new Dictionary<object, object>();
            param.Add("ID_CLIENT", Session.GET_AND_DECRYPT(CYS_SESSION_TAGS.SESSION_LOGGED_S_IDCLIENT));
            dt = CYS_UTIL.getDatatable("PRESTATIONS_GROUPEget", param, SCYS_GEN);
            var result = from row in dt.AsEnumerable()
                         group row by row.Field<int>("ID_PRESTATION_THEME") into grp
                         select new
                         {
                             ID_PRESTATION_THEME = grp.Key
                         };
            param.Clear();
            param.Add("ID_PRESTATAIRE", Session.GET_AND_DECRYPT(CYS_SESSION_TAGS.SESSION_LOGGED_S_IDETABLISSEMENT));

            DataTable dtPrest = CYS_UTIL.getDatatable("PRESTATIONSget", param, SCYS_GEN);

            repeater_prestation_theme_laboratoire.DataSource = result;
            repeater_prestation_theme_laboratoire.DataBind();
            foreach (RepeaterItem i2 in repeater_prestation_theme_laboratoire.Items)
            {
                HtmlGenericControl prestation_them_div = i2.FindControl("prestation_them_div") as HtmlGenericControl;
                HtmlAnchor anchor_accordion1 = prestation_them_div.FindControl("anchor_accordion") as HtmlAnchor;
                HtmlGenericControl prestation_group_div1 = prestation_them_div.FindControl("prestation_groupe_div") as HtmlGenericControl;
                Label lab_id_prestation_theme = i2.FindControl("lab_id_prestation_theme") as Label;
                Label lab_libelle_prestation_theme = prestation_them_div.FindControl("lab_libelle_prestation_theme") as Label;
                anchor_accordion1.HRef = "#" + prestation_group_div1.ClientID;
                anchor_accordion1.Attributes["data-parent"] = prestation_them_div.ClientID;
                switch (Convert.ToInt32(lab_id_prestation_theme.Text))
                {
                    //TRADUCTION
                    case 4:
                    //TRANSPORT
                    case 1:
                        lab_libelle_prestation_theme.Text = dt.Select("ID_PRESTATION_THEME = " + lab_id_prestation_theme.Text)[0]["LIBELLE_THEME"].ToString(); 
                        param = new Dictionary<object, object>();
                        param.Add("ID_PRESTATION_THEME", lab_id_prestation_theme.Text);
                        param.Add("ID_ETABLISSEMENT", Session.GET_AND_DECRYPT(CYS_SESSION_TAGS.SESSION_LOGGED_S_IDETABLISSEMENT));
                        DataTable dtPrestationSource = CYS_UTIL.getDatatable("PRESTATIONSgetSource", param, SCYS_GEN);
                        Repeater repeater_prestation_source = prestation_group_div1.FindControl("repeater_prestation_source") as Repeater;
                        repeater_prestation_source.DataSource = dtPrestationSource;
                        repeater_prestation_source.DataBind();
                        break;
                    default:
                        Repeater repeater_prestation_laboratoire = prestation_group_div1.FindControl("repeater_prestation_laboratoire") as Repeater;
                        var resultPrest = from row in dt.AsEnumerable()
                                          where row.Field<int>("ID_PRESTATION_THEME") == Convert.ToInt32(lab_id_prestation_theme.Text)
                                          select new
                                          {
                                              ID_PRESTATION_GROUPE = row.Field<int>("ID_PRESTATION_GROUPE"),
                                              ID_PRESTATION_THEME = row.Field<int>("ID_PRESTATION_THEME"),
                                              LIBELLE = row.Field<string>("LIBELLE"),
                                              LIBELLE_THEME = row.Field<string>("LIBELLE_THEME"),
                                              MASQUE_PRIX = row.Field<string>("MASQUE_PRIX"),
                                              LIBELLE_PRIX = row.Field<string>("LIBELLE_PRIX")
                                          };
                        lab_libelle_prestation_theme.Text = resultPrest.First().LIBELLE_THEME;
                        repeater_prestation_laboratoire.DataSource = resultPrest;
                        repeater_prestation_laboratoire.DataBind();
                        foreach (RepeaterItem i in repeater_prestation_laboratoire.Items)
                        {
                            HtmlGenericControl prestation_groupe_div = i.FindControl("prestation_groupe_div") as HtmlGenericControl;
                            HtmlGenericControl prestation_detail_div = prestation_groupe_div.FindControl("prestation_detail_div") as HtmlGenericControl;
                            Label ID_PRESTATION_GROUPE = prestation_groupe_div.FindControl("ID_PRESTATION_GROUPE") as Label;
                            HtmlAnchor anchor_accordion = prestation_groupe_div.FindControl("anchor_accordion") as HtmlAnchor;
                            Repeater repeater_prestation_detail = prestation_detail_div.FindControl("repeater_prestation_detail") as Repeater;
                            //LIEN ACCORDION
                            anchor_accordion.HRef = "#" + prestation_detail_div.ClientID;
                            anchor_accordion.Attributes["data-parent"] = prestation_groupe_div.ClientID;
                            param = new Dictionary<object, object>();
                            param.Add("ID_PRESTATION_GROUPE", ID_PRESTATION_GROUPE.Text);
                            param.Add("ID_PRESTATAIRE", Session.GET_AND_DECRYPT(CYS_SESSION_TAGS.SESSION_LOGGED_S_IDETABLISSEMENT));
                            DataTable dtPrestationDetail = CYS_UTIL.getDatatable("PRESTATIONSget", param, SCYS_GEN);
                            repeater_prestation_detail.DataSource = dtPrestationDetail;
                            repeater_prestation_detail.DataBind();
                        }
                        break;
                }
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {

        }
    }
Example #19
1
    /// <summary>
    /// 多个DataTable 合成一个 DataTable
    /// </summary>
    /// <param name="dts"></param>
    /// <returns></returns>
    public static DataTable MargeTable(List<DataTable> dts)
    {
        DataTable dt = new DataTable();
        DataColumn col1 = new DataColumn("NodeId", typeof(int));
        DataColumn col2 = new DataColumn("NodeName");
        DataColumn col3 = new DataColumn("des");
        DataColumn col4 = new DataColumn("ipcs");
        DataColumn col5 = new DataColumn("live");
        DataColumn col6 = new DataColumn("showtype");
        DataColumn col7 = new DataColumn("IsParent");

        dt.Columns.Add(col1); dt.Columns.Add(col2); dt.Columns.Add(col3); dt.Columns.Add(col4); dt.Columns.Add(col5); dt.Columns.Add(col6); dt.Columns.Add(col7);
        foreach (var dt1 in dts)
        {
            for (int i = 0; i < dt1.Rows.Count; i++)
            {
                string nodeid = dt1.Rows[i]["NodeId"].ToString();
                int x = (from y in dt.AsEnumerable()
                         where y["NodeId"].ToString() == nodeid
                         select y).Count();
                if (x > 0) continue;
                DataRow row = dt.NewRow();
                row["NodeId"] = dt1.Rows[i]["NodeId"];
                row["NodeName"] = dt1.Rows[i]["NodeName"];
                row["des"] = dt1.Rows[i]["des"];
                row["ipcs"] = dt1.Rows[i]["ipcs"];
                row["live"] = dt1.Rows[i]["live"];
                row["IsParent"] = dt1.Rows[i]["IsParent"];
                row["showtype"] = dt1.Rows[i]["showtype"];
                dt.Rows.Add(row);
            }
        }

        return dt;
    }
Example #20
0
        public async Task GenerateFileAsync()
        {
            string connectionString = GetConnectionString();

            if (connectionString == null)
            {
                return;
            }

            (string schemaName, string tableName) = DetermineSchemaAndTable(_options.SchemaName, _options.TableName);

            DataTable columns = await GetColumnsAsync(connectionString, schemaName, tableName);

            List <ColumnDefinition> processedColumns = columns?.AsEnumerable().Select(p => new ColumnDefinition(p)).ToList();

            if (processedColumns == null || !processedColumns.Any())
            {
                WriteError($"Cannot find table: {_options.SchemaName}.{_options.TableName}");
                return;
            }

            IEnumerable <IGrouping <string, ColumnDefinition> > grouped = processedColumns.GroupBy(p => p.TableName);

            foreach (IGrouping <string, ColumnDefinition> group in grouped)
            {
                WriteSuccess($"Found table ({schemaName}.{group.Key}) and {group.Count()} column{(group.Count() == 1 ? "" : "s")}");
            }
            foreach (IGrouping <string, ColumnDefinition> group in grouped)
            {
                GenerateFile(group, schemaName, group.Key);
            }
        }
Example #21
0
        private void ConvertTabelToList(DataTable models)
        {
            var firstColumnName = models.Columns[0].ColumnName;

            ServerModels = models?.AsEnumerable()
                           .Select(r => r.Field <string>(firstColumnName))
                           .ToList();
            TableOk = (ServerModels != null);
        }
 // returns a List<String> which has a row of CV information of the user who has the given ID
 public String[] getDetailsToForm(String id)
 {
     DataTable myTable = new DataTable();
     myTable = this.getCV(id);
     DataRow[] rowArray = myTable.AsEnumerable().Take(1).ToArray();
     object[] objectArray = rowArray[0].ItemArray;
     String[] stringArray = Array.ConvertAll(objectArray, (p => Convert.ToString(p)));
     return stringArray;
 }
Example #23
0
    protected void Page_Load(object sender, EventArgs e) //获取全部的Family
    {
        if (!this.IsPostBack)
        {
            DataTable fy = new DataTable();
            fy = uph.GetUPHFamily();
            var q = from r in fy.AsEnumerable()
                    select new ListItem(r[0].ToString());

            string FY = fy.ToString();
            this.Select3.Items.AddRange(q.ToArray());
            bindbullTable(null,20);
        }
    }
Example #24
0
    public List<dynamic> GetData(DataTable dt)
    {
        List<dynamic> bendinamigim = new List<dynamic>();

            foreach (var item in dt.AsEnumerable())
            {
                IDictionary<string, object> dn = new ExpandoObject();

                foreach (var column in dt.Columns.Cast<DataColumn>()) dn[column.ColumnName] = item[column];

                bendinamigim.Add(dn);
            }

            return bendinamigim;
    }
Example #25
0
        private int GetMaxSequence(DataTable table, string column)
        {
            try
            {
                if (table.Columns.Contains(column) == false)
                {
                    throw new Exception($"{table} 테이블에 {column} 컬럼이 존재하지 않습니다.");
                }

                var result = table?.AsEnumerable().Select(m => m.Field <int>(column)).Where(m => m < MaxSeq)?.ToList();
                return(result.Any() ? result.Max() : 0);
            }
            catch (Exception ex)
            {
                logger.Error($"ex={ex}");
                return(-1);
            }
        }
Example #26
0
        //Tồn Kho
        public static DataTable GetTonKhoLookup()
        {
            try
            {
                DonViTinh dv = new DonViTinh();
                Kho       k  = new Kho();
                DataTable dataTableTonKho = k.LayTonKhoLookUp();
                DataTable dataTableDV     = dv.LoadDVTDonGian();
                DataTable dataTableKho    = k.LoadKhoHang();
                DataTable dataTableHD     = k.GetHanhDong();

                var results = from table1 in dataTableTonKho.AsEnumerable()
                              join table2 in dataTableDV.AsEnumerable() on(string) table1["Unit"] equals(string) table2["UNIT_ID"]
                              join table3 in dataTableKho.AsEnumerable() on(string) table1["Stock_ID"] equals(string) table3["Stock_ID"]
                              join table4 in dataTableHD.AsEnumerable() on(int) table1["RefType"] equals(int) table4["Action_ID"]
                              select new
                {
                    RefDate      = DateTime.Parse(table1["RefDate"].ToString()),
                    RefNo        = table1["RefNo"].ToString(),
                    RefType      = table4["Action_Name"].ToString(),
                    Stock_ID     = table3["Stock_Name"].ToString(),
                    Product_ID   = table1["Product_ID"].ToString(),
                    Product_Name = table1["Product_Name"].ToString(),
                    Unit         = table2["Unit_Name"].ToString(),
                    Quantity     = float.Parse(table1["Quantity"].ToString()),
                    Price        = float.Parse(table1["Price"].ToString()),
                    UnitPrice    = float.Parse(table1["UnitPrice"].ToString()),
                    Amount       = float.Parse(table1["Amount"].ToString()),
                    E_Qty        = float.Parse(table1["E_Qty"].ToString()),
                    E_Amt        = float.Parse(table1["E_Amt"].ToString()),
                    Description  = table1["Description"].ToString()
                };

                DataTable rs = new DataTable();
                rs.Columns.Add("RefDate");
                rs.Columns.Add("RefNo");
                rs.Columns.Add("RefType");
                rs.Columns.Add("Stock_ID");
                rs.Columns.Add("Product_ID");
                rs.Columns.Add("Product_Name");
                rs.Columns.Add("Unit");
                rs.Columns.Add("Quantity");
                rs.Columns.Add("Price");
                rs.Columns.Add("UnitPrice");
                rs.Columns.Add("Amount");
                rs.Columns.Add("E_Qty");
                rs.Columns.Add("E_Amt");
                rs.Columns.Add("Description");

                foreach (var item in results)
                {
                    rs.Rows.Add
                    (
                        item.RefDate,
                        item.RefNo,
                        item.RefType,
                        item.Stock_ID,
                        item.Product_ID,
                        item.Product_Name,
                        item.Unit,
                        item.Quantity,
                        item.Price,
                        item.UnitPrice,
                        item.Amount,
                        item.E_Qty,
                        item.E_Amt,
                        item.Description
                    );
                }

                return(rs);
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }
        public static void GetExtlevelTable(TextWriter twWriter, DataTable des, DataTable dt, string seperator, List <ColumnTotals> Totals)
        {
            try
            {
                var depts = dt.AsEnumerable().Select(s => s.Field <string>("Cost Centre")).Distinct();

                foreach (var v in depts)
                {
                    twWriter.WriteLine();
                    twWriter.Write(v.ToString());
                    twWriter.WriteLine(" ");

                    twWriter.WriteLine();

                    foreach (DataColumn column in des.Columns)
                    {
                        if (column.ColumnName != "Department" && column.ColumnName != "Totalnonform" && column.ColumnName != "indurnonformated" && column.ColumnName != "outdurnonformated")
                        {
                            twWriter.Write(column.ColumnName);
                            twWriter.Write(seperator);
                        }
                    }


                    var dest = from db in des.AsEnumerable()
                               where db.Field <string>("Department") == v.ToString()
                               select new
                    {
                        Destinationname = db.Field <object>("Destination Name"),
                        Totalcalls      = db.Field <object>("Total Calls"),
                        Totalduration   = db.Field <object>("Total Duration"),
                        Totalnonform    = db.Field <object>("Totalnonform"),
                        Cost            = db.Field <object>("Cost")
                    };
                    foreach (var destinations in dest)
                    {
                        twWriter.WriteLine();
                        twWriter.Write(destinations.Destinationname.ToString().Replace(" ", string.Empty));
                        twWriter.Write(seperator);
                        twWriter.Write(destinations.Totalcalls.ToString().Replace(" ", string.Empty));
                        twWriter.Write(seperator);
                        twWriter.Write(destinations.Totalduration.ToString().Replace(" ", string.Empty));
                        twWriter.Write(seperator);
                        twWriter.Write(destinations.Cost.ToString().Replace(" ", string.Empty));
                        twWriter.Write(seperator);
                    }

                    twWriter.WriteLine();
                    #region "Problem with totals"
                    try
                    {
                        foreach (DataColumn colad in des.Columns)
                        {
                            string col    = colad.ColumnName;
                            double footer = 0;
                            if (col != "Department" && col != "Totalnonform")
                            {
                                if (Totals.Where(t => t.ColumnName.Contains(col)).Any())
                                {
                                    switch (Totals.Where(t => t.ColumnName.Contains(col)).Select(g => g.Totaltype).SingleOrDefault())
                                    {
                                    case "Count":
                                    {
                                        footer = dest.AsEnumerable().Count();
                                        twWriter.Write("Count" + ":" + footer.ToString());
                                        break;
                                    }

                                    case "Sum":
                                    {
                                        if (col.ToLower().Contains("cost"))
                                        {
                                            twWriter.Write("Total" + ":" + dest.AsEnumerable().Sum(s => Convert.ToDouble(s.Cost)));
                                        }
                                        else
                                        {
                                            footer = dest.AsEnumerable().Sum(s => Convert.ToInt32(s.Totalcalls));
                                            twWriter.Write("Total" + ":" + footer.ToString());
                                        }


                                        break;
                                    }

                                    case "FormatedSum":
                                    {
                                        int      form = dest.AsEnumerable().Sum(s => Convert.ToInt32(s.Totalnonform));
                                        TimeSpan t1   = new TimeSpan(0, 0, form);
                                        twWriter.Write("Total" + ":" + t1.ToString());
                                        break;
                                    }
                                    }
                                }
                                twWriter.Write(seperator);
                            }
                        }
                    }
                    catch { }
                    #endregion
                    twWriter.WriteLine();
                    foreach (DataColumn column in dt.Columns)
                    {
                        if (column.ColumnName != "Cost Centre" && column.ColumnName != "indurnonformated" && column.ColumnName != "outdurnonformated")
                        {
                            twWriter.Write(column.ColumnName);
                            twWriter.Write(seperator);
                        }
                    }


                    twWriter.WriteLine(" ");
                    var records = from db in dt.AsEnumerable()
                                  where db.Field <string>("Cost Centre") == v.ToString()
                                  select new
                    {
                        Name             = db.Field <object>("Name"),
                        Extension        = db.Field <object>("Extension"),
                        OutgoingCalls    = db.Field <object>("Outgoing Calls"),
                        OutgoingDuration = db.Field <object>("Outgoing Duration"),
                        outnonformated   = db.Field <object>("outdurnonformated"),
                        RingResponse     = db.Field <object>("Ring Response"),
                        AbandonedCalls   = db.Field <object>("Abandoned Calls"),
                        IncomingCalls    = db.Field <object>("Incoming Calls"),
                        IncomingDuration = db.Field <object>("Incoming Duration"),
                        Innonformated    = db.Field <object>("indurnonformated"),
                        Cost             = db.Field <object>("Cost")
                    };
                    foreach (var all in records)
                    {
                        twWriter.WriteLine();
                        twWriter.Write(all.Name.ToString().Replace(" ", string.Empty));
                        twWriter.Write(seperator);
                        twWriter.Write(all.Extension.ToString().Replace(" ", string.Empty));
                        twWriter.Write(seperator);
                        twWriter.Write(all.OutgoingCalls.ToString().Replace(" ", string.Empty));
                        twWriter.Write(seperator);
                        twWriter.Write(all.OutgoingDuration.ToString().Replace(" ", string.Empty));
                        twWriter.Write(seperator);
                        twWriter.Write(all.RingResponse.ToString().Replace(" ", string.Empty));
                        twWriter.Write(seperator);
                        twWriter.Write(all.AbandonedCalls.ToString().Replace(" ", string.Empty));
                        twWriter.Write(seperator);
                        twWriter.Write(all.IncomingCalls.ToString().Replace(" ", string.Empty));
                        twWriter.Write(seperator);
                        twWriter.Write(all.IncomingDuration.ToString().Replace(" ", string.Empty));
                        twWriter.Write(seperator);
                        twWriter.Write(all.Cost.ToString().Replace(" ", string.Empty));
                    }
                    # region "Problem with totals...."
                    twWriter.WriteLine(" ");
                    try
                    {
                        foreach (DataColumn colnm in dt.Columns)
                        {
                            string col    = colnm.ColumnName;
                            string footer = "";
                            if (col != "Cost Centre" && col != "indurnonformated" && col != "outdurnonformated")
                            {
                                if (Totals.Where(t => t.ColumnName.Contains(col)).Any())
                                {
                                    switch (Totals.Where(t => t.ColumnName.Contains(col)).Select(g => g.Totaltype).SingleOrDefault())
                                    {
                                    case "Count":
                                    {
                                        footer = records.AsEnumerable().Count().ToString();
                                        twWriter.Write("Count" + ":" + footer.ToString());
                                        break;
                                    }

                                    case "Sum":
                                    {
                                        if (col.ToLower().Contains("cost"))
                                        {
                                            twWriter.Write("Total" + ":" + records.AsEnumerable().Sum(s => Convert.ToDouble(s.Cost)).ToString());
                                        }


                                        else if (col.Contains("Incoming Calls"))
                                        {
                                            footer = Convert.ToString(records.AsEnumerable().Sum(s => Convert.ToInt32(s.IncomingCalls)));
                                            twWriter.Write("Total" + ":" + footer.ToString());
                                        }
                                        else if (col.Contains("Outgoing Calls"))
                                        {
                                            footer = Convert.ToString(records.AsEnumerable().Sum(s => Convert.ToInt32(s.OutgoingCalls)));
                                            twWriter.Write("Total" + ":" + footer.ToString());
                                        }

                                        break;
                                    }

                                    case "FormatedSum":
                                    {
                                        int dur = 0; TimeSpan t1;
                                        if (col.Contains("Incoming Duration"))
                                        {
                                            dur = records.Sum(s => Convert.ToInt32(s.Innonformated));
                                        }
                                        else
                                        {
                                            dur = records.Sum(s => Convert.ToInt32(s.outnonformated));
                                        }
                                        t1 = new TimeSpan(0, 0, dur);
                                        twWriter.Write("Total" + ":" + t1.ToString());
                                        break;
                                    }

                                    case "Avg":
                                    {
                                        footer = Convert.ToString(Math.Round(records.AsEnumerable().Average(s => Convert.ToInt32(s.RingResponse)), 2));
                                        twWriter.Write("Avg" + ":" + footer.ToString());
                                        break;
                                    }
                                    }
                                }
                                twWriter.Write(seperator);
                            }
                        }
                    }
                    catch { }
                    #endregion
                    twWriter.WriteLine(" ");
                }
            }
Example #28
0
        private void BusquedaCliente()
        {
            Cls_Personas_BLL objBLL = new Cls_Personas_BLL();
            Cls_Personas_DAL objDAL = new Cls_Personas_DAL();

            objBLL.Listar(ref objDAL);

            DataTable dt = objDAL.dtTablaPersonas;

            EnumerableRowCollection <DataRow> query = from dtTablaPersonas in dt.AsEnumerable()
                                                      where dtTablaPersonas.Field <string>("Cedula").Equals(txtCedula.Value.Trim())
                                                      select dtTablaPersonas;

            DataView view = query.AsDataView();

            if (view.Count == 0)
            {
                txtCliente.Value     = "No se encuentra cliente con esa cédula";
                txtCliente.Visible   = true;
                btnRegistrar.Enabled = false;
                btnRegistrar.Visible = false;
                divTarjetas.Style.Add("display", "none");
                updIngreso.Update();
            }
            else
            {
                foreach (DataRowView row in view)
                {
                    string nombre = row["Nombre"].ToString();
                    if (!Convert.ToBoolean(row["Activo"].ToString()))
                    {
                        txtCliente.Value   = "El cliente se encuentra inactivo";
                        txtCliente.Visible = true;
                        updIngreso.Update();
                    }
                    else
                    {
                        txtCliente.Visible     = false;
                        txtNombreCliente.Value = row["Nombre"].ToString() + " " + row["Primer_Apellido"].ToString() + " " + row["Segundo_Apellido"].ToString();
                        txtCedula.Disabled     = true;
                        btnBuscar.Visible      = false;
                        btnBuscar.Enabled      = false;
                        btnLimpiar.Enabled     = true;
                        btnLimpiar.Visible     = true;
                        if (chkRecoger.Checked)
                        {
                            divEntrega.Style.Add("display", "block");
                            divSucursal.Style.Add("display", "none");
                        }
                        else
                        {
                            divEntrega.Style.Add("display", "none");
                            divSucursal.Style.Add("display", "block");
                        }
                        divTarjetas.Style.Add("display", "block");
                        CargarTarjetas(row["Usuario"].ToString());
                        updIngreso.Update();
                    }
                }
            }
        }
Example #29
0
 public static List <AccountMapping> GetAccountMappings(DataTable sourceDataTable)
 {
     return(sourceDataTable.AsEnumerable().Select(dr => { return GetAccountMappings(dr); }).ToList());
 }
        protected void DataCheck(ref DataTable campus, ref DataTable Vkd)
        {
            int  _maximum = campus.Rows.Count == 0 ? 1 : campus.Rows.Cast <DataRow>().Select(p => Convert.ToInt32(p["eEmployees1Id"])).Max() + 1;
            bool check    = false;

            var EmployeeIdlist = Vkd.AsEnumerable().Select(y => new
            {
                EmpId = y["ID_employee"],
                SubId = y["ID_Subdivision"]
            }).Distinct().ToList();

            var vkdRows    = Vkd.Rows.Cast <DataRow>().ToList();
            var campusRows = campus.Rows.Cast <DataRow>().ToList();

            for (int rowNumber = 0; rowNumber < EmployeeIdlist.Count; rowNumber++)
            {
                var row = EmployeeIdlist[rowNumber];

                NLogCore.LogStatusAplication(String.Format("DataCheck : {0} of {1}", rowNumber, EmployeeIdlist.Count));

                var gottenEmployes = vkdRows.Where(o => o["ID_employee"].ToString() == row.EmpId.ToString() &&
                                                   o["ID_Subdivision"].ToString() == row.SubId.ToString())
                                     .OrderBy(e => e["RowNumber"].ToString())
                                     .ToList();
                var existingEmployes = campusRows.Where(t => t["ID_employee"].ToString() == row.EmpId.ToString() &&
                                                        t["ID_Subdivision"].ToString() == row.SubId.ToString())
                                       //  && t["vcChangeStatus"].ToString() != "Видалено")
                                       .OrderBy(e => e["eEmployees1Id"].ToString())
                                       .ToList();

                if (gottenEmployes.Count == existingEmployes.Count)
                {
                    var indexis = gottenEmployes.Count;
                    for (int i = 0; i < indexis; i++)
                    {
                        if (check)
                        {
                            i     = 0;
                            check = false;
                        }
                        if (existingEmployes[i]["ID_Duties"].ToString() == gottenEmployes[i]["ID_Duties"].ToString() &&
                            existingEmployes[i]["ID_Group"].ToString() == gottenEmployes[i]["ID_Group"].ToString() &&
                            existingEmployes[i]["IDEmploymentForm"].ToString() ==
                            gottenEmployes[i]["IDEmploymentForm"].ToString())
                        {
                            RowUpdate(existingEmployes[i], gottenEmployes[i]);
                            existingEmployes.RemoveAt(i);
                            gottenEmployes.RemoveAt(i);
                            indexis -= 1;
                            check    = true;
                        }
                        else
                        {
                            var getSameEmployee = existingEmployes.Find(t =>
                                                                        t["ID_Duties"].ToString() == gottenEmployes[i]["ID_Duties"].ToString() &&
                                                                        t["ID_Group"].ToString() == gottenEmployes[i]["ID_Group"].ToString() &&
                                                                        t["IDEmploymentForm"].ToString() ==
                                                                        gottenEmployes[i]["IDEmploymentForm"].ToString());
                            if (getSameEmployee != null)
                            {
                                var getSameEmployeeIndex = existingEmployes.FindIndex(t =>
                                                                                      t["ID_Duties"].ToString() == gottenEmployes[i]["ID_Duties"].ToString() &&
                                                                                      t["ID_Group"].ToString() == gottenEmployes[i]["ID_Group"].ToString() &&
                                                                                      t["IDEmploymentForm"].ToString() ==
                                                                                      gottenEmployes[i]["IDEmploymentForm"].ToString());
                                RowUpdate(existingEmployes[getSameEmployeeIndex], gottenEmployes[i]);
                                existingEmployes.RemoveAt(getSameEmployeeIndex);
                                gottenEmployes.RemoveAt(i);
                                indexis -= 1;
                                check    = true;
                            }
                        }
                    }
                    if (gottenEmployes.Count != 0)
                    {
                        for (int i = 0; i < gottenEmployes.Count; i++)
                        {
                            RowUpdate(existingEmployes[i], gottenEmployes[i]);
                        }
                    }
                }

                if (gottenEmployes.Count > existingEmployes.Count)
                {
                    int index = gottenEmployes.Count;
                    for (int i = 0; i < index; i++)
                    {
                        if (check)
                        {
                            i     = 0;
                            check = false;
                        }
                        var getSameEmployee = existingEmployes.Find(t =>
                                                                    t["ID_Duties"].ToString() == gottenEmployes[i]["ID_Duties"].ToString() &&
                                                                    t["ID_Group"].ToString() == gottenEmployes[i]["ID_Group"].ToString() &&
                                                                    t["IDEmploymentForm"].ToString() ==
                                                                    gottenEmployes[i]["IDEmploymentForm"].ToString());
                        if (getSameEmployee != null)
                        {
                            var getSameEmployeeIndex = existingEmployes.FindIndex(t =>
                                                                                  t["ID_Duties"].ToString() == gottenEmployes[i]["ID_Duties"].ToString() &&
                                                                                  t["ID_Group"].ToString() == gottenEmployes[i]["ID_Group"].ToString() &&
                                                                                  t["IDEmploymentForm"].ToString() ==
                                                                                  gottenEmployes[i]["IDEmploymentForm"].ToString());
                            RowUpdate(existingEmployes[getSameEmployeeIndex], gottenEmployes[i]);
                            existingEmployes.RemoveAt(getSameEmployeeIndex);
                            gottenEmployes.RemoveAt(i);
                            index -= 1;
                            check  = true;
                        }
                    }
                    if (gottenEmployes.Count() != 0)
                    {
                        for (int i = 0; i < gottenEmployes.Count(); i++)
                        {
                            if (i + 1 < gottenEmployes.Count)
                            {
                                RowUpdate(existingEmployes[i], gottenEmployes[i]);
                            }
                            else
                            {
                                var sourceRow = gottenEmployes[i];
                                var newRow    = campus.NewRow();
                                CloneRow(sourceRow, _maximum, ref newRow);

                                campus.Rows.Add(newRow);
                                NLogCore.LogStatusAplication(
                                    "Додано запис з ідентифікатором eEmployees1Id=" + _maximum.ToString());
                                _maximum = _maximum + 1;
                            }
                        }
                    }
                }

                if (gottenEmployes.Count < existingEmployes.Count)
                {
                    int index1 = existingEmployes.Count;
                    int index2 = gottenEmployes.Count;

                    //for (int i = 0; i < index1; i++)
                    //{
                    //    for (int j = 0; j < index2; j++)
                    //    {
                    //        if (check)
                    //        {
                    //            i = 0; j = 0; check = false;
                    //        }

                    //        if (existingEmployes[i]["ID_Subdivision"].ToString() == gottenEmployes[j]["ID_Subdivision"].ToString())
                    //        {
                    //            RowUpdate(existingEmployes[i], gottenEmployes[j]);
                    //            existingEmployes.RemoveAt(i);
                    //            gottenEmployes.RemoveAt(j);
                    //            index1 = index1 - 1;
                    //            index2 = index2 - 1;
                    //            check = true;
                    //        }
                    //    }
                    //}

                    for (int i = 0; i < index1; i++)
                    {
                        for (int j = 0; j < index2; j++)
                        {
                            if (check)
                            {
                                j = 0; i = 0; check = false;
                            }
                            // int index2 = 0;

                            // foreach (var gotEmployee in gottenEmployes.ToList())
                            // {
                            if (existingEmployes[i]["ID_Duties"].ToString() == gottenEmployes[j]["ID_Duties"].ToString() &&
                                existingEmployes[i]["ID_Group"].ToString() == gottenEmployes[j]["ID_Group"].ToString() &&
                                existingEmployes[i]["IDEmploymentForm"].ToString() == gottenEmployes[j]["IDEmploymentForm"].ToString())
                            {
                                //var getthis = existingEmployes.Find(r =>
                                //    r["ID_Duties"].ToString() == gottenEmployes[i]["ID_Duties"].ToString() &&
                                //    r["ID_Group"].ToString() == gottenEmployes[i]["ID_Group"].ToString() &&
                                //    r["IDEmploymentForm"].ToString() == gottenEmployes[i]["IDEmploymentForm"].ToString());
                                //// var
                                //var index = existingEmployes.FindIndex(r =>
                                //    r["ID_Duties"].ToString() == gottenEmployes[i]["ID_Duties"].ToString() &&
                                //    r["ID_Group"].ToString() == gottenEmployes[i]["ID_Group"].ToString() &&
                                //    r["IDEmploymentForm"].ToString() == gottenEmployes[i]["IDEmploymentForm"].ToString());
                                RowUpdate(existingEmployes[i], gottenEmployes[j]);
                                existingEmployes.RemoveAt(i);
                                gottenEmployes.RemoveAt(j);
                                index1 -= 1;
                                index2 -= 1;
                                check   = true;
                            }
                            //   index2++;
                        }
                    }

                    if (existingEmployes.Count != 0)
                    {
                        for (int i = 0; i < existingEmployes.Count; i++)
                        {
                            if (existingEmployes[i]["vcChangeStatus"].ToString() != "Видалено")
                            {
                                existingEmployes[i]["vcChangeStatus"] = "Видалено";
                                existingEmployes[i]["vcChangeDate"]   = DateTime.Now;

                                NLogCore.LogStatusAplication("Запис з ідентифікатором eEmployees1Id=" + existingEmployes[i]["eEmployees1Id"].ToString() + " видалено");
                            }
                        }
                    }

                    if (gottenEmployes.Count != 0)
                    {
                        for (int i = 0; i < gottenEmployes.Count; i++)
                        {
                            var sourceRow = gottenEmployes[i];
                            var newRow    = campus.NewRow();
                            CloneRow(sourceRow, _maximum, ref newRow);

                            campus.Rows.Add(newRow);
                            NLogCore.LogStatusAplication("Додано запис з ідентифікатором eEmployees1Id=" + _maximum.ToString());
                            _maximum = _maximum + 1;
                        }
                    }
                }
            }
        }
Example #31
0
        public static List <LogItem> GetLog(string dayFrom = null, string dayTo = null, bool isHistory = false)
        {
            string option_query = null;
            string date_query   = null;
            string order_query  = "ASC";

            if (isHistory)
            {
                order_query = "DESC";
            }
            string is_active = "";

            if (!isHistory)
            {
                is_active = "AND end_at is NULL";
            }
            else
            {
                is_active = "";
            }

            if (!string.IsNullOrEmpty(dayFrom) && !string.IsNullOrEmpty(dayTo))
            {
                date_query  = string.Format($" AND L.start_at BETWEEN '{dayFrom}' AND '{dayTo}'");
                order_query = "DESC";
                is_active   = "";
            }

            DataTable dt    = new DataTable();
            string    query = String.Format(@"SELECT DATE_FORMAT(L.start_at, '%Y-%m-%d %H:%i:%s') as start_at
, IFNULL(DATE_FORMAT(L.end_at, '%Y-%m-%d %H:%i:%s'), '') as end_at
, L.ip as ip
, L.oid as oid
, S.name AS name
, L.level as level
, IF(L.level = 'Critical', 'Red', IF(L.level = 'Warning', '#FF8000', IF(L.level = 'Information', 'Blue', 'Black'))) AS color
, L.value as value
, L.snmp_type_value as type_value
, L.idx as idx
FROM log L
LEFT JOIN server S ON S.ip = L.ip
WHERE 1=1
AND client_ip = '{0}'
AND snmp_type_value = 'begin'
{1}
{2}
ORDER BY L.start_at {3}", _LocalIp, is_active, date_query, order_query);

            using (MySqlConnection conn = new MySqlConnection(DatabaseManager.getInstance().ConnectionString))
            {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand(query, conn);
                cmd.Prepare();
                MySqlDataAdapter adpt = new MySqlDataAdapter(query, conn);
                dt.Clear();
                adpt.Fill(dt);
            }

            return(dt.AsEnumerable().Select(row => new LogItem
            {
                idx = row.Field <int>("idx"),
                StartAt = row.Field <string>("start_at"),
                EndAt = row.Field <string>("end_at"),
                Oid = row.Field <string>("oid"),
                Ip = row.Field <string>("ip"),
                Name = row.Field <string>("name"),
                Level = row.Field <string>("level"),
                Color = row.Field <string>("color"),
                Value = row.Field <string>("value"),
                TypeValue = row.Field <string>("type_value")
            }).ToList());
        }
Example #32
0
        public string GetRoleNavBtns(string roleid)
        {
            DataTable dt = RoleDal.Instance.GetNavBtnsBy(roleid);

            return(GetRoleNavBtns(dt.AsEnumerable(), "0").ToString().Replace("icon ", ""));
        }
Example #33
0
        private void SendToAddresses(DataSet ds)
        {
            string body;

            DataTable dt = ds.Tables[0];


            var dr = dt.AsEnumerable().Select(x => new { prefix = x.Field <string>("PREFIX"), fname = x.Field <string>("FIRST NAME"), lname = x.Field <string>("LAST/FAMILY NAME"), email = x.Field <string>("EMAIL ADDRESS") });
            //define email parameters
            SmtpClient smtp = new SmtpClient
            {
                Host           = oProv.Host,
                Port           = oProv.Port,
                DeliveryMethod = SmtpDeliveryMethod.Network,
                Credentials    = new System.Net.NetworkCredential(oProv.User, oProv.PWD),
                EnableSsl      = true
            };

            int iCnt = 0;

            foreach (var s in dr)
            {
                if (s.prefix == null || s.fname == null || s.lname == null || s.email == null)
                {
                    //do nothing
                }
                else
                {
                    body = string.Format("Dear {0} {1} {2}, {3}{4}", s.prefix.ToString(), s.fname.ToString(), s.lname.ToString(), System.Environment.NewLine, mk.ConvertRtfToHtml(richTextBox.Rtf));
                    MailMessage mail = new MailMessage
                    {
                        From    = new MailAddress(oProv.User),
                        Subject = oEmail.Subject,

                        Body       = body,
                        IsBodyHtml = true
                    };
                    if (lstAttach.Count > 0)
                    {
                        // Create the file attachment for this e-mail message.
                        foreach (string file in lstAttach)
                        {
                            Attachment data = new Attachment(file, MediaTypeNames.Application.Octet);

                            // Add time stamp information for the file.
                            ContentDisposition disposition = data.ContentDisposition;
                            disposition.CreationDate     = System.IO.File.GetCreationTime(file);
                            disposition.ModificationDate = System.IO.File.GetLastWriteTime(file);
                            disposition.ReadDate         = System.IO.File.GetLastAccessTime(file);

                            // Add the file attachment to this e-mail message.
                            mail.Attachments.Add(data);
                        }
                    }
                    mail.Bcc.Add(new MailAddress(s.email.ToString()));
                    smtp.Send(mail);
                    mail.Dispose();
                    iCnt++;
                    if (iCnt == 25)
                    {
                        //pause to prevent gmail from blocking send
                        Thread.Sleep(2000);
                        iCnt = 0;
                    }
                }
            }

            smtp.Dispose();
        }
Example #34
0
        /// <summary>Main run method for performing our calculations and storing data.</summary>
        /// <param name="dataStore">The data store.</param>
        /// <exception cref="ApsimXException">
        /// Could not find model data table:  + ObservedTableName
        /// or
        /// Could not find observed data table:  + ObservedTableName
        /// </exception>
        public void Run(IStorageReader dataStore)
        {
            if (PredictedTableName != null && ObservedTableName != null)
            {
                dataStore.DeleteDataInTable(this.Name);

                DataTable predictedDataNames = dataStore.RunQuery("PRAGMA table_info(" + PredictedTableName + ")");
                DataTable observedDataNames  = dataStore.RunQuery("PRAGMA table_info(" + ObservedTableName + ")");

                if (predictedDataNames == null)
                {
                    throw new ApsimXException(this, "Could not find model data table: " + ObservedTableName);
                }

                if (observedDataNames == null)
                {
                    throw new ApsimXException(this, "Could not find observed data table: " + ObservedTableName);
                }

                IEnumerable <string> commonCols = from p in predictedDataNames.AsEnumerable()
                                                  join o in observedDataNames.AsEnumerable() on p["name"] equals o["name"]
                                                  select p["name"] as string;

                StringBuilder query = new StringBuilder("SELECT ");
                foreach (string s in commonCols)
                {
                    if (s == FieldNameUsedForMatch || s == FieldName2UsedForMatch || s == FieldName3UsedForMatch)
                    {
                        query.Append("I.'@field', ");
                    }
                    else
                    {
                        query.Append("I.'@field' AS 'Observed.@field', R.'@field' AS 'Predicted.@field', ");
                    }

                    query.Replace("@field", s);
                }


                query.Append("FROM " + ObservedTableName + " I INNER JOIN " + PredictedTableName + " R USING (SimulationID) WHERE I.'@match1' = R.'@match1'");
                if (FieldName2UsedForMatch != null && FieldName2UsedForMatch != string.Empty)
                {
                    query.Append(" AND I.'@match2' = R.'@match2'");
                }
                if (FieldName3UsedForMatch != null && FieldName3UsedForMatch != string.Empty)
                {
                    query.Append(" AND I.'@match3' = R.'@match3'");
                }

                int checkpointID = dataStore.GetCheckpointID("Current");
                query.Append(" AND R.CheckpointID = " + checkpointID);

                query.Replace(", FROM", " FROM"); // get rid of the last comma
                query.Replace("I.'SimulationID' AS 'Observed.SimulationID', R.'SimulationID' AS 'Predicted.SimulationID'", "I.'SimulationID' AS 'SimulationID'");

                query = query.Replace("@match1", FieldNameUsedForMatch);
                query = query.Replace("@match2", FieldName2UsedForMatch);
                query = query.Replace("@match3", FieldName3UsedForMatch);

                if (Parent is Folder)
                {
                    // Limit it to particular simulations in scope.
                    List <string> simulationNames = new List <string>();
                    foreach (Experiment experiment in Apsim.FindAll(this, typeof(Experiment)))
                    {
                        simulationNames.AddRange(experiment.GetSimulationNames());
                    }
                    foreach (Simulation simulation in Apsim.FindAll(this, typeof(Simulation)))
                    {
                        if (!(simulation.Parent is Experiment))
                        {
                            simulationNames.Add(simulation.Name);
                        }
                    }

                    query.Append(" AND I.SimulationID in (");
                    foreach (string simulationName in simulationNames)
                    {
                        if (simulationName != simulationNames[0])
                        {
                            query.Append(',');
                        }
                        query.Append(dataStore.GetSimulationID(simulationName));
                    }
                    query.Append(")");
                }

                DataTable predictedObservedData = dataStore.RunQuery(query.ToString());

                if (predictedObservedData != null)
                {
                    predictedObservedData.TableName = this.Name;
                    dataStore.WriteTable(predictedObservedData);

                    List <string> unitFieldNames = new List <string>();
                    List <string> unitNames      = new List <string>();

                    // write units to table.
                    foreach (string fieldName in commonCols)
                    {
                        string units = dataStore.GetUnits(PredictedTableName, fieldName);
                        if (units != null && units != "()")
                        {
                            string unitsMinusBrackets = units.Replace("(", "").Replace(")", "");
                            unitFieldNames.Add("Predicted." + fieldName);
                            unitNames.Add(unitsMinusBrackets);
                            unitFieldNames.Add("Observed." + fieldName);
                            unitNames.Add(unitsMinusBrackets);
                        }
                    }
                    if (unitNames.Count > 0)
                    {
                        dataStore.AddUnitsForTable(Name, unitFieldNames, unitNames);
                    }
                }

                else
                {
                    // Determine what went wrong.
                    DataTable predictedData = dataStore.RunQuery("SELECT * FROM " + PredictedTableName);
                    DataTable observedData  = dataStore.RunQuery("SELECT * FROM " + ObservedTableName);
                    if (predictedData == null || predictedData.Rows.Count == 0)
                    {
                        throw new Exception(Name + ": Cannot find any predicted data.");
                    }
                    else if (observedData == null || observedData.Rows.Count == 0)
                    {
                        throw new Exception(Name + ": Cannot find any observed data in node: " + ObservedTableName + ". Check for missing observed file or move " + ObservedTableName + " to top of child list under DataStore (order is important!)");
                    }
                    else
                    {
                        throw new Exception(Name + ": Observed data was found but didn't match the predicted values. Make sure the values in the SimulationName column match the simulation names in the user interface. Also ensure column names in the observed file match the APSIM report column names.");
                    }
                }
            }
        }
    void GetReport()
    {
        try
        {
            vdm             = new VehicleDBMgr();
            pnlHide.Visible = true;
            DateTime fromdate  = DateTime.Now;
            string[] datestrig = txtdate.Text.Split(' ');
            if (datestrig.Length > 1)
            {
                if (datestrig[0].Split('-').Length > 0)
                {
                    string[] dates = datestrig[0].Split('-');
                    string[] times = datestrig[1].Split(':');
                    fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
                }
            }
            DateTime todate      = DateTime.Now;
            string[] todatestrig = txtTodate.Text.Split(' ');
            if (todatestrig.Length > 1)
            {
                if (todatestrig[0].Split('-').Length > 0)
                {
                    string[] dates = todatestrig[0].Split('-');
                    string[] times = todatestrig[1].Split(':');
                    todate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
                }
            }
            DataTable dtdue = new DataTable();
            if (Session["branch"].ToString() == "172")
            {
                cmd = new MySqlCommand("SELECT branchdata.BranchName, productsdata.ProductName, ROUND(SUM(indents_subtable.DeliveryQty), 2) AS DeliveryQty,ROUND(SUM(indents_subtable.UnitCost * indents_subtable.DeliveryQty) ) AS SaleValue, products_category.Categoryname FROM branchmappingtable INNER JOIN branchdata ON branchmappingtable.SubBranch = branchdata.sno INNER JOIN  branchmappingtable branchmappingtable_1 ON branchdata.sno = branchmappingtable_1.SuperBranch INNER JOIN branchdata branchdata_1 ON branchmappingtable_1.SubBranch = branchdata_1.sno INNER JOIN indents ON branchdata_1.sno = indents.Branch_id INNER JOIN indents_subtable ON indents.IndentNo = indents_subtable.IndentNo INNER JOIN productsdata ON indents_subtable.Product_sno = productsdata.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno WHERE (branchmappingtable.SuperBranch = @BranchID) AND (branchmappingtable.SubBranch NOT IN (1801)) AND (indents.I_date BETWEEN @d1 AND @d2) GROUP BY branchdata.BranchName, productsdata.ProductName ORDER BY branchmappingtable.SuperBranch");
                //cmd = new MySqlCommand("SELECT branchdata.BranchName, productsdata.ProductName, ROUND(SUM(indents_subtable.DeliveryQty), 2) AS DeliveryQty,ROUND(SUM(indents_subtable.UnitCost * indents_subtable.DeliveryQty) ) AS SaleValue, products_category.Categoryname FROM branchmappingtable INNER JOIN branchdata ON branchmappingtable.SubBranch = branchdata.sno INNER JOIN  branchmappingtable branchmappingtable_1 ON branchdata.sno = branchmappingtable_1.SuperBranch INNER JOIN branchdata branchdata_1 ON branchmappingtable_1.SubBranch = branchdata_1.sno INNER JOIN indents ON branchdata_1.sno = indents.Branch_id INNER JOIN indents_subtable ON indents.IndentNo = indents_subtable.IndentNo INNER JOIN productsdata ON indents_subtable.Product_sno = productsdata.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno WHERE (branchmappingtable.SuperBranch = @BranchID) AND (branchmappingtable.SubBranch NOT IN (538,2749, 3928, 1801, 3625)) AND (indents.I_date BETWEEN @d1 AND @d2) GROUP BY branchdata.BranchName, productsdata.ProductName");
                cmd.Parameters.AddWithValue("@BranchID", Session["branch"].ToString());
                cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate.AddDays(-1)));
                cmd.Parameters.AddWithValue("@d2", GetHighDate(todate.AddDays(-1)));
                dtdue = vdm.SelectQuery(cmd).Tables[0];
            }
            else if (Session["branch"].ToString() == "1801")
            {
                cmd = new MySqlCommand("SELECT branchdata.BranchName, productsdata.ProductName, ROUND(SUM(indents_subtable.DeliveryQty), 2) AS DeliveryQty,ROUND(SUM(indents_subtable.UnitCost * indents_subtable.DeliveryQty) ) AS SaleValue, products_category.Categoryname FROM branchmappingtable INNER JOIN branchdata ON branchmappingtable.SubBranch = branchdata.sno INNER JOIN  branchmappingtable branchmappingtable_1 ON branchdata.sno = branchmappingtable_1.SuperBranch INNER JOIN branchdata branchdata_1 ON branchmappingtable_1.SubBranch = branchdata_1.sno INNER JOIN indents ON branchdata_1.sno = indents.Branch_id INNER JOIN indents_subtable ON indents.IndentNo = indents_subtable.IndentNo INNER JOIN productsdata ON indents_subtable.Product_sno = productsdata.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno WHERE (branchmappingtable.SuperBranch = @BranchID) AND (indents.I_date BETWEEN @d1 AND @d2) GROUP BY branchdata.BranchName, productsdata.ProductName");
                cmd.Parameters.AddWithValue("@BranchID", Session["branch"].ToString());
                cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate.AddDays(-1)));
                cmd.Parameters.AddWithValue("@d2", GetHighDate(todate.AddDays(-1)));
                dtdue = vdm.SelectQuery(cmd).Tables[0];
            }
            else if (Session["branch"].ToString() == "3625")
            {
                cmd = new MySqlCommand("SELECT branchdata.BranchName, productsdata.ProductName, ROUND(SUM(indents_subtable.DeliveryQty), 2) AS DeliveryQty,ROUND(SUM(indents_subtable.UnitCost * indents_subtable.DeliveryQty) ) AS SaleValue, products_category.Categoryname FROM branchmappingtable INNER JOIN branchdata ON branchmappingtable.SubBranch = branchdata.sno INNER JOIN  branchmappingtable branchmappingtable_1 ON branchdata.sno = branchmappingtable_1.SuperBranch INNER JOIN branchdata branchdata_1 ON branchmappingtable_1.SubBranch = branchdata_1.sno INNER JOIN indents ON branchdata_1.sno = indents.Branch_id INNER JOIN indents_subtable ON indents.IndentNo = indents_subtable.IndentNo INNER JOIN productsdata ON indents_subtable.Product_sno = productsdata.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno WHERE (branchmappingtable.SuperBranch = @BranchID) AND (indents.I_date BETWEEN @d1 AND @d2) GROUP BY branchdata.BranchName, productsdata.ProductName");
                cmd.Parameters.AddWithValue("@BranchID", Session["branch"].ToString());
                cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate.AddDays(-1)));
                cmd.Parameters.AddWithValue("@d2", GetHighDate(todate.AddDays(-1)));
                dtdue = vdm.SelectQuery(cmd).Tables[0];
            }
            else
            {
                cmd = new MySqlCommand("SELECT branchdata.BranchName, productsdata.ProductName, ROUND(SUM(indents_subtable.DeliveryQty), 2) AS DeliveryQty,ROUND(SUM(indents_subtable.UnitCost * indents_subtable.DeliveryQty) ) AS SaleValue, products_category.Categoryname FROM branchmappingtable INNER JOIN branchdata ON branchmappingtable.SubBranch = branchdata.sno INNER JOIN  branchmappingtable branchmappingtable_1 ON branchdata.sno = branchmappingtable_1.SuperBranch INNER JOIN branchdata branchdata_1 ON branchmappingtable_1.SubBranch = branchdata_1.sno INNER JOIN indents ON branchdata_1.sno = indents.Branch_id INNER JOIN indents_subtable ON indents.IndentNo = indents_subtable.IndentNo INNER JOIN productsdata ON indents_subtable.Product_sno = productsdata.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno WHERE (branchmappingtable.SuperBranch = @BranchID) AND (indents.I_date BETWEEN @d1 AND @d2) GROUP BY branchdata.BranchName, productsdata.ProductName");
                cmd.Parameters.AddWithValue("@BranchID", Session["branch"].ToString());
                cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate.AddDays(-1)));
                cmd.Parameters.AddWithValue("@d2", GetHighDate(todate.AddDays(-1)));
                dtdue = vdm.SelectQuery(cmd).Tables[0];
            }



            cmd = new MySqlCommand("SELECT   products_subcategory.rank, branchdata.BranchName, products_subcategory.tempcatsno, productsdata.tempsubcatsno, productsdata.ProductName, ROUND(SUM(indents_subtable.DeliveryQty), 2) AS DeliveryQty, ROUND(SUM(indents_subtable.UnitCost * indents_subtable.DeliveryQty), 2) AS salevalue, products_category.description AS Categoryname FROM  (SELECT IndentNo, Branch_id, I_date, Status, IndentType FROM  indents WHERE (I_date BETWEEN @d1 AND @d2) AND (Status <> 'D')) indent INNER JOIN branchdata ON indent.Branch_id = branchdata.sno INNER JOIN indents_subtable ON indent.IndentNo = indents_subtable.IndentNo INNER JOIN branchmappingtable ON branchdata.sno = branchmappingtable.SubBranch INNER JOIN branchdata branchdata_1 ON branchmappingtable.SuperBranch = branchdata_1.sno INNER JOIN productsdata ON indents_subtable.Product_sno = productsdata.sno INNER JOIN products_subcategory ON productsdata.tempsubcatsno = products_subcategory.tempsub_catsno INNER JOIN products_category ON products_subcategory.tempcatsno = products_category.tempcatsno WHERE (branchmappingtable.SuperBranch = @BranchID) AND (indents_subtable.DeliveryQty <> 0) GROUP BY branchdata.sno, branchdata.BranchName, productsdata.ProductName ORDER BY branchdata.BranchName");
            cmd.Parameters.AddWithValue("@BranchID", Session["branch"].ToString());
            cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate).AddDays(-1));
            cmd.Parameters.AddWithValue("@d2", GetHighDate(todate).AddDays(-1));
            DataTable DtLocalTemp = vdm.SelectQuery(cmd).Tables[0];


            cmd = new MySqlCommand("SELECT ff.Sno, Leaks.tempcatsno,Leaks.rank,Leaks.ProductName,Leaks.tempsubcatsno, Leaks.Categoryname, Leaks.ProductId, ROUND(SUM(Leaks.DeliveryQty),2) AS DeliveryQty FROM (SELECT tripdata.Sno, productsdata.ProductName,productsdata.tempsubcatsno, products_category.Categoryname,products_subcategory.rank, products_category.tempcatsno,tripsubdata.ProductId, tripsubdata.Qty AS DeliveryQty FROM tripsubdata INNER JOIN productsdata ON tripsubdata.ProductId = productsdata.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno INNER JOIN tripdata ON tripsubdata.Tripdata_sno = tripdata.Sno WHERE (tripdata.I_Date BETWEEN @d1 AND @d2) AND (tripsubdata.Qty>0)) Leaks INNER JOIN (SELECT DispName, Sno, DespSno, I_Date FROM (SELECT dispatch.DispName, tripdata_1.Sno, dispatch.sno AS DespSno, tripdata_1.I_Date FROM branchdata INNER JOIN dispatch ON branchdata.sno = dispatch.Branch_Id INNER JOIN triproutes ON dispatch.sno = triproutes.RouteID INNER JOIN tripdata tripdata_1 ON triproutes.Tripdata_sno = tripdata_1.Sno WHERE (dispatch.Branch_Id = @BranchID) AND (dispatch.Route_id IS NULL) AND (dispatch.DispType <> 'spl') AND (dispatch.DispType <> 'agent') AND (dispatch.DispType <> 'so')) TripInfo) ff ON ff.Sno = Leaks.Sno GROUP BY Leaks.ProductName");
            cmd.Parameters.AddWithValue("@BranchID", Session["branch"].ToString());
            cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate));
            cmd.Parameters.AddWithValue("@d2", GetHighDate(todate));
            DataTable dtlocal = vdm.SelectQuery(cmd).Tables[0];
            dtlocal.Merge(DtLocalTemp);

            //cmd = new MySqlCommand("SELECT ff.Sno, Leaks.ProductName, Leaks.Categoryname, Leaks.ProductId, SUM(Leaks.DeliveryQty) AS DeliveryQty FROM (SELECT        tripdata.Sno, productsdata.ProductName, products_category.Categoryname, tripsubdata.ProductId, tripsubdata.Qty AS DeliveryQty FROM tripsubdata INNER JOIN productsdata ON tripsubdata.ProductId = productsdata.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno INNER JOIN tripdata ON tripsubdata.Tripdata_sno = tripdata.Sno WHERE (tripdata.I_Date BETWEEN @d1 AND @d2)) Leaks INNER JOIN (SELECT DispName, Sno, DespSno, I_Date FROM (SELECT dispatch.DispName, tripdata_1.Sno, dispatch.sno AS DespSno, tripdata_1.I_Date FROM branchdata INNER JOIN dispatch ON branchdata.sno = dispatch.Branch_Id INNER JOIN triproutes ON dispatch.sno = triproutes.RouteID INNER JOIN tripdata tripdata_1 ON triproutes.Tripdata_sno = tripdata_1.Sno WHERE (dispatch.Branch_Id = @BranchID) AND (dispatch.Route_id IS NULL) AND (dispatch.DispMode <> 'SPL')) TripInfo) ff ON ff.Sno = Leaks.Sno GROUP BY Leaks.ProductName");
            //cmd.Parameters.AddWithValue("@BranchID", Session["branch"].ToString());
            //cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate));
            //cmd.Parameters.AddWithValue("@d2", GetHighDate(todate));
            //DataTable dtlocal = vdm.SelectQuery(cmd).Tables[0];


            cmd = new MySqlCommand("SELECT branchproducts.branch_sno, branchproducts.product_sno, branchproducts.Rank, productsdata.ProductName, products_category.Categoryname FROM branchproducts INNER JOIN productsdata ON branchproducts.product_sno = productsdata.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno WHERE (branchproducts.branch_sno = @BranchID) ORDER BY branchproducts.Rank");
            //cmd.Parameters.AddWithValue("@Flag", "1");
            cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate.AddDays(-1)));
            cmd.Parameters.AddWithValue("@d2", GetHighDate(todate.AddDays(-1)));
            cmd.Parameters.AddWithValue("@BranchID", Session["branch"].ToString());
            cmd.Parameters.AddWithValue("@Branch", Session["branch"].ToString());
            DataTable produtstbl = vdm.SelectQuery(cmd).Tables[0];
            if (produtstbl.Rows.Count > 0)
            {
                DataView  view1            = new DataView(produtstbl);
                DataView  view             = new DataView(dtdue);
                DataTable distinctproducts = view1.ToTable(true, "ProductName", "Categoryname");
                Report = new DataTable();
                Report.Columns.Add("SNo");
                Report.Columns.Add("Agent Name");
                int count = 0;
                foreach (DataRow dr in distinctproducts.Rows)
                {
                    Report.Columns.Add(dr["ProductName"].ToString()).DataType = typeof(Double);
                    count++;
                }
                Report.Columns.Add("Total Milk", typeof(Double)).SetOrdinal(count + 2);
                Report.Columns.Add("Total Curd&BM", typeof(Double)).SetOrdinal(count + 3);
                Report.Columns.Add("Total Lts", typeof(Double)).SetOrdinal(count + 4);
                int       i = 1;
                double    totalsalevalue = 0;
                DataTable distincttable  = view.ToTable(true, "BranchName");
                foreach (DataRow branch in distincttable.Rows)
                {
                    DataRow newrow = Report.NewRow();
                    newrow["SNo"]        = i;
                    newrow["Agent Name"] = branch["BranchName"].ToString();
                    double total          = 0;
                    double totalcurdandBM = 0;
                    foreach (DataRow dr in dtdue.Rows)
                    {
                        if (branch["BranchName"].ToString() == dr["BranchName"].ToString())
                        {
                            double qtyvalue    = 0;
                            string DeliveryQty = dr["DeliveryQty"].ToString();
                            if (DeliveryQty == "")
                            {
                            }
                            else
                            {
                                double assqty     = 0;
                                double curdBm     = 0;
                                double Buttermilk = 0;
                                double salevalue  = 0;
                                newrow[dr["ProductName"].ToString()] = DeliveryQty;
                                //if (dr["Categoryname"].ToString() == "MILK")
                                //{
                                //double.TryParse(dr["DeliveryQty"].ToString(), out qtyvalue);
                                //total += qtyvalue;
                                if (dr["Categoryname"].ToString() == "MILK")
                                {
                                    double.TryParse(dr["DeliveryQty"].ToString(), out assqty);
                                    total += assqty;
                                }
                                if (dr["Categoryname"].ToString() == "CURD")
                                {
                                    double.TryParse(dr["DeliveryQty"].ToString(), out curdBm);
                                    totalcurdandBM += curdBm;
                                    //double.TryParse(dr["SaleValue"].ToString(), out salevalue);
                                    //totalsalevalue += salevalue;
                                }
                                if (dr["Categoryname"].ToString() == "ButterMilk")
                                {
                                    double.TryParse(dr["DeliveryQty"].ToString(), out Buttermilk);
                                    totalcurdandBM += Buttermilk;
                                    //double.TryParse(dr["SaleValue"].ToString(), out salevalue);
                                    //totalsalevalue += salevalue;
                                }
                            }
                            //}
                        }
                    }
                    newrow["Total Milk"]    = total;
                    newrow["Total Curd&BM"] = totalcurdandBM;
                    newrow["Total Lts"]     = total + totalcurdandBM;
                    Report.Rows.Add(newrow);
                    i++;
                }
                DataRow newrow1 = Report.NewRow();
                newrow1["SNo"]        = i;
                newrow1["Agent Name"] = "LOCAL SALE";
                double total1          = 0;
                double totalcurdandBM1 = 0;
                foreach (DataRow dr in dtlocal.Rows)
                {
                    double qtyvalue    = 0;
                    string DeliveryQty = dr["DeliveryQty"].ToString();
                    if (DeliveryQty == "")
                    {
                    }
                    else
                    {
                        double assqty     = 0;
                        double curdBm     = 0;
                        double Buttermilk = 0;
                        double dqty       = 0;
                        double.TryParse(DeliveryQty, out dqty);
                        dqty = Math.Round(dqty, 2);
                        double salevalue = 0;
                        newrow1[dr["ProductName"].ToString()] = dqty;
                        //if (dr["Categoryname"].ToString() == "MILK")
                        //{
                        //double.TryParse(dr["DeliveryQty"].ToString(), out qtyvalue);
                        //total += qtyvalue;
                        string catname = dr["Categoryname"].ToString();
                        if (dr["Categoryname"].ToString() == "MILK" || dr["Categoryname"].ToString() == "Milk")
                        {
                            double.TryParse(dr["DeliveryQty"].ToString(), out assqty);
                            assqty  = Math.Round(assqty, 2);
                            total1 += assqty;
                        }
                        if (dr["Categoryname"].ToString() == "CURD" || dr["Categoryname"].ToString() == "Curd")
                        {
                            double.TryParse(dr["DeliveryQty"].ToString(), out curdBm);
                            curdBm           = Math.Round(curdBm, 2);
                            totalcurdandBM1 += curdBm;
                            //double.TryParse(dr["SaleValue"].ToString(), out salevalue);
                            //totalsalevalue += salevalue;
                        }
                        if (dr["Categoryname"].ToString() == "ButterMilk")
                        {
                            double.TryParse(dr["DeliveryQty"].ToString(), out Buttermilk);
                            Buttermilk       = Math.Round(Buttermilk, 2);
                            totalcurdandBM1 += Buttermilk;
                            //double.TryParse(dr["SaleValue"].ToString(), out salevalue);
                            //totalsalevalue += salevalue;
                        }
                    }
                    //}
                }
                newrow1["Total Milk"]    = total1;
                newrow1["Total Curd&BM"] = totalcurdandBM1;
                newrow1["Total Lts"]     = total1 + totalcurdandBM1;
                Report.Rows.Add(newrow1);
                i++;

                foreach (var column in Report.Columns.Cast <DataColumn>().ToArray())
                {
                    if (Report.AsEnumerable().All(dr => dr.IsNull(column)))
                    {
                        Report.Columns.Remove(column);
                    }
                }

                DataRow newvartical = Report.NewRow();
                newvartical["Agent Name"] = "Total";
                double val = 0.0;
                foreach (DataColumn dc in Report.Columns)
                {
                    if (dc.DataType == typeof(Double))
                    {
                        val = 0.0;
                        double.TryParse(Report.Compute("sum([" + dc.ToString() + "])", "[" + dc.ToString() + "]<>'0'").ToString(), out val);
                        newvartical[dc.ToString()] = val;
                    }
                }
                Report.Rows.Add(newvartical);
                //DataRow newvartical1 = Report.NewRow();
                //newvartical1["Agent Name"] = "AvgRate";
                //double avg = 0.0;
                //foreach (DataColumn dc in Report.Columns)
                //{
                //    if (dc.DataType == typeof(Double))
                //    {
                //        val = 0.0;
                //        double.TryParse(Report.Compute("sum([" + dc.ToString() + "])", "[" + dc.ToString() + "]<>'0'").ToString(), out val);
                //        newvartical1[dc.ToString()] = val / totalsalevalue;
                //    }
                //}
                //Report.Rows.Add(newvartical1);
                foreach (DataColumn col in Report.Columns)
                {
                    string Pname       = col.ToString();
                    string ProductName = col.ToString();
                    ProductName = GetSpace(ProductName);
                    Report.Columns[Pname].ColumnName = ProductName;
                }
                grdReports.DataSource = Report;
                grdReports.DataBind();
            }
        }
        catch (Exception ex)
        {
            lblmsg.Text           = ex.Message;
            grdReports.DataSource = Report;
            grdReports.DataBind();
        }
    }
        private void button2_Click(object sender, EventArgs e)
        {
            Excel.Workbook MyBookRCT2 = null;
            Excel.Workbook MyBook     = null;

            try
            {
                if (dataGridView1.Rows.Count > 0)
                {
                    FolderBrowserDialog f = new FolderBrowserDialog();
                    f.Description = "Elija una carpeta para guardar el template.";
                    f.ShowDialog();

                    string path = f.SelectedPath;
                    if (!string.IsNullOrEmpty(path))
                    {
                        System.IO.File.Copy(Application.StartupPath + "\\Resources\\ORCT_Demasias.xlsx", path + "\\ORCT_Demasias.xlsx", true);
                        System.IO.File.Copy(Application.StartupPath + "\\Resources\\RCT4_Demasias.xlsx", path + "\\RCT4_Demasias.xlsx", true);
                        int lastRow = 0;

                        Excel.Application MyApp   = null;
                        Excel.Worksheet   MySheet = null;

                        MyApp         = new Excel.Application();
                        MyApp.Visible = false;
                        MyBook        = MyApp.Workbooks.Open(path + "\\ORCT_Demasias.xlsx");
                        MySheet       = (Excel.Worksheet)MyBook.Sheets[1]; // Explicit cast is not required here
                        lastRow       = 3;

                        DataTable Datos = dataGridView1.DataSource as DataTable;

                        toolStatus.Text     = "Generando: ORCT_Demasias.xlsx";
                        progressBar.Maximum = (from item in Datos.AsEnumerable()
                                               where !string.IsNullOrEmpty(item.Field <string>("Cta Mov no identificados"))
                                               select item).Count();
                        progressBar.Value = 0;
                        foreach (DataRow item in Datos.Rows)
                        {
                            if (!string.IsNullOrEmpty(item.Field <string>("Cta Mov no identificados")))
                            {
                                MySheet.Cells[lastRow, 1] = item.Field <Int32>("Folio");
                                MySheet.Cells[lastRow, 2] = "rAccount";
                                MySheet.Cells[lastRow, 3] = item.Field <DateTime>("Fecha").ToString("yyyyMMdd");
                                MySheet.Cells[lastRow, 4] = item.Field <string>("Cta Mov no identificados");
                                MySheet.Cells[lastRow, 5] = item.Field <decimal>("Monto").ToString();
                                MySheet.Cells[lastRow, 6] = item.Field <DateTime>("Fecha").ToString("yyyyMMdd");
                                MySheet.Cells[lastRow, 7] = "Demasía - " + item.Field <string>("Cliente").Trim().TrimEnd(',');
                                lastRow++;

                                progressBar.PerformStep();
                            }
                        }

                        MyBook.Save();
                        MyBook.Close();

                        //this.Detalle();


                        Excel.Application MyAppRCT2   = null;
                        Excel.Worksheet   MySheetRCT2 = null;

                        MyAppRCT2         = new Excel.Application();
                        MyAppRCT2.Visible = false;
                        MyBookRCT2        = MyAppRCT2.Workbooks.Open(path + "\\RCT4_Demasias.xlsx");
                        MySheetRCT2       = (Excel.Worksheet)MyBookRCT2.Sheets[1]; // Explicit cast is not required here
                        lastRow           = 3;

                        toolStatus.Text     = "Generando: RCT4_Demasias.xlsx";
                        progressBar.Maximum = (from item in Datos.AsEnumerable()
                                               where !string.IsNullOrEmpty(item.Field <string>("Cta Mov no identificados"))
                                               select item).Count();
                        progressBar.Value = 0;
                        foreach (DataRow item in Datos.Rows)
                        {
                            if (!string.IsNullOrEmpty(item.Field <string>("Cta Mov no identificados")))
                            {
                                MySheetRCT2.Cells[lastRow, 1] = item.Field <Int32>("Folio");
                                MySheetRCT2.Cells[lastRow, 2] = "1130-003-000";
                                MySheetRCT2.Cells[lastRow, 3] = (item.Field <string>("SumApplied")).ToString();
                                MySheetRCT2.Cells[lastRow, 4] = (item.Field <string>("AppliedFC")).ToString();
                                MySheetRCT2.Cells[lastRow, 5] = "Demasía - " + item.Field <string>("Cliente").Trim().TrimEnd(',');
                                lastRow++;
                                progressBar.PerformStep();
                            }
                            //   lineNum++;
                        }

                        MyBookRCT2.Save();
                        MyBookRCT2.Close();

                        toolStatus.Text     = "Actualizando registros.";
                        progressBar.Maximum = (from item in Datos.AsEnumerable()
                                               where !string.IsNullOrEmpty(item.Field <string>("Cta Mov no identificados"))
                                               select item).Count();
                        progressBar.Value = 0;
                        foreach (DataRow item in Datos.Rows)
                        {
                            if (!string.IsNullOrEmpty(item.Field <string>("Cta Mov no identificados")))
                            {
                                this.ActualizarTempleate(item.Field <Int32>("Folio"));
                                progressBar.PerformStep();
                            }
                        }
                        progressBar.Value = 0;
                        toolStatus.Text   = "Listo.";
                        MessageBox.Show("El template se genero exitosamente", "HalcoNET", MessageBoxButtons.OK, MessageBoxIcon.Information);

                        button1_Click(sender, e);
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error inesperado: " + ex.Message, "HalcoNET", MessageBoxButtons.OK, MessageBoxIcon.Error);
                toolStatus.Text   = "";
                progressBar.Value = 0;
            }
            finally
            {
                // MyBook.Close();
                //  MyBookRCT2.Close();
            }
        }
Example #37
0
        private void Efectividad_Load(object sender, EventArgs e)
        {
            try
            {
                this.Icon = ClasesSGUV.Propiedades.IconHalcoNET;

                DataTable tblTotal = new DataTable();
                tblTotal.Columns.Add("Vendedor", typeof(string));
                tblTotal.Columns.Add("Clientes", typeof(int));
                tblTotal.Columns.Add("Efectivo", typeof(int));
                tblTotal.Columns.Add("Inefectivo", typeof(int));

                tblTotal.Columns.Add("Efectividad", typeof(decimal), "Efectivo / Clientes");
                tblTotal.Columns.Add("Obj. Efectividad", typeof(decimal));
                tblTotal.Columns.Add("Obj Ctes Efectivos", typeof(decimal));
                tblTotal.Columns.Add("Venta CERO", typeof(int));

                var filterEfectivos = (from item in TablaDetalle.AsEnumerable()
                                       where item.Field <decimal>("Pronostico fin de mes(%)") >= (decimal)0.75 &&
                                       item.Field <string>("U_Efectividad") == "Y"
                                       select item);

                var filterVentaCero = (from item in TablaDetalle.AsEnumerable()
                                       where item.Field <decimal>("Pronostico fin de mes(%)") <= 0 &&
                                       item.Field <string>("U_Efectividad") == "Y"
                                       select item);
                var filterInfectivos = (from item in TablaDetalle.AsEnumerable()
                                        where item.Field <decimal>("Pronostico fin de mes(%)") < (decimal)0.75
                                        //  && item.Field<decimal>("Pronostico fin de mes(%)") > 0
                                        && item.Field <string>("U_Efectividad") == "Y"
                                        select item);
                DataRow r = tblTotal.NewRow();
                r["Vendedor"]   = "TODOS";
                r["Clientes"]   = filterEfectivos.Count() + filterInfectivos.Count();
                r["Efectivo"]   = filterEfectivos.Count();
                r["Inefectivo"] = filterInfectivos.Count();
                r["Venta CERO"] = filterVentaCero.Count();

                r["Obj. Efectividad"]   = Convert.ToDecimal(1);
                r["Obj Ctes Efectivos"] = Convert.ToDecimal(filterEfectivos.Count() + filterInfectivos.Count());

                tblTotal.Rows.Add(r);

                dataGridView1.DataSource = null;
                dataGridView1.Columns.Clear();
                dataGridView1.DataSource = tblTotal;

                List <string> Vendedores = new List <string>();
                Vendedores = (from vendor in TablaDetalle.AsEnumerable()
                              select vendor.Field <string>("Vendedor")).Distinct().ToList();

                DataTable tblVendor = new DataTable();
                tblVendor.Columns.Add("Vendedor", typeof(string));
                tblVendor.Columns.Add("Clientes", typeof(int));
                tblVendor.Columns.Add("Efectivo", typeof(int));
                tblVendor.Columns.Add("Inefectivo", typeof(int));

                tblVendor.Columns.Add("Efectividad", typeof(decimal), "Efectivo / Clientes");
                tblVendor.Columns.Add("Obj. Efectividad", typeof(decimal));
                tblVendor.Columns.Add("Obj Ctes Efectivos", typeof(decimal));
                tblVendor.Columns.Add("Venta CERO", typeof(int));

                foreach (string v in Vendedores)
                {
                    var filterEfectivosV = (from item in TablaDetalle.AsEnumerable()
                                            where item.Field <decimal>("Pronostico fin de mes(%)") >= (decimal)0.75 &&
                                            item.Field <string>("U_Efectividad") == "Y" &&
                                            item.Field <string>("Vendedor") == v
                                            select item);

                    var filterVentaCeroV = (from item in TablaDetalle.AsEnumerable()
                                            where item.Field <decimal>("Pronostico fin de mes(%)") <= 0 &&
                                            item.Field <string>("U_Efectividad") == "Y" &&
                                            item.Field <string>("Vendedor") == v
                                            select item);

                    var filterInfectivosV = (from item in TablaDetalle.AsEnumerable()
                                             where item.Field <decimal>("Pronostico fin de mes(%)") < (decimal)0.75
                                             // && item.Field<decimal>("Pronostico fin de mes(%)") > 0
                                             && item.Field <string>("U_Efectividad") == "Y" &&
                                             item.Field <string>("Vendedor") == v
                                             select item);

                    DataRow rv = tblVendor.NewRow();
                    rv["Vendedor"]   = v;
                    rv["Clientes"]   = filterEfectivosV.Count() + filterInfectivosV.Count();
                    rv["Efectivo"]   = filterEfectivosV.Count();
                    rv["Inefectivo"] = filterInfectivosV.Count();
                    rv["Venta CERO"] = filterVentaCeroV.Count();

                    rv["Obj. Efectividad"]   = Convert.ToDecimal(1);
                    rv["Obj Ctes Efectivos"] = Convert.ToDecimal(filterEfectivosV.Count() + filterInfectivosV.Count());

                    tblVendor.Rows.Add(rv);
                }

                dataGridView2.DataSource = tblVendor;

                FormatoGrid(dataGridView1);
                FormatoGrid(dataGridView2);
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex.Message);
            }
        }
        /// <summary>
        /// Joins the passed in DataTables on the colToJoinOn.
        /// <para>Returns an appropriate DataTable with zero rows if the colToJoinOn does not exist in both tables.</para>
        /// </summary>
        /// <param name="dtblLeft"></param>
        /// <param name="dtblRight"></param>
        /// <param name="colToJoinOn"></param>
        /// <param name="joinType"></param>
        /// <returns></returns>
        /// <remarks>
        /// <para>http://stackoverflow.com/questions/2379747/create-combined-datatable-from-two-datatables-joined-with-linq-c-sharp?rq=1</para>
        /// <para>http://msdn.microsoft.com/en-us/library/vstudio/bb397895.aspx</para>
        /// <para>http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html</para>
        /// <para>http://stackoverflow.com/questions/406294/left-join-and-left-outer-join-in-sql-server</para>
        /// </remarks>
        public static DataTable JoinTwoDataTablesOnOneColumn(DataTable dtblLeft, DataTable dtblRight, string colToJoinOn, JoinType joinType)
        {
            //Change column name to a temp name so the LINQ for getting row data will work properly.
            string strTempColName = colToJoinOn + "_2";

            if (dtblRight.Columns.Contains(colToJoinOn))
            {
                dtblRight.Columns[colToJoinOn].ColumnName = strTempColName;
            }

            //Get columns from dtblLeft
            DataTable dtblResult = dtblLeft.Clone();

            //Get columns from dtblRight
            var dt2Columns = dtblRight.Columns.OfType <DataColumn>().Select(dc => new DataColumn(dc.ColumnName, dc.DataType, dc.Expression, dc.ColumnMapping));

            //Get columns from dtblRight that are not in dtblLeft
            var dt2FinalColumns = from dc in dt2Columns.AsEnumerable()
                                  where !dtblResult.Columns.Contains(dc.ColumnName)
                                  select dc;

            //Add the rest of the columns to dtblResult
            dtblResult.Columns.AddRange(dt2FinalColumns.ToArray());

            //No reason to continue if the colToJoinOn does not exist in both DataTables.
            if (!dtblLeft.Columns.Contains(colToJoinOn) || (!dtblRight.Columns.Contains(colToJoinOn) && !dtblRight.Columns.Contains(strTempColName)))
            {
                if (!dtblResult.Columns.Contains(colToJoinOn))
                {
                    dtblResult.Columns.Add(colToJoinOn);
                }
                return(dtblResult);
            }

            switch (joinType)
            {
            default:
            case JoinType.Inner:
                #region Inner
                //get row data
                //To use the DataTable.AsEnumerable() extension method you need to add a reference to the System.Data.DataSetExtension assembly in your project.
                var rowDataLeftInner = from rowLeft in dtblLeft.AsEnumerable()
                                       join rowRight in dtblRight.AsEnumerable() on rowLeft[colToJoinOn] equals rowRight[strTempColName]
                                       select rowLeft.ItemArray.Concat(rowRight.ItemArray).ToArray();


                //Add row data to dtblResult
                foreach (object[] values in rowDataLeftInner)
                {
                    dtblResult.Rows.Add(values);
                }

                #endregion
                break;

            case JoinType.Left:
                #region Left
                var rowDataLeftOuter = from rowLeft in dtblLeft.AsEnumerable()
                                       join rowRight in dtblRight.AsEnumerable() on rowLeft[colToJoinOn] equals rowRight[strTempColName] into gj
                                       from subRight in gj.DefaultIfEmpty()
                                       select rowLeft.ItemArray.Concat((subRight == null)?(dtblRight.NewRow().ItemArray) : subRight.ItemArray).ToArray();


                //Add row data to dtblResult
                foreach (object[] values in rowDataLeftOuter)
                {
                    dtblResult.Rows.Add(values);
                }

                #endregion
                break;
            }

            //Change column name back to original
            dtblRight.Columns[strTempColName].ColumnName = colToJoinOn;

            //Remove extra column from result
            dtblResult.Columns.Remove(strTempColName);

            return(dtblResult);
        }
Example #39
0
        // GET: /Bodega/
        public ActionResult Index(string sortOrder,
                                  String SearchField,
                                  String SearchCondition,
                                  String SearchText,
                                  String Export,
                                  int?PageSize,
                                  int?page,
                                  string command)
        {
            if (command == "Show All")
            {
                SearchField                = null;
                SearchCondition            = null;
                SearchText                 = null;
                Session["SearchField"]     = null;
                Session["SearchCondition"] = null;
                Session["SearchText"]      = null;
            }
            else if (command == "Add New Record")
            {
                return(RedirectToAction("Create"));
            }
            else if (command == "Export")
            {
                Session["Export"] = Export;
            }
            else if (command == "Search" | command == "Page Size")
            {
                if (!string.IsNullOrEmpty(SearchText))
                {
                    Session["SearchField"]     = SearchField;
                    Session["SearchCondition"] = SearchCondition;
                    Session["SearchText"]      = SearchText;
                }
            }
            if (command == "Page Size")
            {
                Session["PageSize"] = PageSize;
            }

            ViewData["SearchFields"]     = GetFields((Session["SearchField"] == null ? "Bodega I D" : Convert.ToString(Session["SearchField"])));
            ViewData["SearchConditions"] = Library.GetConditions((Session["SearchCondition"] == null ? "Contains" : Convert.ToString(Session["SearchCondition"])));
            ViewData["SearchText"]       = Session["SearchText"];
            ViewData["Exports"]          = Library.GetExports((Session["Export"] == null ? "Pdf" : Convert.ToString(Session["Export"])));
            ViewData["PageSizes"]        = Library.GetPageSizes();

            ViewData["CurrentSort"]         = sortOrder;
            ViewData["BodegaIDSortParm"]    = sortOrder == "BodegaID_asc" ? "BodegaID_desc" : "BodegaID_asc";
            ViewData["ProductosIDSortParm"] = sortOrder == "ProductosID_asc" ? "ProductosID_desc" : "ProductosID_asc";
            ViewData["ObservacionSortParm"] = sortOrder == "Observacion_asc" ? "Observacion_desc" : "Observacion_asc";

            dtBodega = BodegaData.SelectAll();

            try
            {
                if (!string.IsNullOrEmpty(Convert.ToString(Session["SearchField"])) & !string.IsNullOrEmpty(Convert.ToString(Session["SearchCondition"])) & !string.IsNullOrEmpty(Convert.ToString(Session["SearchText"])))
                {
                    dtBodega = BodegaData.Search(Convert.ToString(Session["SearchField"]), Convert.ToString(Session["SearchCondition"]), Convert.ToString(Session["SearchText"]));
                }
            }
            catch { }

            var Query = from rowBodega in dtBodega.AsEnumerable()
                        select new Bodega()
            {
                BodegaID      = rowBodega.Field <String>("BodegaID")
                , ProductosID = rowBodega.Field <String>("ProductosID")
                , Observacion = rowBodega.Field <String>("Observacion")
            };

            switch (sortOrder)
            {
            case "BodegaID_desc":
                Query = Query.OrderByDescending(s => s.BodegaID);
                break;

            case "BodegaID_asc":
                Query = Query.OrderBy(s => s.BodegaID);
                break;

            case "ProductosID_desc":
                Query = Query.OrderByDescending(s => s.ProductosID);
                break;

            case "ProductosID_asc":
                Query = Query.OrderBy(s => s.ProductosID);
                break;

            case "Observacion_desc":
                Query = Query.OrderByDescending(s => s.Observacion);
                break;

            case "Observacion_asc":
                Query = Query.OrderBy(s => s.Observacion);
                break;

            default:      // Name ascending
                Query = Query.OrderBy(s => s.BodegaID);
                break;
            }

            if (command == "Export")
            {
                GridView  gv = new GridView();
                DataTable dt = new DataTable();
                dt.Columns.Add("Bodega I D", typeof(string));
                dt.Columns.Add("Productos I D", typeof(string));
                dt.Columns.Add("Observacion", typeof(string));
                foreach (var item in Query)
                {
                    dt.Rows.Add(
                        item.BodegaID
                        , item.ProductosID
                        , item.Observacion
                        );
                }
                gv.DataSource = dt;
                gv.DataBind();
                ExportData(Export, gv, dt);
            }

            int pageNumber = (page ?? 1);
            int?pageSZ     = (Convert.ToInt32(Session["PageSize"]) == 0 ? 5 : Convert.ToInt32(Session["PageSize"]));

            return(View(Query.ToPagedList(pageNumber, (pageSZ ?? 5))));
        }
 public int DeleteCount(DataTable data)
 {
     return(data?.AsEnumerable().FirstOrDefault()?.Field <int>(0) ?? 0);
 }
Example #41
0
        public static void GetAllBMZComposite()
        {
            DataClass dc = new DataClass("KontinentDBEntity.db");
            DataTable dt = dc.SelectQuery(
                "select _typeBMZ.idTypeBMZ,_typeBMZ.nameBMZ, _typeBMZ.indexBMZ, _typeBMZ.maxPowerTransform, " +
                "_typeBMZ.maxWidthVNSecFirst, _typeBMZ.maxWidthVNSecSecond, _typeBMZ.maxWidthNNSecFirst, " +
                "_typeBMZ.maxWidthNNSecSecond,_typeBMZ.seismicResistance,_typeBMZ.climatExecution, _typeBMZ.pathPNG, " +
                "_typeBMZ.maxWidth, _typeBMZ.maxLength, " +
                "_compositeBMZValue.Dg, _compositeBMZValue.Dg2, _compositeBMZValue.Dg3, _compositeBMZValue.H," +
                "_compositeBMZValue.Shg1, _compositeBMZValue.Shg2, _compositeBMZValue.Shg3, _compositeBMZValue.Shg4, " +
                "_compositeBMZValue.Dv1, _compositeBMZValue.Dv2," +
                "_compositeBMZValue.Gv1, _compositeBMZValue.Gv2, " +
                "_compositeBMZValue.Dn1, _compositeBMZValue.Dn2, " +
                "_compositeBMZValue.Gn1, _compositeBMZValue.Gn2," +
                "_compositeBMZValue.Dt, _compositeBMZValue.Shkn , _compositeBMZValue.Shkn2, _compositeBMZValue.Shkv, _compositeBMZValue.Shkv2, " +
                "_compositeBMZValue.Shmn  from _compositeBMZValue " +
                $"INNER JOIN _typeBMZ on _compositeBMZValue.nameComposite = _typeBMZ.idTypeBMZ ORDER BY _compositeBMZValue.nameComposite," +
                " _compositeBMZValue.Dg, _compositeBMZValue.Shg1");

            var ss = dt.AsEnumerable().ToList();

            foreach (var vari in ss)
            {
                ClassCompositeBMZ.InformationSet infSet = new ClassCompositeBMZ.InformationSet
                {
                    maxLength           = Convert.ToInt32(vari["maxLength"]),
                    maxWidth            = Convert.ToInt32(vari["maxWidth"]),
                    nameBMZ             = vari["nameBMZ"].ToString(),
                    indexBMZ            = vari["indexBMZ"].ToString(),
                    maxPowerTransform   = Convert.ToInt32(vari["maxPowerTransform"]),
                    maxWidthNNSecFirst  = Convert.ToInt32(vari["maxWidthNNSecFirst"]),
                    maxWidthNNSecSecond = Convert.ToInt32(vari["maxWidthNNSecSecond"]),
                    maxWidthVNSecFirst  = Convert.ToInt32(vari["maxWidthVNSecFirst"]),
                    maxWidthVNSecSecond = Convert.ToInt32(vari["maxWidthVNSecSecond"]),
                    climatExecution     = vari["climatExecution"].ToString(),
                    seismicResistance   = Convert.ToInt32(vari["seismicResistance"])
                };

                ClassCompositeBMZ.InformationBuild inf = new ClassCompositeBMZ.InformationBuild();
                int.TryParse(vari["Dg"].ToString(), out inf.Dg);
                int.TryParse(vari["Dg2"].ToString(), out inf.Dg2);
                int.TryParse(vari["Dg3"].ToString(), out inf.Dg3);

                inf.Dn1   = Convert.ToInt32(vari["Dn1"]);
                inf.Dn2   = Convert.ToInt32(vari["Dn2"]);
                inf.Dt    = Convert.ToInt32(vari["Dt"]);
                inf.Dv1   = Convert.ToInt32(vari["Dv1"]);
                inf.Dv2   = Convert.ToInt32(vari["Dv2"]);
                inf.Gn1   = Convert.ToInt32(vari["Gn1"]);
                inf.Gn2   = Convert.ToInt32(vari["Gn2"]);
                inf.Gv1   = Convert.ToInt32(vari["Gv1"]);
                inf.Gv2   = Convert.ToInt32(vari["Gv2"]);
                inf.H     = Convert.ToInt32(vari["H"]);
                inf.Shg1  = Convert.ToInt32(vari["Shg1"]);
                inf.Shg2  = Convert.ToInt32(vari["Shg2"]);
                inf.Shg3  = Convert.ToInt32(vari["Shg3"]);
                inf.Shg4  = Convert.ToInt32(vari["Shg4"]);
                inf.Shkn  = Convert.ToInt32(vari["Shkn"]);
                inf.Shkn2 = Convert.ToInt32(vari["Shkn2"]);
                inf.Shkv  = Convert.ToInt32(vari["Shkv"]);
                inf.Shkv2 = Convert.ToInt32(vari["Shkv"]);
                inf.Shmn  = Convert.ToInt32(vari["Shmn"]);
                int width = Convert.ToInt32(vari["Shg1"]) + Convert.ToInt32(vari["Shg2"]) +
                            Convert.ToInt32(vari["Shg3"]) + Convert.ToInt32(vari["Shg4"]);
                int length = Convert.ToInt32(vari["Dg"]);
                if (inf.Dg2 != 0)
                {
                    width = inf.Shg1 + inf.Shg2 + inf.Dg2;
                }
                if (inf.Dg3 != 0)
                {
                    length = inf.Shg1 * 2 + inf.Shg2;
                    width  = inf.Dg + inf.Shg3 + inf.Dg3;
                }

                ClassCompositeBMZ clsBMZ = new ClassCompositeBMZ()
                {
                    idBMZ     = Convert.ToInt32(vari["idTypeBMZ"].ToString()),
                    allWidth  = width,
                    allLength = length,
                    NameBMZ   = $"{vari["nameBMZ"]} ({vari["indexBMZ"]})",
                    Image     = new BitmapImage(
                        new Uri(AppDomain.CurrentDomain.BaseDirectory + @"PNG/" + vari["pathPNG"])),
                    InformationForBuild = inf,
                    InformationForSet   = infSet
                };
                _forBuildListBmzComposite.Add(clsBMZ);
            }
        }
 public static IEnumerable <TDictionary> ToDictionaries <TDictionary>(this DataTable table)
     where TDictionary : IDictionary, new()
 {
     return(ToDictionaries <DataRow, TDictionary>(table?.AsEnumerable()));
 }
Example #43
0
        public static void GetFraems()
        {
            int widthForWhere = 0;

            fr.notWall1 = 0;
            fr.notWall2 = 0;
            fr.notWall3 = 0;
            fr.notWall4 = 0;
            //fr.upPanel = 0;
            switch (_notWallGOOD)
            {
            case 1:
                fr.notWall1 = 1;
                break;

            case 2:
                fr.notWall2 = 1;
                break;

            case 3:
                fr.notWall3 = 1;
                break;

            case 4:
                fr.notWall4 = 1;
                break;

            case 24:
                fr.notWall2 = 1;
                fr.notWall4 = 1;
                break;
            }

            fr.countWall = Convert.ToInt32(dc.SelectQuery($"select countWall from _countWallFrame where nameFrame = '{_nameBlokc.Trim()}'").AsEnumerable().ToList()[0][0].ToString());
            int lenghtForWhere = _currentCompositeBmz.InformationForBuild.Dg;

            if (_currentCompositeBmz.InformationForBuild.Dg2 == 0)
            {
                switch (_numberBlock)
                {
                case 1:
                    widthForWhere = _currentCompositeBmz.InformationForBuild.Shg1;
                    break;

                case 2:
                    widthForWhere = _currentCompositeBmz.InformationForBuild.Shg2;
                    break;

                case 3:
                    widthForWhere = _currentCompositeBmz.InformationForBuild.Shg3;
                    break;

                case 4:
                    widthForWhere = _currentCompositeBmz.InformationForBuild.Shg4;
                    break;
                }
            }
            if (_currentCompositeBmz.InformationForBuild.Dg3 != 0)
            {
                switch (_numberBlock)
                {
                case 1:
                {
                    lenghtForWhere = _currentCompositeBmz.InformationForBuild.Dg;
                    if ((ContentBlock.IndexOf("1A") > -1) || (ContentBlock.IndexOf("1В") > -1))
                    {
                        widthForWhere = _currentCompositeBmz.InformationForBuild.Shg1;
                    }
                    if (ContentBlock.IndexOf("1Б") > -1)
                    {
                        widthForWhere = _currentCompositeBmz.InformationForBuild.Shg2;
                    }
                }
                break;

                case 2:
                    lenghtForWhere = _currentCompositeBmz.InformationForBuild.Dg2;
                    widthForWhere  = _currentCompositeBmz.InformationForBuild.Shg3;
                    break;

                case 3:
                    lenghtForWhere = _currentCompositeBmz.InformationForBuild.Dg3;
                    if ((ContentBlock.IndexOf("1A") > -1) || (ContentBlock.IndexOf("1В") > -1))
                    {
                        widthForWhere = _currentCompositeBmz.InformationForBuild.Shg1;
                    }
                    if (ContentBlock.IndexOf("1Б") > -1)
                    {
                        widthForWhere = _currentCompositeBmz.InformationForBuild.Shg2;
                    }
                    break;
                }
            }

            if (_currentCompositeBmz.InformationForBuild.Dg2 != 0)
            {
                switch (_numberBlock)
                {
                case 1:
                {
                    lenghtForWhere = _currentCompositeBmz.InformationForBuild.Dg;
                    widthForWhere  = _currentCompositeBmz.InformationForBuild.Shg1;
                }
                break;

                case 2:
                    lenghtForWhere = _currentCompositeBmz.InformationForBuild.Dg;
                    widthForWhere  = _currentCompositeBmz.InformationForBuild.Shg2;
                    break;

                case 3:
                    lenghtForWhere = _currentCompositeBmz.InformationForBuild.Dg2;
                    if ((ContentBlock.IndexOf("1A") > -1) || (ContentBlock.IndexOf("1В") > -1))
                    {
                        widthForWhere = _currentCompositeBmz.InformationForBuild.Shg3;
                    }
                    if (ContentBlock.IndexOf("1Б") > -1)
                    {
                        widthForWhere = _currentCompositeBmz.InformationForBuild.Shg4;
                    }
                    break;
                }
            }
            string nname_block = "";

            fr.dataForOneS.nameComercial = _nameBlokc;

            fr.antivandalProtec = 1; // УУУУУУУУУУУУУУУУУУУУУУУУУУУУУУУБББББББББББББББББРААААААААААААААААААААААААААААААТЬЬЬЬЬЬЬЬЬЬЬЬЬЬЬЬ
            switch (_nameBlokc.Trim())
            {
            case "Блок-модуль РУВН-Т-РУНН":
                fr.countTrans = 1;
                nname_block   = _nameBlokc.Trim();
                DataTable dta2 = dc.SelectQuery($"select * from _block_RU_T_RU where notWall = {_notWallGOOD} AND antiVandalProtection = {fr.antivandalProtec}");
                var       ssa2 = dta2.AsEnumerable().ToList();
                fr.dataForOneS.nameBlocks = ssa2[0]["nameBlock"].ToString();
                break;


            case "Блок-модуль Т1-Т2":
                fr.countTrans = 2;
                nname_block   = _nameBlokc.Trim();
                //DataTable dta1 = dc.SelectQuery($"select * from _block_T1_T2 where idBlock = {_idBlock}");
                //var ssa1 = dta1.AsEnumerable().ToList();
                //fr.dataForOneS.nameBlocks = ssa1[0]["nameBlock"].ToString();
                //switch (Convert.ToInt32(ssa1[0]["notWall"]))
                //{
                //    case 2:
                //        fr.notWall2 = 1;
                //        break;
                //    case 4:
                //        fr.notWall2 = 1;
                //        fr.notWall4 = 1;
                //        break;
                //}
                break;

            case "Блок-модуль РУВН":
            case "Блок-модуль1 РУВН":
            case "Блок-модуль РУНН":
                fr.countTrans = 0;
                nname_block   = "Блок-модуль РУ";
                //if (_currentCompositeBmz.InformationForBuild.H == 3040)
                //    fr.upPanel = 1;
                if (_currentCompositeBmz.InformationForBuild.Dg > 7000)
                {
                    fr.countDoors = 2;
                    if (_nameBlokc.Trim().IndexOf("Блок-модуль1 РУВН") > -1)
                    {
                        fr.countDoors = 1;
                    }
                    var vv = (_currentCompositeBmz.CollectFrame.ToList()
                              .FindAll(x => x.dataForOneS.nameComercial.IndexOf("Блок-модуль1") > -1));
                    if ((vv.Count > 0) && (_nameBlokc.IndexOf("Блок-модуль РУВН") > -1))
                    {
                        fr.countDoors = 1;
                    }
                }
                else
                {
                    fr.countDoors = 1;
                    if ((_currentCompositeBmz.CollectFrame.ToList()
                         .FindAll(x => x.dataForOneS.nameComercial == "Блок-модуль1 РУВН").Count > 0) && (_nameBlokc == "Блок - модуль РУВН"))
                    {
                        fr.countDoors = 0;
                    }
                }

                var pack = _currentCompositeBmz.dOneSQuestParametr.Pack.IndexOf("Нет") > -1 ? 1 : 0;
                /*AND upPanel = {fr.upPanel}*/
                DataTable dta = dc.SelectQuery($"select * from _block_RU where door = {fr.countDoors}  AND antiVandalProtection = {fr.antivandalProtec}" +
                                               $" AND notWall = {_notWallGOOD} AND busbarWall = 0 AND pack ={pack}");
                var ssa = dta.AsEnumerable().ToList();
                fr.dataForOneS.nameBlocks = ssa[0]["nameBlock"].ToString();
                break;

            case "Блок-модуль Т1-РУВН-Т2":
            case "Блок-модуль Т1-РУНН-Т2":
                fr.countTrans = 2;
                nname_block   = "Блок-модуль Т1-РУ-Т2";
                DataTable dta3 = dc.SelectQuery($"select * from _block_T1_RU_T2 where antiVandalProtection = {fr.antivandalProtec}");
                var       ssa3 = dta3.AsEnumerable().ToList();
                fr.dataForOneS.nameBlocks = ssa3[0]["nameBlock"].ToString();
                break;

            default:
                fr.notWall2 = 1;

                widthForWhere  = _currentCompositeBmz.InformationForBuild.Shg2;
                lenghtForWhere = _currentCompositeBmz.InformationForBuild.Dg;
                fr.notWall4    = 1;
                fr.countTrans  = 0;
                DataTable dta4 = dc.SelectQuery($"select * from _commonRoom where antiVandalProtection = {fr.antivandalProtec}");
                var       ssa4 = dta4.AsEnumerable().ToList();
                fr.dataForOneS.nameBlocks = ssa4[0]["nameBlock"].ToString();
                nname_block = _nameBlokc.Trim();
                break;
            }


            string locDoor = "";

            if ((_nameBlokc.IndexOf("Т1") > -1) && (_nameBlokc.IndexOf("РУ") > -1))
            {
                locDoor = $" AND doorLocation ='{_compositeT1RUT2}'";
            }

            //AND upPanel = { fr.upPanel }
            DataTable dt = dc.SelectQuery($"select * from _frameBlock where typeBlock = '{nname_block}' AND width = {widthForWhere} " +
                                          $"AND height = {_currentCompositeBmz.InformationForBuild.H} AND length = {lenghtForWhere} " +
                                          $"AND notWall1 = {fr.notWall1} AND notWall2 = {fr.notWall2} AND notWall3 = {fr.notWall3} AND notWall4 = {fr.notWall4} " +
                                          $"{locDoor}  AND locationCell = '{_numbOfRow}' AND compFrame = {_compositeBlock}");
            var ss = dt.AsEnumerable().ToList();

            if (ss.Count != 0)
            {
                _idRam = Convert.ToInt32(ss[0][0].ToString());



                DataTable dt2     = dc.SelectQuery($"select * from _frameBlock where idFrame = {_idRam}");
                var       ss2     = dt2.AsEnumerable().ToList();
                string    locCell = ss2[0]["locationCell"].ToString();/*.Substring(ss[0]["locationCell"].ToString().IndexOf(" "));*/
                string    door    = ss2[0]["doorLocation"].ToString() /*.Substring(ss[0]["doorLocation"].ToString().IndexOf(" "))*/;

                fr.name      = ss[0]["nameFrame"].ToString();
                fr.height    = Convert.ToInt32(ss[0]["height"].ToString());
                fr.length    = Convert.ToInt32(ss[0]["length"]);
                fr.width     = Convert.ToInt32(ss[0]["width"]);
                fr.localCell = locCell.Replace(locCell[0], char.ToUpper(locCell[0]));
                fr.localDoor = door.Replace(door[0], char.ToUpper(door[0]));
                //fr.upPanel = Convert.ToInt32(ss[0]["upPanel"].ToString());
                fr.position = ContentBlock;
                _currentCompositeBmz.CollectFrame.Add(fr);
            }
        }
Example #44
0
        public override void Execute(object parameter)
        {
            DatabaseConfigModel dbConfig     = NetworkingViewModel.DatabaseConfigModel;
            DatabaseEnum        databaseEnum = DataBaseSoft.TransDatabaseSoft(dbConfig.DatabaseSoft, dbConfig.IsAdvancedSetting);
            IDatabaseHelper     db           = DatabaseHelper.Open(databaseEnum, dbConfig.ConnectionString);

            if (string.IsNullOrEmpty(dbConfig.User) || string.IsNullOrEmpty(dbConfig.Password))
            {
                return;
            }
            string sql = null;

            string tableName = NetworkingViewModel.PatientMapModel.PatientTableName;

            if (string.IsNullOrEmpty(tableName))
            {
                return;
            }
            if (dbConfig.DatabaseSoft == DatabaseSoft.SQLServer)
            {
                sql =
                    string.Format(
                        "select a.name as ColumnName,b.name as type,a.length,a.isnullable from syscolumns a,systypes b,sysobjects d where a.xtype=b.xusertype and a.id=d.id and a.id =object_id('{0}')",
                        tableName);
            }
            else if (dbConfig.DatabaseSoft == DatabaseSoft.Oracle)
            {
                sql =
                    string.Format(
                        "SELECT A.TABLE_NAME   as TableName,A.COLUMN_NAME  as ColumnName,A.DATA_TYPE    as type,A.DATA_LENGTH  as length  FROM USER_TAB_COLS A  where A.TABLE_NAME  = '{0}'",
                        tableName.ToUpper());
            }
            else if (dbConfig.DatabaseSoft == DatabaseSoft.PostgreSQL)
            {
                sql =
                    string.Format(
                        "select a.column_name as ColumnName, a.udt_name as type,a.character_maximum_length as length, a.is_nullable as isnullable from information_schema.columns a where table_name='{0}'",
                        tableName);
            }
            else
            {
                sql =
                    string.Format(
                        "Select A.COLUMN_NAME as ColumnName, A.DATA_TYPE TYPE,A.COLUMN_TYPE as LENGTH,A.IS_NULLABLE as ISNULLABLE FROM INFORMATION_SCHEMA.COLUMNS A Where A.table_schema = '{0}' AND A.table_name = '{1}'",
                        dbConfig.Database, tableName);
            }

            DataTable table = db.ExecuteQuery(sql);

            var list = new ObservableCollection <string>();

            foreach (DataRow row in table.AsEnumerable())
            {
                string name = row["ColumnName"].ToString();

                if (string.IsNullOrEmpty(name))
                {
                    continue;
                }

                list.Add(string.Format("[{0}]", name));
            }

            NetworkingViewModel.TargetFieldList = list;
        }
        private void BindSql()
        {
            pnl_SQL.Visible = true;

            //gv2
            //실행 쿼리 데이터 읽어오기
            cloud.R_Adhoc("select TimeIn_UTC, Data_JSON from tbSQLCurrentExecution_JSON where Timein_UTC = '" + dtmTimeIn_UTC.ToString("yyyy-MM-dd HH:mm:ss") + "' and ServerNum = " + ServerNum);

            //신규추가 2017-09-23
            if (cloud.dsReturn.Tables[0].Rows.Count > 0)
            {
                //데이터 테이블 구조생성용으로 첫번째 json데이터를 불러와서 컬럼명을 자동셋팅하도록
                DataTable tester = (DataTable)JsonConvert.DeserializeObject(((string)cloud.dsReturn.Tables[0].Rows[0]["Data_Json"]), (typeof(DataTable)));
                //tester 에는 실제로 데이터가 들어가잇고 clone 을 이용해 dt_Struct 에 데이터테이블 구조만 복사
                //DataTable dt_struct = new DataTable();
                dt_SQLQuery = tester.Clone();
                dt_SQLQuery.AcceptChanges();
                // 이후 dt_struct 에 계속 merge (union) 하여 하나로 합체 테스트로 돌려보니 rowcount 8만 정도 나왓네요
                foreach (DataRow dr in cloud.dsReturn.Tables[0].Rows)
                {
                    DataTable dt_tmp = (DataTable)JsonConvert.DeserializeObject(((string)dr["Data_Json"]), (typeof(DataTable)));

                    //신규추가 2017-09-23 데이터 머지 = mssql union
                    //신규추가 2017-09-23 참조 https://msdn.microsoft.com/ko-kr/library/fk68ew7b(v=vs.110).aspx
                    dt_SQLQuery.Merge(dt_tmp);
                }
            }

            {
                //dt_SQLCurrentQuery 데이터 테이블 만들기
                var test = (from r in dt_SQLQuery.AsEnumerable()
                            orderby r.Field <Int64>("cpu_time") descending

                            select r).Take(20);

                foreach (DataRow r in test)
                {
                    var newRow = dt_SQLCurrentQuery.NewRow();
                    newRow.ItemArray = r.ItemArray;
                    dt_SQLCurrentQuery.Rows.Add(newRow);//I'm doubtful if you need to call this or not
                }

                rpt_Query.DataSource = dt_SQLCurrentQuery;
                rpt_Query.DataBind();
            }


            //label 이름
            cloud.w_ChartSubject(CompanyNum, MemberNum, strReasonCode);
            DataTable dt_ChartSubject = cloud.dsReturn.Tables[0];

            if (dt_ChartSubject != null)
            {
                if (dt_ChartSubject.Rows.Count > 0)
                {
                    lbl_Sql.Text     = dt_ChartSubject.Rows[0][0].ToString();
                    lbl_Sql_Sub.Text = dt_ChartSubject.Rows[0][0].ToString();
                }
            }
            //gv1
            cloud.w_AlertDetail_Table(CompanyNum, MemberNum, ServerNum, strInstanceName, strReasonCode);
            DataTable dt_Descript = cloud.dsReturn.Tables[0];

            gv_List_Sql.DataSource = dt_Descript;
            gv_List_Sql.DataBind();

            //chart
            cloud.w_AlertDetail_Chart(CompanyNum, MemberNum, ServerNum, strInstanceName, strReasonCode);
            DataTable dt_ChartValue = cloud.dsReturn.Tables[0];

            //챠트바인드
            List <Lib.chartProperty> cplst = new List <Lib.chartProperty>();
            StringBuilder            sb    = new StringBuilder();

            cplst = SetChartProperty_SQL();
            cplst = Lib.Flotr2.SetArrayString_Lines(dt_ChartValue, cplst);
            sb    = Lib.Flotr2.SetStringValue(cplst, sb, ServerNum.ToString());
            litScript_Pop.Text += Lib.Util.BoxingScript(sb.ToString());
        }
        private void LoadDgvClients()
        {
            DataTable dataTable     = DataIntegrationHub.Business.Entities.Customer.GetAll();
            var       dataTableEnum = dataTable.AsEnumerable();

            /// Set the datatable based on the SelectedIndex of <see cref="cboClientViews"/>.
            switch (cboClientViews.SelectedIndex)
            {
            case 0:     //Active Clients
                dataTableEnum = dataTableEnum.Where(x => x.Field <byte>("StateCode") == 0);
                break;

            case 1:     // Active 3(21) Clients
                dataTableEnum = dataTableEnum.Where(x => x.Field <bool>("Is321"));
                break;

            case 2:     // Active 3(38) Clients
                dataTableEnum = dataTableEnum.Where(x => x.Field <bool>("Is338"));
                break;

            case 3:     // Active EC Clients
                dataTableEnum = dataTableEnum.Where(x => x.Field <bool>("IsEC"));
                break;

            case 4:     // Active VM Clients
                dataTableEnum = dataTableEnum.Where(x => x.Field <bool>("IsVM"));
                break;

            case 5:     // Inactive Clients
                dataTableEnum = dataTableEnum.Where(x => x.Field <byte>("StateCode") == 1);
                break;

            default:
                return;
            }

            if (String.IsNullOrWhiteSpace(txtClientSearch.Text) == false)
            {
                dataTableEnum = dataTableEnum.Where(x => x.Field <string>("Name").ToUpper().Contains(txtClientSearch.Text.ToUpper()));
            }

            if (dataTableEnum.Any())
            {
                dataTable = dataTableEnum.CopyToDataTable();
            }
            else
            {
                dataTable.Rows.Clear();
            }

            dgvClients.DataSource = dataTable;

            // Display/order the columns.
            dgvClients.Columns["CustomerId"].Visible       = false;
            dgvClients.Columns["PrimaryContactId"].Visible = false;
            dgvClients.Columns["AssetValue"].Visible       = false;
            dgvClients.Columns["MainPhone"].Visible        = false;
            dgvClients.Columns["Fax"].Visible          = false;
            dgvClients.Columns["AddressLine1"].Visible = false;
            dgvClients.Columns["AddressLine2"].Visible = false;
            dgvClients.Columns["AddressCity"].Visible  = false;
            dgvClients.Columns["AddressState"].Visible = false;
            dgvClients.Columns["AddressZip"].Visible   = false;
            dgvClients.Columns["Is321"].Visible        = false;
            dgvClients.Columns["Is338"].Visible        = false;
            dgvClients.Columns["IsEC"].Visible         = false;
            dgvClients.Columns["IsVM"].Visible         = false;
            dgvClients.Columns["ModifiedBy"].Visible   = false;
            dgvClients.Columns["Createdon"].Visible    = false;
            dgvClients.Columns["CreatedBy"].Visible    = false;
            dgvClients.Columns["StateCode"].Visible    = false;

            dgvClients.Columns["Name"].DisplayIndex         = 0;
            dgvClients.Columns["BusinessUnit"].DisplayIndex = 1;
            dgvClients.Columns["ModifiedOn"].DisplayIndex   = 2;
        }
        private LICSRequestArticle[] LoadArticles(IDataReader reader)
        {
            DataTable table = new DataTable();
            List <LICSRequestArticle> articles = new List <LICSRequestArticle>();

            table.Load(reader);
            foreach (DataRow row in table.AsEnumerable())
            {
                try
                {
                    LICSRequestArticle articleItem = new LICSRequestArticle();
                    //   articleItem.GetType().GetProperty(propertyName).SetValue.GetValue((car, null);

                    //  var r = table.AsEnumerable();
                    foreach (DataColumn column in  row.Table.Columns)
                    {
                        var          res          = row[column.ColumnName];
                        PropertyInfo propertyInfo = articleItem.GetType().GetProperty(column.ColumnName);
                        if (row[column.ColumnName].GetType().Equals(DBNull.Value.GetType()))
                        {
                            propertyInfo.SetValue(articleItem, Convert.ChangeType(GetDefaultValue(propertyInfo.PropertyType), propertyInfo.PropertyType), null);
                        }
                        else
                        {
                            propertyInfo.SetValue(articleItem, Convert.ChangeType(row[column.ColumnName].ToString().Trim(), propertyInfo.PropertyType), null);
                        }
                    }
                    articles.Add(articleItem);
                    //double currentArticleGroupFactor = 0;
                    //double currentWeight = 0;
                    //int identityNo = 0;
                    //if (!string.IsNullOrEmpty(Convert.ToString(row["ArticleGroupFactor"])))
                    //{
                    //    currentArticleGroupFactor = double.Parse(Convert.ToString(row["ArticleGroupFactor"]), CultureInfo.InvariantCulture);
                    //}

                    //if (!string.IsNullOrEmpty(Convert.ToString(row["Weight"])))
                    //{
                    //    currentWeight = double.Parse(Convert.ToString(row["Weight"]), CultureInfo.InvariantCulture);
                    //}
                    //if (!string.IsNullOrEmpty(Convert.ToString(row["IdentityNo"])))
                    //{
                    //   identityNo = Int32.Parse(Convert.ToString(row["IdentityNo"]), CultureInfo.InvariantCulture);
                    //}

                    //articles.Add(new LICSRequestArticle
                    //{
                    //    ArticleNo = (row["ArticleNo"] ?? string.Empty).ToString().Trim(),
                    //    ArticleNo2 = (row["ArticleNo2"] ?? string.Empty).ToString().Trim(),
                    //    ArticleDescription = (row["ArticleDescription"] ?? string.Empty).ToString().Trim(),
                    //    ArticleDescription2 = (row["ArticleDescription2"] ?? string.Empty).ToString().Trim(),
                    //    //  IdentityNo = identityNo,
                    //    QuantityUnit = (row["QuantityUnit"] ?? string.Empty).ToString().Trim(),
                    //    // EAN = (row["EAN"] ?? string.Empty).ToString(),
                    //    //  ProductCode = (row["ProductCode"] ?? string.Empty).ToString(),
                    //    //  ArticleGroup = (row["ArticleGroup"] ?? string.Empty).ToString(),
                    //    //  ArticleGroupFactor = currentArticleGroupFactor,
                    //    //   Weight = currentWeight
                    //});
                }
                catch (Exception ex)
                {
                    _loger.Log("Exception while reflect DataColumn values using Reflection");
                    _loger.Log(ex);
                }
            }
            return(articles.Where(i => !string.IsNullOrEmpty(i.ArticleNo)).ToArray());
        }
Example #48
0
 public static IEnumerable <dynamic> AsDynamicEnumerable(this DataTable table)
 {
     return(table.AsEnumerable().Select(row => new DynamicRow(row)));
 }
Example #49
0
        private void dtpFromDate_ValueChanged(object sender, EventArgs e)
        {
            try
            {
                if (rbCustomer.Checked = false && rbDealer.Checked == false)
                {
                    if (Utility.Langn == "English")
                    {
                        MessageBox.Show("Please select customer or dealer..");
                    }
                    else
                    {
                        MessageBox.Show("कृपया ग्राहक किंवा व्यापारी निवडा..");
                    }
                    return;
                }
                dtpToDate.MinDate = dtpFromDate.Value;
                if (Convert.ToInt64(cmbAccountName.SelectedValue) != 0)
                {
                    DataTable dtAccountLedgerReport = accountLedgerReportController.getAccountLedgerDetails(Convert.ToInt64(cmbAccountName.SelectedValue), Convert.ToDateTime(dtpFromDate.Value.ToShortDateString()), Convert.ToDateTime(dtpToDate.Value.ToShortDateString()), 1, Utility.FinancilaYearId, summary, salesmanId);
                    if (dtAccountLedgerReport.Rows.Count > 0)
                    {
                        var query = from accountTable in dtAccountLedgerReport.AsEnumerable()
                                    where accountTable.Field <Double>("crAmount") > 0 ||
                                    accountTable.Field <Double>("drAmount") > 0
                                    select accountTable;
                        if (query.Count() > 0)
                        {
                            dtAccountLedgerReport = query.CopyToDataTable <DataRow>();
                            double crAmount = 0; double drAmount = 0;
                            for (int i = 0; i < dtAccountLedgerReport.Rows.Count; ++i)
                            {
                                crAmount += Convert.ToDouble(dtAccountLedgerReport.Rows[i]["crAmount"]);
                                drAmount += Convert.ToDouble(dtAccountLedgerReport.Rows[i]["drAmount"]);
                            }
                            DataRow dr = dtAccountLedgerReport.NewRow();
                            dr["transactionDate"] = "";
                            if (Utility.Langn == "English")
                            {
                                dr["naration"] = "Total Amount";
                            }
                            else
                            {
                                dr["naration"] = "एकून रक्कम";
                            }
                            //dr["orjName"] = "";
                            //dr["orjId"] = 0;
                            dr["crAmount"] = crAmount;
                            dr["drAmount"] = drAmount;
                            dtAccountLedgerReport.Rows.Add(dr);
                            DataRow dr1 = dtAccountLedgerReport.NewRow();
                            double  due = 0;
                            if (type == 1)
                            {
                                due = drAmount - crAmount;
                            }
                            if (type == 2)
                            {
                                due = crAmount - drAmount;
                            }
                            if (due < 0)
                            {
                                due = (-due);
                                dr1["transactionDate"] = "";

                                if (Utility.Langn == "English")
                                {
                                    dr1["naration"] = "Remaining Amt(Dr)";
                                }
                                else
                                {
                                    dr1["naration"] = "बाकी रक्कम(Dr)";
                                }
                                //dr1["orjName"] = "";
                                //dr1["orjId"] = 0;
                                dr1["crAmount"] = due;
                                dr1["drAmount"] = 0;
                            }
                            else
                            {
                                dr1["transactionDate"] = "";
                                if (Utility.Langn == "English")
                                {
                                    dr1["naration"] = "Remaining Amt(Cr)";
                                }
                                else
                                {
                                    dr1["naration"] = "बाकी रक्कम(Cr)";
                                }
                                //dr1["orjName"] = "";
                                //dr1["orjId"] = 0;
                                dr1["crAmount"] = due;
                                dr1["drAmount"] = 0;
                            }
                            dtAccountLedgerReport.Rows.Add(dr1);



                            BindCustomerGrid(dtAccountLedgerReport);
                            // mpPro.Hide();
                        }
                        else
                        {
                            if (Utility.Langn == "English")
                            {
                                MessageBox.Show("Information Not present" + dtpFromDate.Value + "To" + dtpToDate.Value, "", MessageBoxButtons.OK, MessageBoxIcon.Information);
                            }
                            else
                            {
                                MessageBox.Show("माहिती नाही आहे" + dtpFromDate.Value + "ते" + dtpToDate.Value, "", MessageBoxButtons.OK, MessageBoxIcon.Information);
                            }
                            dgvAccountLedger.DataSource = null;
                            //mpPro.Hide();
                        }
                    }
                }
                else
                {
                    int type = 0;
                    if (rbCustomer.Checked == true)
                    {
                        type = 0;
                    }
                    else
                    {
                        type = 3;
                    }
                    if (chkSummery.Checked == true)
                    {
                        summary = true;
                    }
                    else
                    {
                        summary = false;
                    }
                    DataTable dtAccountLedgerReport = accountLedgerReportController.getAccountLedgerDetails(Convert.ToInt64(cmbAccountName.SelectedValue), Convert.ToDateTime(dtpFromDate.Value.ToShortDateString()), Convert.ToDateTime(dtpToDate.Value.ToShortDateString()), type, Utility.FinancilaYearId, summary, salesmanId);
                    if (dtAccountLedgerReport.Rows.Count > 0)
                    {
                        var query = from accountTable in dtAccountLedgerReport.AsEnumerable()
                                    where accountTable.Field <Double>("crAmount") > 0 ||
                                    accountTable.Field <Double>("drAmount") > 0
                                    select accountTable;
                        if (query.Count() > 0)
                        {
                            dtAccountLedgerReport = query.CopyToDataTable <DataRow>();
                            double crAmount = 0; double drAmount = 0; double dueAmount = 0;
                            for (int i = 0; i < dtAccountLedgerReport.Rows.Count; ++i)
                            {
                                crAmount  += Math.Round(Convert.ToDouble(dtAccountLedgerReport.Rows[i]["crAmount"]));
                                drAmount  += Math.Round(Convert.ToDouble(dtAccountLedgerReport.Rows[i]["drAmount"]));
                                dueAmount += Math.Round(Convert.ToDouble(dtAccountLedgerReport.Rows[i]["dueAmount"]));
                            }
                            DataRow dr = dtAccountLedgerReport.NewRow();


                            dr["accountName"] = "";
                            dr["crAmount"]    = crAmount;
                            dr["drAmount"]    = drAmount;
                            dr["dueAmount"]   = dueAmount;
                            dtAccountLedgerReport.Rows.Add(dr);
                            BindCustomerGrid(dtAccountLedgerReport);
                            // mpPro.Hide();
                        }
                    }
                }
            }
            catch (Exception ea)
            {
                MessageBox.Show(ea.Message);
            }
        }
Example #50
0
        /// <summary>
        ///     Joins the result table of the data table into the feature class of the tool by using the oid/fid.
        ///     Afterwards the method updates the view with the new result. This also includes the update of the class breaks or bi
        ///     polar renderer if set.
        /// </summary>
        /// <param name="tool"></param>
        /// <param name="dataTable"></param>
        public void JoinToolResultByOID(AbstractToolTemplate tool, DataTable dataTable)
        {
            Feature feature;

            if (!_dictionaryOfLinks.TryGetValue(tool, out feature))
            {
                return;
            }

            var featureClass = feature.FeatureClass;

            if (featureClass.FindField(tool.DefaultResultColumnName) < 0)
            {
                IField newField     = new FieldClass();
                var    newFieldEdit = (IFieldEdit)newField;
                newFieldEdit.Type_2      = esriFieldType.esriFieldTypeDouble;
                newFieldEdit.Name_2      = tool.DefaultResultColumnName;
                newFieldEdit.AliasName_2 = tool.DefaultResultColumnName;

                featureClass.AddField(newField);

                feature.UpdateFieldsProperty();
                feature.SetToolField(newField);
            }

            using (var comReleaser = new ComReleaser())
            {
                var featureCursor = featureClass.Update(null, true);

                comReleaser.ManageLifetime(featureCursor);

                var esriFeature = featureCursor.NextFeature();

                var fieldIndex = featureClass.FindField(feature.Fields.First(f => f.IsToolField).FieldName);

                var oidIndex = featureClass.FindField(featureClass.OIDFieldName);

                while (esriFeature != null)
                {
                    var oid = Convert.ToInt32(esriFeature.Value[oidIndex]);

                    var dataRows = dataTable.AsEnumerable().Where(dr => dr.Field <FieldTypeOID>(featureClass.OIDFieldName).OID == oid);

                    var dRow = dataRows.FirstOrDefault();

                    if (dRow != null)
                    {
                        esriFeature.Value[fieldIndex] = dRow[tool.DefaultResultColumnName];
                    }

                    esriFeature.Store();

                    esriFeature = featureCursor.NextFeature();
                }
            }

            if (feature.SelectedFieldForRendering != null)
            {
                switch (ConfigSingleton.Instance.SelectedRenderoption)
                {
                case RenderOption.AfterSliderDrag:
                    ProgressDialog.ShowProgressDialog("Creating Symbology", (Action <RendererContainer, IFeatureLayer2>)Render, feature.SelectedFieldForRendering.RenderContainer, feature.FeatureLayer);
                    break;

                case RenderOption.AnimationLike:
                case RenderOption.Realtime:
                default:
                    Render(feature.SelectedFieldForRendering.RenderContainer, feature.FeatureLayer);
                    break;
                }
            }

            PropertyChanged.Notify(() => LinkDictionary);
        }
 public static IEnumerable<DataRow> Intersect(this DataTable table, DataTable other, IEqualityComparer<DataRow> comparer)
 {
     return table.AsEnumerable().Intersect(other.AsEnumerable(), comparer);
 }
Example #52
0
        private void multyInsertIntoFilesToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if (MainUI.copiedValuesStrHolders.Count > 0)
            {
                isTextChanged = false;
                closeAndSavePackage(false);

                OpenFileDialog openFileDialog1 = new OpenFileDialog();

                openFileDialog1.Multiselect = true;

                switch (Thread.CurrentThread.CurrentUICulture.ThreeLetterWindowsLanguageName)
                {
                case "CHS":
                case "ZHI":
                    openFileDialog1.Filter      = "STBL文件 (*.stbl)|*.stbl|所有文件 (*.*)|*.*";
                    openFileDialog1.FilterIndex = 1;
                    openFileDialog1.Title       = "选择STBL文件";
                    break;

                case "CHT":
                case "ZHH":
                case "ZHM":
                    openFileDialog1.Filter      = "STBL檔案 (*.stbl)|*.stbl|所有檔案 (*.*)|*.*";
                    openFileDialog1.FilterIndex = 1;
                    openFileDialog1.Title       = "選取STBL檔案";
                    break;

                default:
                    openFileDialog1.Filter      = "STBL Files (*.stbl)|*.stbl|All Files (*.*)|*.*";
                    openFileDialog1.FilterIndex = 1;
                    openFileDialog1.Title       = "Choose STBL File";
                    break;
                }

                if (openFileDialog1.ShowDialog() == DialogResult.OK)
                {
                    foreach (string fileName in openFileDialog1.FileNames)
                    {
                        openSTBLfile(fileName);

                        isTextChanged = true;

                        for (int x = 0; x < MainUI.copiedValuesStrHolders.Count; x++)
                        {
                            StringHolder copiedStrElement = MainUI.copiedValuesStrHolders[x];

                            DataTable dt = (DataTable)dataGridView1.DataSource;

                            var drArr = (from rowEl in dt.AsEnumerable()
                                         where rowEl.Field <string>(0) == copiedStrElement.textIDFld
                                         select rowEl);

                            if (drArr.Count() == 0)
                            {
                                DataRow dr = dt.NewRow();
                                dr[0] = copiedStrElement.textIDFld;
                                dr[1] = copiedStrElement.displayTextFld;
                                dt.Rows.Add(dr);
                                dataGridView1.Rows[dataGridView1.Rows.Count - 1].HeaderCell.Value = (dataGridView1.Rows.Count).ToString();
                            }
                            else
                            {
                                DataRow dr = drArr.First();
                                dr[1] = copiedStrElement.displayTextFld;
                            }
                        }

                        //break;

                        closeAndSavePackage();
                    }
                    isTextChanged = false;
                }
                MessageBox.Show("Done!");
            }
            else
            {
                switch (Thread.CurrentThread.CurrentUICulture.ThreeLetterWindowsLanguageName)
                {
                case "CHS":
                case "ZHI":
                    MessageBox.Show("您未复制任何字串。\r\n请先复制字串,然后再使用该选项向STBL文件大量插入已复制的字串!");
                    break;

                case "CHT":
                case "ZHH":
                case "ZHM":
                    MessageBox.Show("您未複製任何字串。\r\n請先複製字串,然後再使用該選項向STBL文件大量插入已複製的字串!");
                    break;

                default:
                    MessageBox.Show("You have not copied any string. \r\nCopy strings and use this option for mass insert of copied strings into STBL files!");
                    break;
                }
            }
        }
Example #53
0
        public Ent_Promotion_Orce_Lista lista_tipo_param()
        {
            Ent_Promotion_Orce_Lista lista_param = null;
            string    sqlquery = "USP_ORCE_GET_FILTROS_PROMOS";
            DataTable dt       = null;

            try
            {
                using (SqlConnection cn = new SqlConnection(Ent_Conexion.conexion))
                {
                    try
                    {
                        using (SqlCommand cmd = new SqlCommand(sqlquery, cn))
                        {
                            cmd.CommandTimeout = 0;
                            cmd.CommandType    = CommandType.StoredProcedure;
                            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                            {
                                dt = new DataTable();
                                da.Fill(dt);
                                //List<Ent_Promotion_Orce_Filtros> lista_filtros = new List<Ent_Promotion_Orce_Filtros>();
                                //lista_filtros = (from DataRow fila in dt.Rows
                                //                 select new Ent_Promotion_Orce_Filtros()
                                //                 {
                                //                     PROMOTION_TYPE=fila["PROMOTION_TYPE"].ToString(),
                                //                     CREATE_USER = fila["CREATE_USER"].ToString(),
                                //                     STATUS = fila["STATUS"].ToString(),

                                //                 }
                                //               ).ToList();

                                if (dt != null)
                                {
                                    if (dt.Rows.Count > 0)
                                    {
                                        lista_param = new Ent_Promotion_Orce_Lista();

                                        lista_param.Promotion_Orce_Status = (from item in dt.AsEnumerable()
                                                                             group item by
                                                                             new
                                        {
                                            CODIGO = item["STATUS"].ToString(),
                                            DESCRIPCION = item["STATUS"].ToString(),
                                        }
                                                                             into G
                                                                             select new Ent_Promotion_Orce_Status()
                                        {
                                            codigo = G.Key.CODIGO,
                                            descripcion = G.Key.DESCRIPCION,
                                        }).ToList();

                                        lista_param.Promotion_Orce_Type = (from item in dt.AsEnumerable()
                                                                           group item by
                                                                           new
                                        {
                                            CODIGO = item["PROMOTION_TYPE"].ToString(),
                                            DESCRIPCION = item["PROMOTION_TYPE"].ToString(),
                                        }
                                                                           into G
                                                                           select new Ent_Promotion_Orce_Type()
                                        {
                                            codigo = G.Key.CODIGO,
                                            descripcion = G.Key.DESCRIPCION,
                                        }).ToList();

                                        lista_param.Promotion_Orce_User = (from item in dt.AsEnumerable()
                                                                           group item by
                                                                           new
                                        {
                                            CODIGO = item["CREATE_USER"].ToString(),
                                            DESCRIPCION = item["CREATE_USER"].ToString(),
                                        }
                                                                           into G
                                                                           select new Ent_Promotion_Orce_User()
                                        {
                                            codigo = G.Key.CODIGO,
                                            descripcion = G.Key.DESCRIPCION,
                                        }).ToList();
                                    }
                                }
                            }
                        }
                    }
                    catch
                    {
                    }
                }
            }
            catch
            {
            }
            return(lista_param);
        }